SSRS keys can be backed up and restored in 2 easier ways. One, offcourse using the GUI panel of Reporting Configuration Manager and the other one is command line tool by executing rskeymgmt utility.
The rskeymgmt utility can be found in the binn sub-directory of your SQL Server install directory. Opening the command prompt and navigating to this directory, we can run rskeymgmt -? to get a list of arguments and additionally some example commands.
To backup the key, issue this command within cmd prompt: rskeymgmt.exe -e -f D:\TestBackup\SSRSKeybackup -p Yourownpwd -i MSSQLServer
While, to restore, use this command: rskeymgmt.exe -a -f D:\TestBackup\SSRSKeybackup -p Yourownpwd -i MSSQLServer
In the event you restore a SSRS database to a new server, the encryption keys will need to be loaded onto the new server in order to allow that server to read and utilize all of the items noted in the below list.
Otherwise an error will result when attempting to navigate to the Report Server.
Also, your embedded data sources would be unreadable if you add a new key.
Of course you could recreate a SSRS key on the new server and then redeploy all the data sets, data sources, and reports. In that situation though, you would still have to recreate all the folders and more importantly, the security for those folders (and related reports). An easier alternative is the backup and restore the SSRS key. Before digging deep into that, lets understand what gets encrypted within SSRS:
- Credentials used to connect to the Report Server database itself.
- The actual symmetric key used by SSRS to encrypt data.
- Data source credentials which are stored in the database in order to connect to external databases and data sources.
- The unattended user account information which is used to connect to a remote server in order get external images or data.
Moving a Report database to an other SQL Server can be tricky at times especially when there are multiple data sources around and which one should be rightly changed afterwards.
With this query for ReportServer database you get the connection string of all Shared Data Sources to document the usage or to search for a specific server/database.
–Listing out connection strings of all SSRS Shared Data Sources
;WITH XMLNAMESPACES — XML namespace def must be the first in with clause.
(SELECT SDS.name AS SharedDsName
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5) — 5 = Shared Datasource
,DSN.value(‘ConnectString’, ‘varchar(150)’) AS ConnString
SDS.DEF.nodes(‘/DataSourceDefinition’) AS R(DSN)
) AS CON
— Optional filter:
— WHERE CON.ConnString LIKE ‘%Initial Catalog%=%TFS%’
ORDER BY CON.[Path]
Penning down this today as I recently saw my friends struggling to connect with Reporting server after migrating the report server database.
Reporting Services uses a predefined DB role called RSExecRole to grant report server permissions to the report server DB. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.
After you create the roles, you can move the report server database to new SQL Server instance.
T-SQL for adding the roles explicitly within the new instance:
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]