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

This is the start point

Hi!

Today is our opening day and starting from here we would publish here some interesting findings, workarounds, problems and so on. We will try to fill it with unique content, something completely different from blogs where people will tell you how to create folder on windowXP.

We are mostly focused on Microsoft technologies starting from all different versions of Windows Server operating systems to MS Exchange, SQL and many many other windows related software.

So this information will be mostly for technical people who are daily facing the same problems as we do. Hope this information will be helpful for others!