SSRS Configure the Unattended Execution Account

One way to run scheduled reports is to use SQL server logins and store the credentials in the data source of the report. For every SQL server that needs to be accessed to read the report data the SQL account credentials must be maintained. The same credentials must be saved in every data source on the report server.

An alternative is to run SQL reports with an AD windows account. The AD account has to be granted the same rights to read report data, but without saving credentials. The Reporting Service Server stores the credentials once in the Unattended Execution Account of SSRS.

Configure the Unattended Execution Account

Start the Reporting Services Configuration manager

Connect to the Reporting services Server

In the left pane of the configuration manager, select ‘Execution Account’. Check ‘Specify an account’ and enter the AD account credentials.

Click on ‘Apply’. A dialog box will open to give the name for a key file with it’s own password.

Give the unattended account read rights in the SQL Server database

Add the AD account as a windows login to the SQL Server. Add the login as user to the database. Grant the DB user sufficient select and/or execute rights in the database to read the report data.

Use the unattended account in the Report Data Sources

The Connection string must use ‘Integrated Security=SSPI’

For Shared data sources on a report server. Browse to the Data Sources folder of the report server.

Select a Data Source and click on it , or open the dropdown and select ‘Manage’.

In the properties tab, in the connection string textbox add the ‘;Integrated Security=SSPI’ Select ‘Credentials are not required’.

click Apply.

Conclusion

Account administration is managed by AD admins. The SQL Admin only needs to define the AD as a user login on the SQL server level and grant access rights on the database level. No need to maintain passwords for every SQL server.

The Report server stores the unattended account once, hence it is no longer necessary to maintain SQL Login credentials and passwords for every Data Source deployed on the report server.