Understanding SQL Server Reporting Services Authentication

Authentication is the security mechanism used to identify a person, process, or computer that's attempting to use a system resource. Authentication merely confirms the identity of the requestor. It doesn't include authorization, which is the process of using the requestor's identity to check whether permissions allow access to the requested resource.

Related: Implement Kerberos Delegation with SSRS

In SQL Services Reporting Services (SSRS), authentication typically begins with a request to connect to the report server but can also occur when the report server requires access to its application databases or to external data sources, as shown in Figure 1. Figure 1 delineates the authentication process when SSRS is running in native mode. I'll explain the various authentication scenarios shown in the figure and discuss the options you have for authentication protocols when implementing SSRS 2008 and later. Along the way, I'll explain how the authentication process differs when SSRS is running in SharePoint integrated mode.

Client Authentication (Native Mode)

A user can connect to a report server in a variety of ways. Commonly, a user opens a browser and connects to Report Manager to view reports or manage server properties for reports. Other user activities that require a connection to the report server include report deployment using SQL Server Business Intelligence Development Studio (BIDS), server administration using SQL Server Management Studio (SSMS) or a command-line utility, and report rendering using URL access. In addition, programmatic access to the report server can occur when a custom application implements the ReportViewer control or calls a Reporting Services Web service.

Related: Master Data Services and Data Quality Services

No matter which of these connection methods is used, the report server requires that the client authenticate the user or process that's requesting the connection. The client passes the information to the report server in an HTTP authentication request. The report server in turn passes this information to a Windows Authentication extension (which is the default) or a custom security extension. If the authentication fails, the report server sends an HTTP 401 Access Denied error to the client application.

Windows Authentication Extension

When the report server uses the Windows Authentication extension, each user must have a Windows account. It can be a local account (i.e., an account on the report server) or a trusted domain account. The account must be added as an allowed user to the report server either individually or as part of a group. Furthermore, the client application must support Windows integrated security.

The Windows Authentication extension supports several authentication types, but SSRS is initially configured to use only one authentication type. You can manually update the Program FilesMicrosoft SQL ServerReportServerRSReportServer.config file to add other authentication types, as you can see in the configuration file fragment shown in Figure 2. Although Figure 2 shows how each authentication type should appear in the configuration file, you typically wouldn't include all five. Let's review each authentication type setting separately.

RSWindowsNegotiate. If you initially set the Windows service account for the report server to NetworkService or LocalSystem in Reporting Services Configuration Manager, RSWindowsNegotiate is added to the RSReportServer.config file as the default setting. With this setting, the report server can accept requests from client applications requesting Kerberos or NTLM authentication. If Kerberos is requested and the authentication fails, the report server switches to NTLM authentication and prompts the user for credentials unless the network is configured to manage authentication transparently.

Using RSWindowsNegotiate is your best option because it provides the greatest flexibility for multiple clients in an intranet environment. Although a domain administrator needs to configure Kerberos authentication, using it is advantageous because the authenticated identity is retained without passing credentials from server to server in a distributed environment, which is an effective defensive security measure. NTLM authentication allows clients to connect to the report server securely, but it doesn't allow the report server to forward credentials to another server when external data sources are associated with reports.

Even if Kerberos authentication is correctly configured, any of the following conditions in your environment can cause the client to bypass Kerberos and use NTLM authentication instead:

RSWindowsKerberos. If you have client applications that only use Kerberos, you must add this authentication type to the report server configuration.

RSWindowsNTLM. When you initially configure the report server's Windows service account as a domain account, the default authentication type is set to RSWindowsNTLM. You can also add this authentication type to the configuration file as a fallback option if you're experiencing Kerberos authentication errors. However, you won't be able to use Windows integrated security for data source connections when the client application uses NTLM authentication.

Just because SSRS sets up NTLM authentication by default when you use a domain account as the service account, you aren't restricted to this authentication type. If you want to use Kerberos authentication instead, you can have the domain administrator register an SPN for the domain account and manually update the RSReportServer.config file to replace RSWindowsNTLM with RSWindowsNegotiate.

RSWindowsBasic. When you have an Internet-facing deployment, you might have less control over the browsers used to access the report server. In that case, you can use Basic authentication, which is supported by most browsers. However, you must set up each user with a Windows account that's either local to the report server or on the domain.

The Basic authentication type requires the user to supply a username and password when connecting to the report server. The client sends this information to the report server in plaintext. Therefore, you should require an SSL connection between the client and the report server to protect the credentials from theft in transit.

You can use Basic authentication with any of the other Windows authentication types. However, if your users are using the Safari browser, you must delete all other authentication types in the RSReportServer.config file and add only RSWindowsBasic. Whether you use RSWindowsBasic alone or with other authentication types, you can optionally provide values for the following properties, as shown in Figure 3:

Authentication Persistence

In Figure 3, notice the EnableAuthPersistance property. This property controls whether the report server requires authentication for each client request or only once per connection. By default, the EnableAuthPersistance property is set to True in the RSReportServer.config file. This setting applies the security context of the client's first request to each subsequent request. However, if your environment includes a proxy server that sends requests from multiple users as a single connection, you should disable this property by setting its value to False. Disabling it forces the report server to authenticate each request individually.

Extended Protection for Authentication

If you're using SSRS 2008 R2, you can enable the Extended Protection feature in SSRS and in the report server's OS to enforce channel binding and service binding for authentication requests. (Authentication will fail if you enable this feature in SSRS but not the OS.) Channel binding ensures the integrity of the communication channel between two points using a channel binding token. Service binding validates the recipient of an authentication request using an SPN.

Besides enabling Extended Protection in both SSRS and the OS, you must install an SSL certificate on the report sever. You must also edit the RSReportServer.config file to specify values for the following properties, as shown in Figure 4:

Custom Security Extension

In some cases, such as when you have an Internet-facing deployment and you don't want to maintain Windows accounts for users, you can replace Windows authentication with your own custom security extension. However, you can't combine a custom security extension with Windows authentication. If you need to support both types of authentication, you must create two report server instances. After you deploy a custom security extension to the report server, you must configure the RSReportServer.config file by replacing the authentication types with Custom.RSWindows. You must also update the Web.config files for both the report server and Report Manager, as described in the MSDN article "How to: Configure Custom or Forms Authentication in Reporting Services."

Client Authentication (SharePoint Integrated Mode)

In a SharePoint Server 2010 integrated mode deployment, the authentication process is similar to authentication in a native mode deployment, with one additional authentication point, as shown in Figure 5. After the user is authenticated when accessing the SharePoint site, the SharePoint site's application pool identity must be authenticated before connecting to the site's content database to retrieve the requested content. Then, just as in native mode, the user's request for a report requires a connection to the report server database and a connection to the data source host.

The Web application associated with the SharePoint site determines the authentication method used for the client connection. When you create the Web application in Central Administration, you can specify either classic mode authentication or claims-based authentication.

Classic mode authentication. This authentication method relies on Windows integrated security. The client application presents the SharePoint server with the credentials used to run the browser or, in the case of a custom application, the credentials used to run the current thread. When you select this method, you must further specify Kerberos or NTLM as the authentication type.

Claims-based authentication. This authentication method relies on the use of a token for an identity that has been issued by a trusted authority so that private information doesn't need to be passed around. By using claims-based authentication, you can more easily support access to a SharePoint site by internal and external users.

Claims-based authentication can be used in combination with more familiar authentication methods. For example, you can configure claims-based authentication to use Windows authentication (NTLM, Negotiate, or Basic) to translate a Windows identity into a claims identity. Another option is using forms-based authentication to prompt the user for credentials in a login page that uses ASP.NET membership and role providers to establish the claims identity. A third option is identifying a Trusted Identity Provider that issues tokens to a client that the SharePoint server will accept for authentication.

SSRS Authentication (SharePoint Integrated Mode)

When you configure SSRS to run in SharePoint integrated mode, you must specify an authentication mode on the Reporting Services Integration page in Central Administration. You can find this page in the General Application Settings section. SharePoint uses either Windows authentication or Trusted Account mode to authenticate with the report server.

Windows authentication. With this authentication mode, SharePoint uses a delegation model to perform its own authentication when a user accesses a site. SharePoint also uses the delegation model to forward the authenticated identity to the report server for another round of authentication, which requires the user to have a login for the report server. If Kerberos is enabled on your network, Windows authentication is a good option. You can then configure report data sources to use Windows integrated security to pass the user's identity to the database server.

Trusted Account mode. When the report server uses Trusted Account mode for authentication, SharePoint doesn't send the client identity but instead uses the application pool identity assigned to the SharePoint Web application hosting the requested site. The account assigned as the application pool identity must be in the list of service accounts in the Security Configuration section on the Operations page of Central Administration. SharePoint also generates a token, which it sends with the request to the report server. The token is passed back to SharePoint to provide the security context for the response. This token is valid only for the originating site to reduce the surface area for attacks. Data sources must use stored credentials or prompted credentials because Windows integrated security isn't supported when the report server is in Trusted Account mode.

If you configure a Web application to use claims-based authentication, the SSRS proxy in SharePoint will use Trusted Account mode authentication (even if you configured it to use Windows authentication) and forward a SharePoint user token to the report server. The report server, in turn, generates the appropriate security context for the user token and returns the request.

Reporting Services Service Authentication (Both Modes)

Whether you're running SSRS in native mode or SharePoint integrated mode, the Reporting Services service must be authenticated when connecting to the report server database in response to a report request or content management task. In fact, any activity that requires the report server to store the application state, retrieve reports, or retrieve other report-related data (but not including execution of queries for reports) also requires authentication of the Reporting Services service.

You configure the identity and the authentication mode on the Database page of Reporting Services Configuration Manager. There are three authentication options:

No additional configuration is necessary. Reporting Services Configuration Manager runs a script to allow the applicable identity to connect to the database server.

Data Source Authentication (Both Modes)

When a report executes, the report server provides credentials for authentication to the server hosting the data source for the report. When you configure the data source, you need to choose one of the following authentication types.

Windows integrated security. You should use this authentication type in only two scenarios. You'd use it when your data source and the report server are located on the same server. You'd also use it when your network is configured for Kerberos and the report server is configured to use Negotiate or Kerberos authentication. Otherwise, the report server won't pass user credentials to the data source, nor will the report server prompt the user for credentials.

User-supplied credentials. You can prompt the user for credentials when the report executes, although users might grow weary of this method if they frequently request a report. By default, the report server assumes the credentials represent a database login, but you can select a check box to change the usage to Windows credentials. If database credentials are used, the report server connects to the database server using the service account or the unattended execution account specified in Reporting Services Configuration Manager.

Stored credentials. If you need to execute reports on a scheduled basis for caching, snapshots, or subscriptions, you must use stored credentials and supply the credentials in the data source. If you don't need to send the user's identity to the data source to apply row-level security to query results, using stored credentials is a good option. As with user-supplied credentials, you can specify whether the database server or Windows will authenticate the credentials. In addition, you can specify whether the report server can impersonate the user after making the connection to the database.

No credentials. You can use this option for data sources that don't require authentication or if you provide credentials in the data source connection string (which isn't a recommended approach) as long as the unattended execution account is configured. The report server connects to the data source server using the unattended execution account, although authentication will fail if SQL Server Analysis Services (SSAS) is the data source.

Not a Straightforward Task

As you can see, configuring authentication in SSRS isn't always a straightforward task because it occurs at multiple points and involves multiple technologies. Network settings, browser support for authentication modes, and database configuration all affect the range of options available to you for implementing an authentication method. This article provides an overview of these various options. If you'd like to learn more, check out the resources listed in the Learning Path.

LEARNING PATH

For more information about SSRS authentication in native mode:

Extended Protection for Authentication with Reporting Services

How to: Configure Basic Authentication in Reporting Services

How to: Configure Custom or Forms Authentication in Reporting Services

How to: Configure Windows Authentication in Reporting Services

Manage Kerberos Authentication Issues in a Reporting Services Environment

Security Overview for Reporting Services in Native Mode

For more information about SSRS authentication in SharePoint integrated mode:

Claims Authentication and Reporting Services

Configure Kerberos Authentication for SharePoint 2010 Produc

Security Overview for Reporting Services in SharePoint Integrated Mode

For background information about the types of authentication:

A Guide to Claims-Based Identity and Access Control

Microsoft Kerberos

Microsoft NTLM