ASP.Net, IIS and SQL Server – Integrated Security, Authentication and Impersonation

In one of my last projects, I was responsible for designing an enterprise wide ASP.Net application that would run on IIS and use integration windows authentication and connect to backend SQL Server database using integrated security i.e. SSPI. As you see, as per the requirement, everything (all network resource access) had to use integrated windows security mechanism i.e. “trusted connection”.

This is indeed a very common development scenario that we face on a regular basis when working with ASP.Net applications.  In order to use Integrated Security, we simply check the ‘Integrated Windows NT Authentication’ (challenge/response) option in IIS, set ‘impersonate=true’ in our web.config file and we are ready to go.

Or are we?

A very common stumbling block that developers face with the above setup is when trying to connect to SQL Server, you end up getting one of the two following error message:

• Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
• Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
  [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '\'.

 What’s going on? The reason for the above error is due to the ‘anonymous access’ option also being turned on (checked) in IIS. When this is the case, anonymous access takes precedence over Windows NT Authentication access and user’s credentials are not passed. Ok so what can we do? Let’s turn off ‘anonymous access’ option in IIS and try again to connect to SQL Server. Now you will likely end up getting the following error messages:

• Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
  [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

 Huh?? The reason is because of a 'double hop' that authentication mechanism undertakes. When the client authenticates with IIS, it passes the logged in user’s (in the domain) NTLM credentials (username/password). This is the ‘first hop’. After IIS has authenticated the user’s credentials now it is time to access SQL Server (or any other secured network resources). But IIS does not pass the NTLM credentials to the SQL Server machine because that would constitute a "second hop" which is not allowed for security reasons.  Therefore trying to access a SQL Server instance running on another machine other than the web server will result in a logon failure error.
So what is the solution to the problem? Two most commonly used solutions are as follows:

• SQL Server authentication

SQL Server authentication relies on the internal user list maintained by the SQL Server computer. This list does not include Windows NT users, and is specific to the SQL Server computer. You need to provide a specific SQL Server user account’s “username” and “password” in the connection string when connecting to SQL server.

• Windows NT authentication

This is what Microsoft’s Tech Net article has to say - “To configure IIS for Windows NT authentication, you cannot use Windows NT Challenge\Response (NTLM) authentication”. This essentially means do not connect to SQL Server using a trusted connection from ASP.Net application when using Windows NT authentication with IIS. Instead do the following:

• Use ‘Basic Authentication’
• Use anonymous access and follow steps to setup proper authentication (refer the article http://support.microsoft.com/kb/247931/)
• Use Windows NT Authentication and use a specific “generic domain account” (ensure it has been given appropriate access and level of permission to the SQL database). Then use either of the following two ways to provide the generic user credential:
• Specify a username and a password in the connection string of the (this is similar to SQL Server authentication)
• Specify values in the impersonation settings and still use a trusted connection. You can also encrypt the web.config to protect the username/password information

 Now that we have seen the approaches to solve the “double hop” problem next comes the question “Can we impersonate an account at runtime, programmatically?” e.g. say I have the following 2 accounts: (i) DOMAIN\User1 and (ii) DOMAIN\User2 where User1 has “write access” while User2 has ‘full access’ to SQL Server (or any other resource). Once I am done with the network resource I want to continue accessing with the original logged in user’s credentials. In other words, I want to be able to execute certain code under a specific impersonated user credential. Is this possible?

Yes, absolutely. Here is the article on MSDN that explains it all. How To: Use Impersonation and Delegation in ASP.NET 2.0. It explains all the various Impersonation Scenarios namely:

• Impersonating the original caller. You want to access Windows resources that are protected with ACLs configured for your application's domain user accounts.
• Impersonating the original caller programmatically. You want to access resources predominantly by using the application's process identity, but specific methods need to use the original caller's identity.
• Impersonating a specific Windows identity. You need to use a specific identity or several Windows identities to access particular resources.
• Using delegation to access network resources by using an impersonated identity. You need to use an impersonated identity to access remote resources.

That's all folks!