Migrate Reporting Services to SQL 2008 RS

Here is the MS SOP, works well.

Reference:
http://msdn.microsoft.com/en-us/library/ms143724(v=sql.100).aspx

Blank page for SQL 2008 Reporting Services management site

Finally figured this out.  The Windows 2008 R2 image we use had the described setting enabled by default.  Had to disable in order for the Reporting services management page to work/display.

 

*** Report Manager fails after you enable FIPS compliant algorithms in Local Security Policy

After you enable the Local Security Policy "System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing", you are not able to use Report Manager. When you open Report Manager, your browser will be empty or show an "HTTP 500 Internal Server Error". A SQL Server 2008 R2 or later version of the report server will add an error message similar to the following in the report server service log file:

ui!ReportManager_0-1!1708!02/27/2010-08:02:03:: e ERROR: System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.

To work around this issue: At this time, there is no known work around. Report Manager does not support the Local Security Policy "System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing". If you want to use Report Manager you need to disable the setting and restart the report server service.

This issue applies to: SQL Server 2008 Reporting Services and SQL Server 2008 R2 Reporting Services.

Reference:

http://technet.microsoft.com/en-us/library/ms345220(v=sql.105).aspx

How SQL 2005 Report Server works

Nice blog (see Reference below) about how report server works and what the logs files are for.

Here are some queries that I use to check to see if jobs are stuck on our Report server:

use ReportServer

-- Jobs waiting to execute

select *
from event e
order by TimeEntered

-- Jobs that ARE executing

select n.processstart, n.notificationentered, n.deliveryextension, sch.scheduleid, sub.subscriptionid, sub.description, sub.laststatus, sub.lastruntime
from notifications n, schedule sch, subscriptions sub
where (n.subscriptionid = sub.subscriptionid) and (sch.eventdata = sub.subscriptionid)
order by n.notificationentered desc


Reference:
http://blogs.msdn.com/b/psssql/archive/2009/02/02/why-aren-t-my-subscriptions-working.aspx

Reporting server stops updating fileshare

Ran into a case where report creator had created a report (non-unique file names) that updated to a fileshare every 15 mins.  If the file was kept open by a user, job output would fail and be set to retry in 15 mins. 

By default, RS maxretries=3 which means RS will try again 3 times before giving up.  This was causing issue where these timed subscriptions were blocking each other from being successful.  Ie. job runs, tries to save to file, but fails and will retry in 15 mins, but 15 mins later a new iteration of the job runs, tries to save to file, but that one fails as well and will retry in 15 mins, etc...

Resolved by setting Maxretries=0 so that if output fails, RS will NOT retry and next iteration of the job will have a chance to update the file.

Reference:
http://technet.microsoft.com/en-us/library/ms157273.aspx

Migrating SQL Reporting Services

Good article.

Reference:
http://www.mssqltips.com/sqlservertip/2627/migrating-sql-reporting-services-to-a-new-server/
http://www.sqldbatips.com/showarticle.asp?ID=62

How to SSL enable SQL 2005 Reporting Services

This assumes you have received the required CERT to enable SSL on IIS.

1. Install SSL cert to IIS and enable SSL and required 128-bit.
2. Open Reporting Services Configuratioin tool to configure the Report Server Virtual Directory.
3. Select Require Secure Socket Layer (SSL) connection checkbox.
4. Select level 3.
5. Enter certificate name which is (usually) the DNS of the webserver.
6. Click Apply.
7. Using Notepad, edit RSReportServer.config and modify the UrlRoot to use "https:" and the full DNS (certificatename) of the webserver.
   https://certificatename/reportservervirtualdirectoryname

Reference: http://msdn.microsoft.com/en-us/library/ms345223(SQL.90).aspx

Installing SQL 2005 Reporting Services

This install assumes 2 server, one for SQL and one for web/IIS part.

1. Install IIS with ASP.NET enabled to webserver.
2. Install SQL 2005 Std Ed to SQL server, database components only.
3. Install SQL 2005 Reporting services on the webserver.
4. Create SQL Account for SSRS to use to access database (see http://msdn.microsoft.com/en-us/library/ms159133.aspx) on SQL server.
5. Run the SSRS Configuration Wizard and point databases to SQL server.
6. Add users to SSRS access permissions.

Update: installed Oracle drivers to access Oracle database reports, but needed to add permissions (Users group RX) to C:\Oracle install folder so that IIS could access DLL files.

References: http://msdn.microsoft.com/en-us/library/ms159689(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms143736(SQL.90).aspx

Unattended Execution Account

If using a local account for this, I found I had to give it the User Right "Allow log on locally". Otherwise, the account is just a normal Users group account on the Reporting Services test server. SQL 2005, SSRS, and IIS are installed on the same (virtual) server.