Change the SQL Server Reporting Services Service Account

By: Satnam Singh   |   Updated: 2019-01-03   |   Comments (6)   |   Related: > Reporting Services Administration

Problem

Recently, we received a request where we needed to change the service account for SQL Server Reporting Services to a new account. We wanted to use a new account, because it would only be applicable for this particular instance. The Service Account that we used earlier was common for all production SQL Server instances which we wanted to eliminate.

Solution

In order to change the SQL Server Reporting Services (SSRS) Service Account, we need to perform the following steps:

• On the SSRS Home Page, click on the Site Settings and Folder Settings and ensure that the New Service Account has been provided the appropriate role.
• Ensure that the New Service Account has access to the Data Sources.
• At the SQL Server level, ensure the new Service Account has the RSExecRole inside the ReportServer and the ReportServerTempDB databases.
• Issue a full backup of all the databases along with the ReportServer and ReportServerTempDB database on the SSRS SQL Server instance.
• Create a backup of the Encryption Key using the steps below.

Create Backup of Reporting Services Encryption Keys

1. Open Reporting Services Configuration Manager on the server.
2. Click on the Encryption Keys as shown in the screen capture below.

3. On the right side, click on the Backup button and a new window opens. Specify the location and the name of the Encryption Key Backup file. The extension of the Encryption Key Backup file will be *.snk.

4. Enter a Password for the encryption key file and press the OK button.

5. The Encryption Key backup gets created as shown below.

Change the SQL Server Reporting Services Service Account

Once the Encryption Key Backup is created successfully, the next step is to change the Service Account.

1. In the Reporting Services Configuration Manager, click on Service Account as shown below.
2. Enter the new Service Account and Password and then press the Apply button.  As soon as you click the Apply button, the system will create a backup of the Encryption Key then change the Service Account and then Restore the Encryption Key automatically.

3. Once the above step is performed, run services.msc and find the service for SQL Server Reporting Services and verify that the new account is being used for SQL Server Reporting Services.
4. Restart the SQL Server Reporting Services service.
5. Open the SSRS Home Page and ensure that the Folders and Reports are displayed properly.  Once the change happens successfully, the Folders should be shown properly on the SSRS Home Page.

6. Inside the Folders on the Home Page, ensure the Reports are there and it doesn't ask for a User Name and Password. If everything looks good then it means everything has gone smoothly.

As discussed above, the system automatically backups and restores the Encryption Key, but in some cases an error is encountered. In such cases, we will need to manually restore the Backup of the Encryption Key as shown below, this is why we created a backup in the beginning of the tip.

Manually Restore the Reporting Services Encryption Key

1. If the Encryption Key needs to be restored manually, in the Reporting Services Configuration Manager click on the Encryption Keys and click on the Restore button as shown below.

2. A new window opens as shown below.  Specify the File Location of the Encryption Key along with the Password. The password should be the same as specified during the creation.

3. Once the above step is completed then restart the SQL Server Reporting Services service and you should be able to view all the reports successfully without any issues.
4. In some cases, I have seen that the SSRS Reports writes data onto a particular location. Before the change, ensure the new Service Account has the necessary access to that particular file location.

Important Point of Consideration

If you do not create a backup and restore the Encryption Key, but instead directly update the SSRS Service Account, when you try to view the SSRS Home Page you will get the following error message:

In such cases, I have seen people delete the Encryption Key to try to make it work which is never successful. Instead you get the following screen. This is a pretty messy situation and it could be avoided if the operation is performed as per the steps above.

Next Steps

• Changing a SSRS Service Account is possible only when implemented accurately otherwise there can be situations where a restore of the ReportServer/ReportServerTempDB database is needed which shouldn't be the case.
• Check out these other Reporting Services tips.
• Check out the SQL Server Reporting Services Tutorial.

About the author

Satnam Singh is a Lead SQL Server DBA with Capgemini in India with 12 years of experience on Microsoft SQL Server Technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips