Store Credentials in a Reporting Services Data Source

Applies to: SQL Server 2016 (13.x) Reporting Services and later

You can configure stored credentials that a Reporting Services report server uses to access external data for a report. Stored credentials are used if the report runs unattended, for example a Reporting Services subscription that publishes a report as an e-mail. The report server retrieves and uses the credentials when report processing is scheduled or triggered. This topic walks you through configuring stored credentials for both Native mode and SharePoint mode report servers.

Security policy requirements for stored credentials

It is required that the account you use for stored credentials, is configured for one of the following security policies on the report server. It is recommended you select the policy with the minimum level of permissions you require for your environment.

1. Allow log on locally. For more information, see Allow log on locally.

2. Log on as a batch job. For more information, see Log on as a batch job.

3. For general information on policies, see Edit security settings on a Group Policy object.

Configure stored credentials for a report-specific data source (Native mode)

1. In the web portal, browse to the folder that contains the report. Click the ellipsis (...) in the upper-right corner of the report tile.

2. Click Manage and then click Data Sources.

3. Select A custom data source.

4. In the Data Source Type list, select the data processing extension that is used to process data from the data source.

5. For Connection String, specify the connection string that the report server uses to connect to the data source. The following example illustrates a connection string used to connect to the SQL Server AdventureWorks2022 database:

   data source=<servername>;initial catalog=AdventureWorks2022  
   

6. For Connect Using, select Credentials stored securely in the report server.

7. Type a user name and password.

   • If the account is a Windows domain user account, specify it in this format: <domain>\<account>, and then select Use as Windows credentials when connecting to the data source.

   • If the user name and password are database credentials, do not select Use as Windows credentials when connecting to the data source. If the database server supports impersonation or delegation, you can select Impersonate the authenticated user after a connection has been made to the data source.

8. Click Apply.

   Security policy requirements for stored credentials

Configure stored credentials for a report-specific data source (SharePoint mode)

1. Browse to the document library that contains the report and then click the open menu .

2. Click second open menu and then click Manage Data Sources.

3. Click the name of the Custom data source you want to configure with stored credentials.

4. In the Data Source Type list, select the data processing extension that is used to process data from the data source.

5. For Connection String, specify the connection string that the report server uses to connect to the data source. The following example illustrates a connection string used to connect to the SQL Server AdventureWorks2022 database:

   data source=<servername>;initial catalog=AdventureWorks2022  
   

6. For Credentials, select Stored Credentials.

7. Type a user name and password.

   • If the account is a Windows domain user account, specify it in this format: <domain>\<account>, and then select Use as Windows credentials when connecting to the data source.

   • If the user name and password are database credentials, do not select Use as Windows credentials. If the database server supports impersonation or delegation, you can select Set execution context to this account.

8. Click ok.

   Security policy requirements for stored credentials

Configure stored credentials for a shared data source (Native mode)

1. In the web portal, browse to the shared data source item.

2. Click the ellipsis (...) in the upper-right corner of the report tile > Manage.

3. In the Type list, specify the data processing extension that is used to process data from the data source.

4. For Connection String, specify the connection string that the report server uses to connect to the data source. Microsoft recommends that you do not specify credentials in the connection string.

   The following example illustrates a connection string used to connect to the local SQL Server AdventureWorks2022 database:

   data source=<localservername>; initial catalog=AdventureWorks2022  
   

5. Type a user name and password.

   • If the account is a Windows domain user account, specify it in this format: <domain>\<account>, and then select Use as Windows credentials when connecting to the data source.

   • If the user name and password are database credentials, do not select Use as Windows credentials when connecting to the data source. If the database server supports impersonation or delegation, you can select Impersonate the authenticated user after a connection has been made to the data source.

6. Click Apply.

   Security policy requirements for stored credentials

Configure stored credentials for a shared data source (SharePoint mode)

1. In the document library, browse to the shared data source item.

2. Click the context menu and then click the second context menu .

3. Click Edit Data Source Definition.

4. In the Data Source Type list, specify the data processing extension that is used to process data from the data source.

5. For Connection String, specify the connection string that the report server uses to connect to the data source. Microsoft recommends that you do not specify credentials in the connection string.

   The following example illustrates a connection string used to connect to the local SQL Server AdventureWorks2022 database:

   data source=<localservername>; initial catalog=AdventureWorks2022  
   

6. Type a user name and password.

   • If the account is a Windows domain user account, specify it in this format: <domain>\<account>, and then select Use as Windows credentials.

   • If the user name and password are database credentials, do not select Use as Windows credentials. If the database server supports impersonation or delegation, you can select Set Execution context to this account.

7. Click Ok.

   Security policy requirements for stored credentials

Related content

• Specify Credential and Connection Information for Report Data Sources