Datasets/data sources no longer work when deploying reports to a new server

Question

I have a set of reports on a SSRS 2012 server and they work fine. We are moving to a new server, so I changed the server in the report property and redeployed. The reports are on the new server, but none of the shared datasets and data sources work anymore. I get this error:

Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)"

All the datasets and data sources use predefined credentials. I look at the reports in Visual Studio and I can connect just fine. Can anyone tell me why deploying to a new server breaks everything?

Answer 1

If your data sources contain stored credentials, you need to backup the encryption key from the original server and restore it in the new one. Otherwise those shared data sources won't work. If this is the case and you know the credentials, you can edit manually all those shared data sources. Credentials stored in embedded data sources won't work as well if you don't restore the encryption keys.

You can query the SSRS database in several ways to discover all those Shared Data Sources and embedded ones, you can find them in this site. One example could be Listing all Data Sources and their Dependencies (reports, items, etc) in SQL Server 2008 R2

Hope this helps