You would imagine Windows 2008 would know all about SQL 2005 and SQL 2005 Service Packs would have debriefed SQL 2005 about Windows 2008 – the new guy on the block. It might true for SQL 2005 Database Server but not the case with SQL Server Reporting Services.
SSRS worked fine until I decided to upgrade the install to Service Pack 3 and as usual had no good backup to resort. Ops, lesson learned, if you really want to learn and see how you keep your chin up under crises make sure you delete all backups (it’s a sarcastic joke, don’t take it seriously)
Here are few things that I learned. Not in any particular order so use it as a check list if you like.
Virtual instance running on Windows Hyper-V
Windows 2008 x64 Standard, current with all Microsoft Updates. 2 cpu, 2 GB RAM and 50 GB fixed disk.
SQL 2005 x64- all components installed (broke after SP3 upgrade)
Here are some pointer that you may like to check out.
If you don’t have required components of IIS installed, SSRS option will not be available to choose in SQL setup. Minimum components you will are: Default Document, Static Content, Dir Browsing, HTTP errors and redirection, all IIS 6 components (compatibility tools and console).
Make sure your App Pool is in Classic Mode (not the new IIS7 mode) and configured to run under a domain account (I would imagine, for non-clustered installation, it will work for local user account context as well)
Run your SQL services under domain accounts ( not any built-in account like local system or Network Services or NT Authority).
I have Kerberos configured as well. See my other post on setting the Kerberos for SQL.
The account you running your AppPool and SQL Services is member of Windows local group SQLServer2005ReportingServicesWebServiceUser$ServerName$InstanceName. In my installation it is the same account for AppPool and SQL Server. If you have different accounts use the account that you have for AppPool.
Make sure, for two SSRS virtual folders on WebSite, for Handler Mapping feature you edit the permissions to include “scripts” as well or you will get http 401 error with no clear reason. I dig into IIS logs to find the dot codes for the error and the clues.
Update the <WebServiceAccount> element in \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config file that still have the default NT Authority as logon. Replace it with the AppPool account. If using domain account qualify the logon name with domain name
In installs and uninstalls I messed up my IIS bindings. Make sure you have the following and if not create them. Not sure if I need them all but I recreated them anyway.
(Name – value format)
Net.tcp – 808:*
Net.pipe – *
Net.msmq – localhost
Msmq.formatname – localhost
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\Setup]
"WebServiceIdentityConfigured"=" XXYourDomainXX\\XXYourUserNameXX "
System.MissingMethodException: Method not found: 'Void Microsoft.ReportingServices.Diagnostics.UserUtil.CleanCurrentUserName()
Its now time to see the dates on the files in \ Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\bin directory. 11/25/2008 should be the date stamp on updated files with version 9.0.4035.0. Not all files get updated but you can look for Microsoft.ReportingServices.Diagnostics.dll and Microsoft.ReportingServices.Authorization.dll. If these files are not current, wipe the server and rebuild it or install the server elsewhere and copy the files over.