SSRS Problem

One day we got a problem with SQL Server Reporting Services. When you try to navigate to http://servername/Reports you got internal error 500

2 SQL instances with SQL2008RTM on win 2003 gave same error

appdomainmanager!DefaultDomain!af4!06/17/2015-15:01:33:: e ERROR: AppDomain ReportManager_SIPS_0 failed to start. Error: (0): error CS0016: Could not write to output file ‘c:\Program Files\Microsoft SQL Server\MSRS10_50.TEMP\Reporting Services\rstempfiles\reports_temp\699d5a9d\9f58366e\App_global.asax.mhl7l18r.dll’ — ‘The directory name is invalid. ‘

Both SQL Reporting services instances were running under same domain account domain\SVC-ARB-TEMP-RS

First of all, we tried to give all possible and impossible permissions to c:\Program Files\Microsoft SQL Server\MSRS10_50.TEMP\Reporting Services\rstempfiles\ for accounts:

domain\SVC-ARB-TEMP-RS – Reporting services service account

domain\SVC-ARB-TEMP-RS-EXEC – Reporting services execution account

Network Service

Everyone

ASP.Net Machine account

IIS_WPG

SQLServerReportServerUsers$computername$instancename

Computername\Administrators

I know it sounds weird for some of accounts, but we tried all variants we could find on google. We did restart after any modification done, however it was still the same.

For test purpose we removed content of c:\Program Files\Microsoft SQL Server\MSRS10_50.TEMP\Reporting Services\rstempfiles\reports_temp\ and after SQL Server Reporting services were restarted content appeared again, exactly the same content. So the problem is not with permissions actually!

I tried to filter out all actions that ReportingServicesService.exe(SSRS server executable) perform after start up using procmon. However I haven’t seen there any access to c:\Program Files\Microsoft SQL Server\MSRS10_50.TEMP\Reporting Services\rstempfiles\reports_temp\ . Might be cause this was done using SYSTEM account and it was too much to filter out.

As it was RTM edition we decided to perform upgrade to currently newest available SP3. During update we got error that the certain SQL Server cached updates are missing from server. Most likely, they were removed by other admins to release some space on disk C:

There is a wonderful tool from MS to fix such kind of problems FindSQLInstalls.vbs
https://support.microsoft.com/en-us/kb/969052

Pls read it carefully on how to use it. It found all missing installation files; mostly they were from SQL 2008R2 SP1.

So we downloaded SQL 2008R2 SP1 x86 and extracted it using /x key to D:\SP1 folder

The output of FindSQLInstalls.vbs script provides you with advice on how to fix missing files, like:

Copy “G:\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi”

C:\WINDOWS\Installer\19b4d2.msi

So we just executed:

Copy “D:\SP1\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi”

C:\WINDOWS\Installer\19b4d2.msi

And file was copied to cache, however script did not want to recognize it and after next execution written that it is missing same file. We tried to copy again and it asked to replace file in target location. Even after that script did not recognize that the file was in place

So we created folder with the same path as script has:

G:\x64\

And put there files from D:\SP1, after next execution script gave us no errors.
We successfully patched both instances with SQL 2008R2 Sp3 and restarted server as it was prompted.

After server restarted both instances were running fine, error was gone.
As we performed all changes with 1 of instances I decided to check which permissions will Rstempfiles will get now on the secondary instance. Among default accounts, like system, it was:

Network Service – Full permissions
SQLServerReportServerUser$computername$Instancename (local group) – Read/Write permissions
domain\SVC-ARB-TEMP-RS (SSRS service account) – Full permissions

Despite I’m pretty sure those permissions were set before upgrade it did not work. So for reporting services this problem is not correct.

Lessons learned:
! Keep your software with recent service pack level. Even if you will open case to MS they will ask you to patch everything first.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s