SSRS Error 401.3 Access is denied

No Gravatar

So, you’ve been denied!  It’s OK.  It happens to the best of us.

If you are lucky enough be gifted with this message take a look at the NTFS rights of the SQL Server Reporting Services instance which will likely be a folder like C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services.  Granting the appropriate AD group read & execute rights may solve the problem.

clip_image002

Server Error in ‘/Reports’ Application.

Access is denied.

Description: An error occurred while accessing the resources required to serve this request.  You might not have permission to view the requested resources.

Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists).  Ask the Web server’s administrator to give you access.

Thanks to Mike and Jerry for pointing me in the right direction.  After carefully reading the error, it is quite obvious isn’t it.

http://stackoverflow.com/questions/17685452/ssrs-401-3-error-access-denied-due-to-access-control-lists

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/747f9846-dd9a-4fb4-914a-283871d6cedf/client-failing-to-access-the-ssrs-2008-sp1-report-manager-url-with-access-denied-error-4013?forum=sqlreportingservices

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fd41a86b-976f-4851-8dae-5561ebc6d719/browse-reportserver-return-4013-error-after-joined-domain?forum=sqlreportingservices

June 20, 2016

Posted In: SQL Reporting

Tags: , , ,

ReportServerService logs not deleted

No Gravatar

I was performing some initial discovery on a SCCM primary site server and noticed a lack of disk space. Using WinDirStat.exe I started digging deeper and discovered almost 100gb of ReportServerService_<timestamp>.log files. These are associated with the SQL Server Reporting Service and should be cleaned up after 14 days by default as configured in the ReportingServicesService.exe.config file via the parameter

However that was not happening on this server and it was soon to die under the weight of a year worth of log files.  It turns out this is a known bug as described in Microsoft KB2706518.  The solution is to upgrade SQL 2008 to Service Pack 3 and Cumulative Update 5 or higher.  Until that upgrade can happen the logging level can be changed to a value less than 3 in the config file.

You could enable NTFS compression, manually delete the files older than about 30 days, or write a script to automate that.  But why bother… just upgrade SQL! 🙂

June 11, 2014

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting

Huge ReportServer database log

No Gravatar

Today I noticed on two of my SCCM 2012 infrastructures that the ReportServer_log.ldf file was extremely large (>100GB).  This is quite concerning considering that the actual database is less than 256MB.  I have not yet investigated the root cause yet, but here is a workaround / solution to reclaim the disk space and prevent it from growing large again.

  1. Open SQL Server Management Server and connect to the SQL Reporting Services Server (most likely your SCCM Primary server)
  2. Change the ReportServer database Recovery Mode to “Simple” (right-click on the ReportServer database and select properties)
  3. Manually backup the ReportServer database (right-click on the ReportServer, select Tasks, select backup, …)
  4. Truncate the log file (start a new query and run “DBCC ShrinkFile (ReportServer_log, TruncateOnly“)
  5. Configure the ReportServer database AutoGrowth to use a maximum file size (2048MB should be sufficient) (right-click on the ReportServer database and select properties, select Files, …)

You should now see that the log file is only a few MB.

 

References and additional guidance

 

 

 

January 6, 2014

Posted In: SQL Reporting

SQL Reports Subscription Ownership

No Gravatar

We have a number SCCM Reporting Services subscriptions for our automated reporting.  Yesterday I had a former team member’s ID removed from SCCM access and today most of the automated reports did not run.

When a report subscription is created the owner is the logged on user.  If that user ever loses access to SCCM/SSRS then the subscription will fail to run.  I decided the best course of action was to reassign ALL subscriptions to the SCCM service account.  That won’t prevent problems with new subscriptions, but it will prevent the problem with existing ones.  I could run the t-SQL on a schedule in SQL itself to handle that.  The only downside is that there will be no results when clicking the “My Subscriptions” link.

Thanks to Jeremiah Clark for the explanation and some t-SQL: http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

[cc lang=’sql’ line_numbers=’false’]
USE ReportServer
Declare @NewUserName [nvarchar](260); Set @NewUserName = ‘NewDomain\NewUser’
Declare @NewUserID [uniqueidentifier]

— Show Subscriptions for a User
select U.UserName [OwnerUserName], S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = @NewUserName

— Show UserID for a User
select @NewUserID=U.UserID From Users AS U where U.UserName = @NewUserName
Select @NewUserID [NewUserID], @NewUserName [NewUserName]

— Show records for Reassigning the User (Owner)
Select @NewUserID [NewUserID], U.UserID, U.UserName, S.*
From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName

— Reassign the User (Owner)
Update S Set S.OwnerID = @NewUserID From Subscriptions AS S Inner Join Users AS U on S.OwnerID = U.UserID
where U.UserName = ‘OldDomain\OldUser’ —<> @NewUserName
[/cc]
Enjoy

May 1, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting, T-SQL

SQL 2012, BIDS, and SSDT

No Gravatar

With SQL Server 2012 Business Intelligence Development Studio is replaced with SQL Server Data Tools.

BIDS = Business Intelligence Development Studio

SSDT = SQL Server Data Tools (http://msdn.com/data/tools.aspx)

If you customize SQL Reporting Services reports for SCCM 2007 or ConfigMgr 2012 in you’ll probably want some GUI tool to help you edit or design the report.  Microsft ReportBuilder 2.0/3.0 is quasi built-in to Reporting Services and allows creating and editing of RDL files in a limited manner.  If you need a powerful tool to create a complex report you’ll likely turn to BIDS.  However, BIDS is now depricated and beginning with SQL 2012 SSDT is the tool going forward.

SSDT utilizs the Microsoft Visual Studio 2010 SP1 or 2012 Shell which is very nice.

A word of caution: It appears that SSDT comes in several flavors and not all support Reporting Services.  Be sure to install SQL Server Data Tools from the SQL Server 2012 SP1 install soruce and NOT the stand-alone / downloadable installer.

Some useful links:

March 1, 2013

Posted In: ConfigMgr 2007, ConfigMgr 2012, SQL Reporting