Twitter Psychology

As a computer geek (well, SQL Server anyway) with a degree in Psychology, I found this site to be rather interesting. Thanks @pnt for tweeting about this site.

Here is my twitter personality profile according to TweetPsych. http://tweetpsych.com

Cognitive Content

Present tense
Self reference
Tentative
Time
Occupation & work
Past tense
Upward motion
Senses
Certainty
Future tense
Insight
Positive Feelings
Space
Positive emotions
Agreement

Primordial, Conceptual and Emotional Content
Social behavior
Constructive behaviors
Abstract thought
Temporal References
Concreteness
Positive affect
Restraint
Visual sensations
Order

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