Using Kerberos and Windows Authentication with SQL Server

  • Article

In my opinion SQL or Windows Authentication isn’t a choice. You should always use Windows authentication unless technically impossible. The most common scenario is when the user account is not in the same domain as the SQL Instance and there is no trust relationship. Or when the server is based on an architecture that does not support Windows Authentication like Unix.

In most other scenarios you can use Windows Authentication and you should. In this post I will walk you through a scenario where we change a web application from SQL Authentication to Windows authentication. Eventually we will also use Kerberos and constrained delegation to get the users credentials all the way to SQL Server.

To get things running we use two identical web applications listed below. The only thing the web application does is get information about the connection from SQL Server. To be able to see this information the accounts are granted view server state permissions.

 <%@ Page Language=”C#” %> 
 <!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN” 
 “https://www.w3.org/TR/xhml11/DTD/xhtml11.dtd”> 
 <script runat=”server”> 
 </script> 
 <html xmlns=”https://www.w3.org/1999/xhtml” > 
 <body> 
 <form id=”form1” runat=”sever”> 
 <div> 
 <asp:SqlDataSource ID=”sqlDataSourceMaster” runat=”server” SelectCommand=”select s.session_id, net_transport, auth_scheme, s.host_name, s.login_name, s.program_name from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on c.session_id = s.session_id where s.session_id = @@SPID;” 
 ConnectionString=”<%$ ConnectionStrings:connectionStringMaster %>” 
 DataSourceMode=”DataReader”> 
 </asp:SqlDataSource> 
 <asp:GridView ID=”gridViewMaster” runat=”server” 
 DataSourceID=”sqlDataSourceMaster” 
 DataKeyNames=”session_id” AutoGenerateColumns=”true”> 
 <asp:GridView> 
 </div> 
 </form> 
 </body> 
 </html>

 <configuration> 
 <connectionStrings> 
 <add name=”connectionStringMaster” providerName=”System.Data.SqlClient” 
 connectionString=”server=sql-05;database=master;uid=appusr1;pwd-p@ssw0rd;app=site1” /> 
 </connectionStrings> 
 </configuration>

The HTML page includes some C# script to retrieve the current session information from SQL Server. Notice in the connection string the connection is made using SQL Authentication. Also I’m using the app directive to distinguish the connection.

image

This is nothing different than the ordinary web application using SQL Server. First step is to change from SQL Authentication to Windows Authentication. For this we change the connection string to: server=sql-05;database=master;integrated security=sspi;app=site1.

After a refresh of the browser the application fails. Why? The event log on the webserver and the SQL log provide the information; Login failed for user ‘CONTOSO\IIS-01$’ . The dollar signs tells us we are dealing with a computer or managed service account. In this case the computer account. We changed from SQL to Windows Authentication and now instead of authenticating with the SQL login ADO.NET is authenticating with the credentials of the process running the web application. The reason the computer account is used is because the application pool in which the web application is running is running with either the NETWORK SERVICE account or the ApplicationPoolIdentity. The ApplicationPoolIdentity is new for IIS7 and is used to isolate the processes on the machine itself, but when connecting to other machines it will connect in the same way as the NETWORK SERVICE account, using the computer account. To get the application running again we can add the computer account as a login on the SQL box and grant the VIEW SERVER STATE securable.

image

As you may notice we are connecting with NTLM. NTLM is an older authentication protocol where username and password are exchanged. It’s more secure to use Kerberos where there is only a username password exchange between a Kerberos server, most of the times a domain controller, and the client. The generated ticket will then be used for further authentication. Getting our application to use Kerberos instead of NTLM is a breeze, all you need is a domain admin or delegated permissions and set the proper SPNs for SQL Server using SETSPN.

SETSPN –A MSSQLSvc/sql-05.contoso.com:1433 CONTOSO\sqlsvc

And that’s it. Refreshing the websites gives me Kerberos authentication.

image

Now we are going to take things one step further. If you have successfully got Kerberos working you can get constrained delegation to work. In this scenario we get the user account requesting the webpage. A few more steps have to be taken to achieve that.

  1. The website has to be set to Windows Authentication using the IIS manager.
  2. In the web.config we add this section
    <system.web> <identity impersonate=”true” /> </system.web> I got an 500.24 error. Apparently because .NET 2.0 needs classic mode for an IIS7 application pool. This error went away after reconfiguring the application pool. More info: https://learn.iis.net/page.aspx/381/aspnet-20-breaking-changes-on-iis-70/
  3. Set the computer account in active directory as Trusted for Delegation.

And that’s it.

image

As you can see in the screenshot above both sites use Kerberos authentication and in both cases the user credentials are send to SQL Server.

This is a quick introduction to show you how easy you can setup Windows authentication in favor of SQL Authentication and additionally use Kerberos as an authentication mechanism. Ultimately you can leverage Kerberos to use constrained delegation and get the user credentials all the way to the SQL Server, often referred to as ‘Double Hop’. As this is a quick way to introduce this it’s by far not the complete list of possibilities. To further investigate the techniques involved I suggest you take a look at the sites listed below:

https://technet.microsoft.com/en-us/library/cc995228.aspx
https://msdn.microsoft.com/en-us/library/ff649317.aspx
https://msdn.microsoft.com/en-us/library/ee191523.aspx