SSRS encryption key issue.

Question

Ive done the deal to move SSRS to a new server. This server serves as both the new SSRS install as well as having SQL installed and houses the SSRS metadata DB's.

It should be noted that both SSRS and SQL run under a different service account on the new server compared to the old server. In other words both services on box 1 use domain\box1 and both services on box 2 use domain\box2.

I have:

1. Backed up encryption key and restore to new server.
2. Backed up reportServer and resportServerTempDB databases and restored to new server.
3. Told the new RS to use the new DB.
4. Configured everything else on RS Config Mgr.

But my test data source is failing w "Log on failed. Ensure username and password are correct". Truthfully I don't have many data sources and could configure them manually, but may go through this exercise again so want to do it right.

Thanks!

Answer 1

Hi @chrisrdba,

Log on failed. Ensure username and password are correct"

Where did you get the error message? When you open the report?

If so, please check you have specified an unattended execution account at the Config Mgr and the account has permission to the database.

Regards，

Zoe Hui

---

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

Answer 2

For AD accounts, I'm adding the account in the data source to the local admins group that SSRS lives on (different than the sql box I'm querying data on) which works. I think theres also a scaled down permission to make the magic happen through the Local Security Policy but need one of my sysadmins to make the change to test.

For sql authenticated accounts I can work around it by adding "TrustServerCertificate=True" to the end of the connection string, but that's not a good perm solution. The internet suggests "You likely don't have a Certificate Authority(CA)-signed certificate installed in your SQL VM's trusted root store." Waiting on my sysadmin for assistance on this as well.

Figured I'd post my findings for others w the same issue.