Implement Kerberos Delegation with SSRS

When I talk with DBAs and IT professionals about security, I usually hear a few groans or see some eyes rolling when I bring up the subject of Kerberos delegation, which many people refer to as simply Kerberos. Most people I encounter seem to think Kerberos is difficult to set up. My opinion is that Kerberos is easy to set up, but it's also easy to mess up. Finding where it's messed up is often the hardest part. Furthermore, it's challenging to find correct, clear, and concise information about configuring Kerberos.

To help you better understand Kerberos, I'll explain when you should consider using Kerberos for your SQL Server Reporting Services (SSRS) environment. I'll also provide an overview of the process of authentication and delegation when using the Kerberos protocol. Then, I'll lead you through the steps necessary to implement Kerberos successfully in a way that I hope you'll find clear and concise. The information here applies to SSRS 2008 and later for a native-mode report server.

Why Use Kerberos?

When you want to control data security at the database level, you need the security context of the user running the report. If you implement SSRS on the same server as the SQL Server Database Engine that hosts both the SSRS databases and data sources you query for reports, you can rely on Windows integrated security without any additional configuration. The user credentials pass from the client to the report server using the NTLM protocol. Any subsequent connections to a data source on the same server continue to use the same credentials, no matter whether the data source is SQL Server, SQL Server Analysis Services (SSAS), or a file. Those credentials can be used repeatedly, but the key is they never leave the server.

If you set up reports to query a data source on a remote server, you'll find that authentication fails by default. NTLM allows credentials to pass correctly from the client to the first server in the deployment topology, no matter whether the user connects to Report Manager on a native-mode report server or to a SharePoint library on a SharePoint integrated-mode report server (hop 1). However, NTLM doesn't allow credentials to pass from the report server to any other server (hop 2). Instead, the report server attempts an anonymous connection to the back-end server and is denied access, as shown in Figure 1.

 
Figure 1: Failing to Authenticate on the Back-End Server with NTLM

The scenario in which both the report server and the back-end server must authenticate the user's identity is known as a double hop. NTLM doesn't support a double-hop scenario. You can work around this NTLM limitation by setting up stored credentials and using the User!UserID variable to apply row-level filters. However, you must build additional logic into your reports and in your database to support this approach.

Unlike NTLM, Kerberos supports a double-hop scenario. Kerberos also provides more robust security and lets you use roles to more easily manage security at the database level for SQL Server and SSAS.

How Does Kerberos Delegation Work?

Put simply, the Kerberos protocol relies on encrypted tickets from the domain controller (DC) that the client, report server, and back-end server use to prove they are what they say they are. Once the two-way authentication succeeds at each hop, a secure session exists.

It's important to understand that the connection between the report server and back-end server is made in the security context of the user. Credentials never pass from the client to the report server or from the report server to the back-end server. Instead, the report server impersonates the user, which is the delegation part of the process.

Before I explain how to set up Kerberos, let's take a high-level look at the steps involved to authenticate the client in a double-hop scenario, as shown in Figure 2.

Figure 2: Creating a Secure Client-Server Session with Kerberos 

1. When the user logs in, a request is sent to the authentication service, a component of the Key Distribution Center (KDC) running on the DC.

2. The KDC returns an encrypted ticket-granting ticket (TGT) to the client and a TGT session key. The TGT confirms that the user has the correct Windows account name and password. The TGT session key is necessary for requests to connect to network resources.

3. When the user uses a client (a browser or customer application) that's configured to use Windows integrated security to connect to a report server that's configured to use Kerberos, the report server refuses the connection (signified by a red X in Figure 2) and requests authentication.

4. The client sends a request to the ticket-granting service (TGS), which is another component of the KDC. The request includes the TGT (which was encrypted to prevent tampering), the TGT session key, the username, and the name of the service for which the connection is requested.

5. The TGS decrypts the TGT and uses the TGT session key to decrypt the connection request. Part of this process confirms that the username sending the request matches the username included in the connection request. The TGS returns an encrypted service ticket containing another session key to the client. The encryption uses the master key for the service account.

6. The client attempts to connect to the report server again and sends an application request. This application request includes the encrypted service ticket, the TGT, and an authenticator encrypted with the session key. The report server uses the master key for its service account to decrypt the service ticket to get the session key, which it then uses to decrypt the authenticator. If the name in the authenticator matches the name associated with the client, the report server accepts the connection request. All communications between the client and report server continue to use the session key to encrypt and decrypt messages.

7. When the report server requests a connection from the back-end server, another cycle begins with a refused connection and an authentication request from the back-end server, as in step 3.

8. The report server sends a request to the TGS on behalf of the client, sending the original TGT and requesting a new service ticket, as in step 4.

9. The TGS sends the service ticket to the report server, as in step 5.

10. The report server establishes a connection with the back-end server, as in step 6.

Preparations for Implementation

Before you can implement Kerberos with SSRS, you need to prepare by gathering information about your environment. For a native-mode report server, you need the server names and service account information for the report server and any other server to which a connection is required for accessing data sources. You also need the server name hosting the report server database (if it's separate from the report server) and the service account for SQL Server. Table 1 (below) shows the sample information I'll use to explain the following implementation steps:

1. Configure the DC.

2. Configure the Service Principal Names (SPNs).

3. Configure the servers or service accounts as trusted for delegation.

4. Configure the user accounts.

5. Configure the Kerberos authentication type for SSRS.

6. Configure the local security policy settings.

7. Test the delegation.

Step 1: Configure the DC

The DC must have one of the following domain functional levels set: Windows Server 2008 R2, Windows Server 2008, or Windows Server 2003. Only a domain administrator can raise the domain functional level if doing so proves to be necessary. You can confirm the current domain functional level in the Microsoft Management Console (MMC) Active Directory Domains and Trust snap-in, which you can access in the Administrative Tools program group. The minimum domain functional level you need for using Kerberos with SSRS is Windows 2003.

Step 2: Configure the SPNs

Before I explain which SPNs you need, let's first review what an SPN is and how to add one to Active Directory (AD). An SPN is an identifier for a service running on a computer. It must be unique across an entire forest of domains. It identifies the service, the NetBIOS or DNS name of the computer running the service, and the service account.

When you add a computer to AD, a host SPN is automatically added to cover default services running on the computer under either the Local System or NETWORK SERVICE built-in accounts, including the SQL Server Database Engine. An SPN is also automatically added if the Database Engine service is running under a domain administrator account on any OS or under a virtual account or managed service account on Server 2008 R2 or Windows 7.

You have two options for configuring SPNs. You can use the Setspn command-line utility. If you prefer a GUI, you can open ADSI Edit from the Administrative Tools program group. Both of these tools install by default on a DC running Server 2008 or later. If you're running Windows 2003, these tools are available only when you install the Windows Support Tools. Regardless of which tool you use, you must be a domain administrator to configure SPNs.

If you use ADSI Edit, you must connect to the DC, then expand the folders to locate the account for which you need to add the SPN. If the service account is a built-in account, expand the CN=Computers folder and right-click the computer account. Otherwise, expand the CN=Users folder and right-click the service account. Next, select Properties on the context menu. In the dialog box, scroll to locate the servicePrincipalName attribute, select it, and click the Edit button. In the Multi-valued String Editor dialog box, you'll see any existing SPNs, as shown in Figure 3.

Figure 3: Using ADSI Edit to Add an SPN 

In this example, di06 is a back-end server hosting a SQL Server data source. It's running the Database Engine under the NETWORK SERVICE account using the default instance and default port. Therefore, an SPN is created automatically for the Fully Qualified Domain Name (FQDN) MSSQLSvc/di06.adventureworks.com. Generally, you should include an SPN for both the FQDN and the NetBIOS name of the computer to ensure an SPN can be found at authentication time regardless of the method used to resolve the computer name. To add the SPN for this example, you type MSSQLSVC/di06 in the Value to add field and click the Add button.

If you want perform this task using the Setspn utility, you should first check the existing SPNs for a computer using the -L argument. (You can list SPNs with this command without having domain administrator privileges.) You use the computer name as the third argument, like this:

setspn -L di06 

If the SPN you need isn't listed, you can add it using the -S argument, which makes sure a duplicate SPN doesn't exist before adding the new SPN. (Duplicate SPNs was once a common cause of Kerberos failures.) You then combine the service name (e.g., MSSQLSvc) with a forward slash (/) and the computer name (NetBIOS or FQDN) to use as the third argument. The fourth argument is the service account, which uses the format domainuser for a Windows account or the computer name followed by a $ symbol for a built-in account. For example, to create an SPN in which NETWORK SERVICE is the service account for the Database Engine on the di06 server, use the command

setspn -S MSSQLSvc/di06 di06$ 

To delete an SPN, use the same syntax that you use for creating an SPN, but replace -S with -D:

setspn -D MSSQLSvc/di06 di06$ 

Now that you know how to add an SPN, let's review the list of SPNs required for each server and service running in the example environment described in Table 1.

Report server. If you use a built-in account to run the Reporting Services service, you don't configure SPNs, whether or not you use host headers. However, when you use a domain account, you must add SPNs using the HTTP service type, like this:

setspn -S HTTP/denali01.adventureworks.comadventureworksRSServicesetspn -S HTTP/denali01 adventureworksRSService 

(Note that although the first command wraps here, you'd enter it all on one line. The same holds true for the other commands that wrap.)

If you have multiple HTTP services on the same server, each using different ports and different service accounts, there's no way to differentiate the SPNs because Kerberos ignores the port number for this service type. In this case, you should configure a unique host header for each port so that you can create a separate SPN for each site. For a host header such as AWReports when you're running the Reporting Services service under a domain account, you need to create an SPN, like this:

setspn -S HTTP/AWReports.adventureworks.comadventureworksRSServicesetspn -S HTTP/AWReports adventureworksRSService 

SQL Server Database Engine. Beginning with SQL Server 2008, the inclusion of a port number isn't required in the SPN, but many Kerberos experts recommend including the port number anyway. That means you must create four SPNs for a Database Engine:

setspn -S MSSQLSvc/di06.adventureworks.com di06$setspn -S MSSQLSvc/di06 di06$setspn -S MSSQLSvc/di06.adventureworks.com:1433 di06$setspn -S MSSQLSvc/di06:1433 di06$ 

If you're running the Database Engine as a named instance, such as Corp, you append a colon and the instance name to the computer name:

setspn -S MSSQLSvc/di06.adventureworks.com:Corp di06$setspn -S MSSQLSvc/di06:Corp di06$

SSAS. You must create SPNs for SSAS, no matter whether the service account is a built-in account or a domain account. You use the same SPN syntax for SSAS that you use for the Database Engine, except you replace MSSQLSvc with MSOLAPSvc.3. You can specify the port number or instance name if necessary.

Step 3: Configure the Servers or Service Accounts as Trusted for Delegation

If you haven't set up the SPNs, don't start this step until you've added them. You can't access the Delegation properties for a computer or an account until at least one SPN exists.

Configuring a server or service account as trusted for delegation means that you're authorizing it to impersonate a user to use resources on another computer. Open the MMC Active Directory Users and Computers snap-in on the DC. If SSRS is running under a built-in account, right-click the computer with the report server and select Properties. If it's running under a domain account, right-click the user account and select Properties. On the Delegation tab, select the Trust this computer for delegation to any service (Kerberos only) option. For a more secure environment, you can use constrained delegation by selecting the Trust this computer for delegation to specified services only option. You must then manually specify each service eligible for delegation, as shown in Figure 4.

Figure 4: Configuring Constrained Delegation 

Step 4: Configure the User Accounts

User impersonation requires that the client supports the Kerberos protocol and that the impersonated user has an account in AD. For delegation to work properly, you must ensure that each user account you authorized to access the report server is correctly configured. In AD, go to the Account Options list on the Account tab of the user and verify that the Account is sensitive and cannot be delegated option is not selected, as shown in Figure 5.

Figure 5: Configuring a User Account

Step 5: Configure the Kerberos Authentication Type for SSRS

The report server configuration file, RSReportServer.config, contains an AuthenticationTypes property that determines whether the report server supports Kerberos. You can find this file in the Program FilesMicrosoft SQL ServerMSRSxx. folder, where xx is 11 for SSRS 2012, 10_5 for SSRS 2008 R2, or 10 for SSRS 2008.

Open the file in a text or XML editor and locate the AuthenticationTypes element. As Figure 6 shows, you must include RSWindowsNegotiate when client connections use Microsoft Internet Explorer (IE) to connect to the report server. You can add RSWindowsKerberos as another element if other browsers are in use.

Figure 6: Updating the Report Server Configuration File to Support Kerberos 

Step 6: Configure the Local Security Policy Settings

Your next step is to configure the report server's local security policy settings to allow the Reporting Services service account to perform delegation. To do this, open Local Security Policy in the Administrative Tools program group. Expand Local Policies, and click User Rights Assignment. Add the service account to the following policies:

Step 7: Test the Delegation

You can't test Kerberos from the report server because the NTLM protocol is used for local connections. Instead, you must open a browser on another computer on the network and open Report Manager. Locate a report that uses Windows integrated security for a data source located on a separate server from the report server and try to open it. If you succeed, you've successfully configured Kerberos delegation. Otherwise, download and use the " Troubleshooting Kerberos Delegation" white paper to diagnose the problem.

Kerberos Conquered

With a basic understanding of how the Kerberos pieces fit together and the step-by-step implementation guide presented here, you should be able to easily and correctly configure Kerberos for your SSRS environment. Once it's set up, you can enjoy all the benefits that Kerberos has to offer.

Table 1: Sample Information for the Implementation Steps

Learning Path

MICROSOFT RESOURCES

For additional information about configuring Kerberos:

"Kerberos for the Busy Admin"

"Register a Service Principal Name for Kerberos Connections"

"Register a Service Principal Name (SPN) for a Report Server"

"Troubleshooting Kerberos Delegation"

"An Update for Setspn.exe in Windows Server 2003 Is Available"