SSRS move to another new server , reports were missing the data source.

Question

SSRS move to another new server , reports were missing the data source.

Kerry Ou 226

Hi , I restore [ReportServer] and [ReportServerTempDB] from old SQL server.
All reports are there, but can't be opened, need to input data source user name and password.
Am I missing something? I don't want to set data source one by one. Please help.
Thank you.

1 answer

Your answer

Answer 1

Isabellaz-1451 3,616

Hi @Kerry Ou

You need back up the Encryption key in the old reporting service configuration. and restore it in new report service configuration

Best Regards,
Isabella

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Kerry Ou 226 Reputation points

2021-11-04T09:11:47.477+00:00

I already did this. But same problem.
Willsonyuan-msft 111 Reputation points

2021-11-05T01:34:11.07+00:00

Hello KerryOu-8610,

Welcome to Microsoft Q&A, it worked in my environments. Did you try to restart the SQL Server report service after restore the encryption keys. As you can see, if we did not restore the encryption keys, all stored connection strings, credentials and encrypted values in subscription will be deleted. After deleting the encrypted content, we must redefine all data source connections and subscriptions used on the report server.
Kerry Ou 226 Reputation points

2021-11-05T02:51:05.717+00:00

Yes, I already restart the SQL Server report service. And than I try to delete and restore again. But same.Is there any other way? Thank you so much.
Willsonyuan-msft 111 Reputation points

2021-11-05T03:46:14.093+00:00

Hello KerryOu-8610 ,

Thanks for your quick response.

In this scenario, you may try to backup the encryption keys in the new reporting server, then delete the encryption keys in the new reporting server, then restore the encryption keys which backed up from the old server.
Willsonyuan-msft 111 Reputation points

2021-11-05T03:49:54.883+00:00

If above did not work either, you may start from scratch again and just restore the reports server database and reportservertempdb, connect to the restored database using the Reporting Services Configuration Manager and then restore the encryption key.
Kerry Ou 226 Reputation points

2021-11-05T05:21:32.223+00:00

Thank you for your reply.
So, I need to :
1.delete the [reportserver] and [ReportServerTempDB] database from new SQL server.
2.Restore the [reportserver] and [ReportServerTempDB] database from Old SQL server.
3.Restore the encryption key.
What did I miss? What advice do you have to share?
Thank you again.
Willsonyuan-msft 111 Reputation points

2021-11-05T05:47:29.197+00:00

Here are the official documents that you might want to read through:
https://learn.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-ver15
Willsonyuan-msft 111 Reputation points

2021-11-05T06:00:32.61+00:00

Further more, we need to make sure that there has a record for the old server metadata.
Can you run a select on table - [reportserver].[dbo].[keys] and see it will return any result.
Willsonyuan-msft 111 Reputation points

2021-11-05T06:24:08.953+00:00

Symmetric key: Used to encrypt credential data. It is not encrypted itself. Once get Symmetric key, credential data can be decrypted by PBIRS.
Public and private keys: Public and private keys are created by the operating system (Based on the current SSRS services account in each machine). They are used to protect the symmetric key. A public and private key pair is created for each report server instance that stores sensitive data in a report server database.
Encryption Key: use the public and private key of the Report Server service to encrypt symmetric key and store in DB Keys table
SymmetricKey colume in [ReportServer].[dbo].[Keys] is encryption key (encrypted symmetric key)
Kerry Ou 226 Reputation points

2021-11-05T07:19:36.213+00:00

[reportserver].[dbo].[keys] has 2 records.
I will try to restore reportserver database again. Thanks a lot.
Willsonyuan-msft 111 Reputation points

2021-11-05T07:53:40.813+00:00

You are welcome.
Kerry Ou 226 Reputation points

2021-11-10T08:34:09.113+00:00
Hello Willsonyuan-MSFT.
I I have completely followed the steps on MSDN, but it still error.
I have some questions:

Could it be two servers with different names?

The reporting services on both servers are running. The reporting data source on the new server is connected to the old server. Is this related?
Thank you.
Willsonyuan-msft 111 Reputation points

2021-11-10T08:51:38.947+00:00

Hello KerryOu-8610,

Thanks for your feedback.

1.Could it be two servers with different names?
According to your screenshot, [reportserver].[dbo].[keys] table should have 3 records , which also include your old server name "chem", you might need to delete the record with the old server name manually, and restart SSRS service.

2.The reporting services on both servers are running. The reporting data source on the new server is connected to the old server. Is this related?
We can only connect to the report server database with one instance. So still the solution for this issue is to delete the record with the old server name manually, and restart SSRS service.

but before do this, please make sure to backup the report server database [ReportServer] and [ReportServerTempDB].

Share via

SSRS move to another new server , reports were missing the data source.

1 answer

Your answer