Configure SQL Server Reporting Services 2008 R2 To Use SSL

The very brief outline of how to setup SSL access in SQL Server Reporting Services:

First install the SSL Certificate in IIS or Server Certificates:

• Open MMC
• Go to File --> Add Snapin
• Select Certificates
• Import Certificate

Use Reporting Services Configuration Manager to setup the the SSL / 443 in SSRS.

Next edit RSReportServer.config and add the new report server URL 

eg.

<ReportServerUrl>https://server_name/ReportServer_PRD</ReportServerUrl>

Restart the reporting service for the settings to take affect.

Note: The report server has to be accessed using the path configured above. This must be a valid resolvable path and must conform to the SSL certificate issuer address. To test the procedure has worked just edit the hosts file and try connecting with the URL above.

Report Server Command Line Tools Missing From SSRS Install

I searched around my computer for about an hour for this… grr!!!

According to the documentation RS command line tools are installed as part of the reporting services install. They are installed to c:\Program Files\Microsoft SQL Server\100\Tools\Binn by default (unless you change the path in your installation).

When I checked this path they weren’t installed. After doing a Windows file search for rs.exe nothing turned up – I thought I must’ve missed something in the installation.

Turns out that instead they are installed to C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn. It must have something to do with it being a 32 bit utility even though the rest of the SQL Server and Reporting Services installation is a 64 bit install.

Updating SSRS Subscription Addresses Via TSQL

Just need to update the email addresses at the top of the script and run against the reportserver database.

/****** Script for SelectTopNRows command from SSMS  ******/
declare @OldEmailAddress varchar(1000)
declare @NewEmailAddress varchar(1000)

set @OldEmailAddress = 'Old Address'
set @NewEmailAddress = 'New Address'

--Now Update them to a new user that you want to receive the subscriptions
BEGIN TRANSACTION
      UPDATE Subscriptions
            SET ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@OldEmailAddress,@NewEmailAddress))
        FROM ReportServer.dbo.Subscriptions
        WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@OldEmailAddress) + '%'
COMMIT TRANSACTION
 
--OPTIONAL: Now just return a listing of those records that were updated
SELECT * FROM [ReportServer].[dbo].[Subscriptions]
WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@NewEmailAddress) + '%'
GO