How To Change Domain_6 Settings To View Sensitive Content

Change the SQL Server Reporting Services Service Business relationship

By:   |   Updated: 2022-01-03   |   Comments (v)   |   Related: > Reporting Services Administration

Problem

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

Solution

In gild to change the SQL Server Reporting Services (SSRS) Service Account, nosotros need to perform the post-obit steps:

• On the SSRS Home Page, click on the Site Settings and Folder Settings and ensure that the New Service Business relationship has been provided the advisable role.
• Ensure that the New Service Account has admission to the Data Sources.
• At the SQL Server level, ensure the new Service Account has the RSExecRole within 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 Managing director on the server.
2. Click on the Encryption Keys every bit 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 cardinal file and press the OK button.

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

Change the SQL Server Reporting Services Service Account

In one case the Encryption Key Backup is created successfully, the adjacent step is to change the Service Account.

1. In the Reporting Services Configuration Managing director, click on Service Account equally shown below.
2. Enter the new Service Account and Password and then printing the Apply push.  As soon equally yous click the Apply button, the system will create a backup of the Encryption Fundamental then change the Service Account and so Restore the Encryption Key automatically.

3. Once the to a higher place stride is performed, run services.msc and discover the service for SQL Server Reporting Services and verify that the new account is beingness used for SQL Server Reporting Services.
4. Restart the SQL Server Reporting Services service.
5. Open up the SSRS Home Page and ensure that the Folders and Reports are displayed properly.  In one case 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 information technology doesn't ask for a User Name and Password. If everything looks good and then it means everything has gone smoothly.

As discussed above, the system automatically backups and restores the Encryption Fundamental, only in some cases an error is encountered. In such cases, we volition need to manually restore the Backup of the Encryption Primal as shown below, this is why we created a fill-in in the offset 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 beneath.

2. A new window opens equally shown beneath.  Specify the File Location of the Encryption Primal forth with the Password. The countersign should be the same as specified during the creation.

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

Important Point of Consideration

If y'all exercise non create a fill-in and restore the Encryption Key, but instead straight update the SSRS Service Account, when you try to view the SSRS Home Page you volition become the post-obit error message:

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

In such cases, I accept seen people delete the Encryption Fundamental to effort to make it work which is never successful. Instead you get the following screen. This is a pretty messy situation and it could exist avoided if the functioning is performed as per the steps above.

Adjacent Steps

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

Related Articles

Popular Articles

Virtually the writer

Satnam Singh is a Atomic number 82 SQL Server DBA with Capgemini in Republic of india with 12 years of experience on Microsoft SQL Server Technologies.

View all my tips

Article Terminal Updated: 2022-01-03

Source: https://www.mssqltips.com/sqlservertip/5787/change-the-sql-server-reporting-services-service-account/

Share this post