Implement Kerberos Delegation With Ssrs

September 21, 2012 09:46 AM
Implement Kerberos Delegation with SSRS
How to easily and correctly configure Kerberos

Stacia Misner
SQL Server Pro
InstantDoc ID #143744
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 Iencounter 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 foryour 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 UseKerberos?
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 theclient 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 toany 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-hopscenario. You can work around this NTLM limitation by setting up stored credentials and using theUser!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 atthe 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 theconnection 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...