Change the SSRS datasource server or connection string

Yes, the steps provided in the question were specific to migration of the SSRS instance and not the underlying SQL database instance (i.e. data source). That is actually a drawback of this backdoor method for the migration from SQL 2005 to SQL 2012.

The connection string for a data source is stored on the dbo.Catalog table and dbo.DataSource. The data in dbo.Catalog is just encrypted in a binary format and we can manipulate the string to the new string. However, there are ConnectionString, UserName & Password stored on the dbo.DataSource, which appear to be encrypted.

Here is the query that can be used to update the ConnectionString in the dbo.Catalog table. I will continue working on this tomorrow, but I doubt if it would be possible to update the encrypted data.

DECLARE @oldServerName VARCHAR(255) = 'W81VM';
DECLARE @oldCatalogName VARCHAR(255) = 'AdventureWorks2014';
DECLARE @newServerName VARCHAR(255) = 'W81VM';
DECLARE @newCatalogName VARCHAR(255) = 'AdventureWorks2012';

;WITH BaseCatalogContent (RawConnectionStringInformation, ItemID)
AS (
    SELECT CONVERT(VARBINARY(MAX), dc.Content) AS RawConnectionStringInformation,
           dc.ItemID 
    FROM dbo.[Catalog] AS dc
    WHERE dc.[Type] = 5
   ),
ExtractedConnectionString (ItemID, OldConnectionString)
AS (SELECT bcc.ItemID,
           --MUST BE VARCHAR(MAX) here
           CAST(SUBSTRING(bcc.RawConnectionStringInformation,4,LEN(bcc.RawConnectionStringInformation)) AS VARCHAR(MAX)) AS OldConnectionString
    FROM BaseCatalogContent AS bcc
   ),
ModifiedConnectionString(ItemId, OldConnectionString, NewConnectionString)
AS (SELECT ecs.ItemID,
           ecs.OldConnectionString,
           REPLACE(REPLACE(ecs.OldConnectionString,@oldServerName,@newServerName),@oldCatalogName,@newCatalogName) AS NewConnectionString
           --CAST(REPLACE(REPLACE(ecs.OldConnectionString,@oldServerName,@newServerName),@oldCatalogName,@newCatalogName) AS VARBINARY(MAX)),
           --0xEFBBBF
    FROM ExtractedConnectionString AS ecs
   )
UPDATE clog
SET clog.Content = (0xEFBBBF + CAST(mcs.NewConnectionString AS VARBINARY(MAX)))
FROM dbo.[Catalog] AS clog
INNER JOIN ModifiedConnectionString AS mcs ON clog.ItemID = mcs.ItemId;

Select allOpen in new window

thank you very much, Nakul.  I am super pleased to hear back from you.

>> That is actually a drawback of this backdoor method for the migration from SQL 2005 to SQL 2012.
While I understand it is not the gui-driven migration, the fact that it is sourced with a backup/restore of both ReportServer databases, I do believe it's got some legs under it.  This can be done.  

99% of all datasources are set up to run w/windows integration, so the servername is really my focus, wouldn't you say?  There are thousands of reports, but only five different datasources.  Does this make the task any easier?

>>To begin, let me clarify that the earlier exercise was restricted to migrating the reporting server only. Making the reports and data sources in a reporting server point to another database is a separate exercise and is akin to what would have happened if the SQL server acting as the data source changes it's name.

Then clearly I was remiss.  I know that I said I was migrating from one server to another, and the upgrade from v2005 to v2012.  I am not sure how that does not suggest the reports and datasources were not also a requirement of this move.

Very surprising to me that we cannot retrieve or somehow alter the datasource credentials, especially given steps 2 and 8 below, where we backup the encryption key and restore it, from source to target servers.  Also curious whether this could be done with the unattended execution account.  Regardless, as I said before, there is only a handful of datasources in comparison to the number of reports.  If I have to do this manually, it can be done.  Potentially with the two new steps below.  Please let me know what you think.  


steps -
1. old server - backup reportserver databases - both of them
2. old server - backup encryption key
3. old server - backup encryption key (only a safety belt, will not be used)
4. new server - turn off SSRS
5. new server - restore both reportserver databases
6. new server - delete server10 encryption key from ReportServer.dbo.Keys
7. new server - ssrs configuration mgr, 'databases' tab, point to newly restored reportserver db
8. new server - ssrs configuration mgr, restore encryption key from server10
9. new server - test any report from report mgr - I tested all saints parish activity report
10. new server - update report output path - reportserver.extensionsettings
NEW STEP -- CHANGE SERVERNAME IN DATASOURCE WITH SCRIPT - can this be done?  
NEW STEP -- MODIFY EACH DATASOURCE IN REPORT MGR TO INCLUDE CREDENTIALS, WHERE NECESSARY
11. new server - test any subscription agent job

Because generally companies have central corporate reporting servers being shared by various enterprise applications, I had assumed that the reporting server in your case was separate from the application database server and was being migrated from SQL 2005 to 2012.

To the best of my knowledge, Microsoft has intentionally kept the encryption of the credentials complex to ensure that even if the server housing the reporting database (ReportServer & ReportServerTempDB) is compromised, the attacker cannot get to the data source.

We did restore the symmetric key from the old server to the new server. The set of keys we restored contained the symmetric public key, encrypted by a private key stored within the Reporting Services code. Once decrypted, this symmetric key can be used to decrypt the connection string on the datasource. Because we do not have the private key used by Reporting Services, we can't decrypt the symmetric key, and in turn the datasource credentials.

When modified from the UI, the UI has the private key and the user is able to make the necessary modifications.

Nakul, you did not get back to me on these items:

10. new server - update report output path - reportserver.extensionsettings
NEW STEP -- CHANGE SERVERNAME IN DATASOURCE WITH SCRIPT - can this be done?  
NEW STEP -- MODIFY EACH DATASOURCE IN REPORT MGR TO INCLUDE CREDENTIALS, WHERE NECESSARY

It is not possible to change the connection strings embedded in the encrypted Data Source Connection information stored in the (dbo.DataSource) table because it is encrypted and the decryption happens outside the boundaries of the SQL Server database engine for security reasons explained above.

So, the changing of the data source credentials has to be a manual process.

But what of this step?  

NEW STEP -- CHANGE SERVERNAME IN DATASOURCE WITH SCRIPT - can this be done?

The server name on the data source is part of the connection string. So, no, it cannot be updated by using a script.

Thank you for all of the assistance, Nakul.  I am very grateful for your input.