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:
I found the first link to be the most helpful, however both provide great insight.
What I Accomplished
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!