4

I have several servers (all on the same DC) within an Intranet environment at my company. For simplicity, I'll focus my question on the IIS and SQL Servers. I have an IIS 7.5 web server and a remote SQL Server 2005 on another machine on the domain.

Here are my requirements for what I hope to accomplish:

  • Web applications on the IIS server should use some form of authentication (presumably Windows Authentication) such that the user does not need to authenticate as it will use their network user credentials as they are logged into their Windows machines.
  • The authentication method should ideally be supported over a VPN connection into the network as well.
  • The user identity (Principal) should be passed onto the remote SQL Server such that a query on that server would be able to identify the user in order to support authorizations based on their username. e.g. the query select suser_sname() should return the actual username of the person performing actions to the DB via the IIS web application.
  • The solution should work at least with SQL Server and UNC paths on the network.
  • Highly reliable and future proof (i.e. best practice).

I have spent at least a couple weeks (cumulatively) researching this off and on over the past few years as I have had time, yet to no avail. Please help me to understand first, if it possible to meet these requirements (as I am reasonably certain that it is), point me in the right direction as to "HOW" to accomplish this, and what limitations I should consider with said solution.

What I have done so far:

  • I have a test app which is reporting Kerberos as the AuthenticationType using Windows Auth enabled exclusively.
  • The Windows Auth is configured to use "Negotiate" as the provider and also Requires Extended Protection with Kernel Mode enabled.
  • The App Pool uses .NET 4.0 in Integrated mode.
  • The App Pool Identity is a custom domain account which we registered as the SPN.
  • The IIS server has Delegation enabled in AD, but the SQL Server does not.

The best I have been able to accomplish is to confirm the presence of the Kerberos http header, but my test app reveals that the App Pool's custom user (the SPN account) is getting passed instead of my own. Here are the results of my test app too if you like:

Result             Field Description              Test App Code
------             -----------------              -------------
Negotiate          Page Authentication Type       Page.User.Identity.AuthenticationType
<dc>\<username>    Page Identity                  Page.User.Identity.Name
Kerberos           Principal Windows Auth Type    WindowsIdentity.GetCurrent().AuthenticationType
<dc>\<spnUser>     Principal Windows Identity     WindowsIdentity.GetCurrent().Name
<dc>\<username>    HttpContext User               HttpContext.Current.User.Identity.Name
<dc>\<username>    Server Var "LOGON_USER"        Request.ServerVariables("LOGON_USER")
<dc>\<username>    Thread Current Principal       Threading.Thread.CurrentPrincipal.Identity.Name
7
  • In short, you want a user to visit an intranet website, be logged in automatically, and have the website impersonate the user when talking to SQL Server and UNC shares? The database access is possible (I haven't tried UNC), with a few delegation settings and SPNs - e.g. blogs.technet.com/b/askds/archive/2008/06/13/… - have you got SPNs on the SQL service account user? And the IIS service account? And made sure those SPNs are not duplicated anywhere else with setspn -X ? Jun 6 '15 at 2:37
  • @TessellatingHeckler Yes, that is precisely what I am aiming for. :) AFAIK we have a new SPN on the IIS which is what the AppPool Identity is set to, but the SQL Server is running under a different Identity and I do not believe it is an SPN. FYI, we have made successful Kerberos calls with the new SPN we created, but the SQL Server is seeing the SPN rather than the user.
    – Chiramisu
    Jun 6 '15 at 2:53
  • Try searching for impersonation or Kerberos double hop. This article might get you started blogs.msdn.com/b/chiranth/archive/2014/04/17/…
    – Jim B
    Jun 6 '15 at 3:16
  • To say you have "an SPN on the IIS which the AppPool Identity is set to" is not very clear - the identity isn't set to an SPN, it's set to a user account, and the user account has an SPN set on it. And the SQL server service is running as a user account, and the SPN "MSSQLSVC/sqlserver.example.com" needs to be on that account. Getting the two right SPNs on the two right accounts (and no duplicates anywhere else) and then accessing everything by the fully qualified name in the SPN matters enormously for Kerberos to work. Jun 6 '15 at 3:32
  • @JimB The problem is, you don't know what you don't know, so recognizing the solution so far has not worked out for me after MUCH searching as I mentioned. I'll prioritize your link over my other search results. Thanks.
    – Chiramisu
    Jun 6 '15 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.