Double Hop Issue - .NET to IIS to SQL Server Authentication

Recently, after going through an analysis of our SQL Server (2005) security setup, we decided to switch over to Windows Authentication for our .NET applications. Our reasons were enhanced security and implementing best practices. Oddly, in order to achieve some of the ‘best practice’ recommendations, the change over to Windows Authentication does go against some prefered security settings on IIS.

Use at your own discretion and weigh the options for your scenario before implementing.

What I Learned
First – While it may seem a no-brainer to add an AD group to your SQL server, assign it permissions to the required database(s) and configure your .NET app to use Integrated Security, there are additional hoops to navigate before it will actually work.

Second – The Double Hop Issue. In the simplest terms, when the .NET application makes a call to the IIS server and then the IIS server makes calls to the SQL server, the original Authentication used to call the IIS server is not passed along to the SQL server. Time for Kerberos!

Third – There are some wonderful resources on the web for explaining this scenario and ways to fix it. Here are some of the links I used, which I will summarzie below:

http://mkdot.net/blogs/dejan/archive/2008/03/07/kerberos-solution-for-double-hop-authentication.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

I found the first link to be the most helpful, however both provide great insight.

What I Accomplished

1. Server Principle Names for the service account. My SQL server and IIS server were in the same domain. If your SQL Server has TCP/IP enabled and runs under a domain admin user account, you should be all set to go here. If these three things are not true, go back to the article and follow their instructions.
2. AD account. You need an Active Directory service account which allows delegation to the MSSQLService. Each server you will be connecting to must be listed on the delegation tab. If you have multiple instances of SQL Server on any box, you also must include the port number for each instance.
3. IIS Application Pool Properties. In the Application Pool, the Identity property needs to be set to “Configurable”. The Active Directory account used must be set Delegated as “Trust this user/computer for delegation to any service (Kerberos)” in AD.
   

A few words about the AD account
This account is for authentication between IIS and SQL Server. The .NET application uses the appropriate user account and the credentials for the user are passed through the application to IIS and on to SQL Server via the AD service account set up in (2) above.

I may have some updates to post in the next few days, as I have to wait for 'corporate' to implement some of the settings before we can get an accurate test through.

I would like to throw out props to @SQLSocialite (Twitter) for sharing information to be included in an upcoming publication. @SQLChicken and @kbriankelly were also very helpful in pointing me in the right direction. Thanks to the community!

Further, thank you also to @SQLDBA for these posts on Delegation:
http://is.gd/PdTJ
http://is.gd/PdVg