Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change the SQL Server Reporting Services Service Account


By:   |   Last Updated: 2019-01-03   |   Comments (1)   |   Related Tips: > 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.
sql server reporting services manager
  1. 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.
ssrs backup encryption key
  1. Enter a Password for the encryption key file and press the OK button.
Specify the Location and the Password of the Encryption Key.
  1. The Encryption Key backup gets created as shown below.
Encryption Key gets Created.

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.
ssrs change service account
  1. 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.
  2. Restart the SQL Server Reporting Services service.
  3. 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.
ssrs home page
  1. 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.
ssrs restore encryption key
  1. 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.
Specify the Location of the Encryption Key along with the Password. Password should be the same as we have specified during it
  1. 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.
  2. 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:

"The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled)"

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.

In case if things dont work well then we see this situation which is pretty messy.
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.


Last Updated: 2019-01-03


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, January 09, 2019 - 11:20:33 AM - Sureindran Nadesan Back To Top

 How do you revert to the original account?


Learn more about SQL Server tools