All the SQL Server services were starting fine. The database was responsive to user queries, yet in the event logs we were getting event ID = 120, complaining that “The report server cannot decrypt the symmetric key. Most likely, the service account or password has changed. To continue, run rsconfig.exe to update the report server account information.” What caused this SQL Reporting issue and how can it be resolved?
To see if your SQL 2008 server is getting this error, run Control Panel | Administrative Tools | Event Viewer. In the Windows Logs | Application events, use the Filter action pane item to search for event ID = 120. If the error condition is present, the event happens each time the service restarts and/or backups are taken. Thus, you can quickly reproduce the error by stopping and starting the SQL Server Reporting service.
The underlying issue is that the Reporting service stores encrypted credentials, and if you change the service account and/or reset the password it uses, it loses access to these encrypted credentials. Now, to be clear, if you login to the SQL server as the service account user and change the password nicely via the GUI, this error should not happen. But, who does that? I always jump into a domain server, reset the service account password there, then go fix whatever services need the new password. Maybe I shouldn’t be doing that?
Anyway, to fix the error, you can either (a) restore a saved set of credentials, assuming you previously saved them, or (b) delete the saved set of credentials. Since we did not have a bunch of saved credentials, it was easiest to follow the latter path. Here are the steps:
- Open the Reporting GUI:
- From the Start menu | Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | Reporting Service Configuration Manager
- When the screen loads, select “Encryption Keys” from the left
- Reset the key store:
- Click the Delete button
(notice that you can Backup and Restore using this screen) - Click Yes when prompted
- Click the Exit button to close the GUI
- Click the Delete button
- Restart the Reporting services:
- Open Control Panel | Administrative Tools | Services
- Scroll down to the “SQL Server Reporting service”
- Right-click and select Stop
- Right-click and select Start
You can validate that the error has disappeared by going back to the event viewer and searching for event ID = 120. Take this fix with a grain of salt, its only a good idea if you are not using Reporting services (and then why do you have it installed?) or know all the external credentials it is using and can recreate them as needed.