Getting IIS to impersonate the windows user to SQL server in an intranet environment

oceanexplorer picture oceanexplorer · Feb 19, 2014 · Viewed 25.2k times · Source

I am developing an intranet site using C# and ASP.NET MVC. I have SQL Server on one machine and IIS running on a separate machine. I would like a user to visit the intranet site and without prompting the user internet explorer sends the users windows credentials to IIS and these are then passed to sql server meaning sql server can see the user accessing the database.

I am aware of the Kerberos double hop issue and it is this I am trying to get around. At present I can get IE to pass the windows credentials to IIS and authenticate fine. I just cannot get IIS to pass on those credentials to SQL Server and instead the request currently runs under the app pool identity which is set to a domain service account "htu\srv-htu-iis".

My setup is as follows:

Web.Config

<system.web>
    <authentication mode="Windows" />
    <authorization>
        <deny users="?" />
    </authorization>
    <identity impersonate="true" />
</system.web>
<system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
</system.webServer>

Connection String

connection string=&quot;data source=hturesbsqlp01;initial catalog=R2_Dev;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"

IIS Authentication Settings

Anonymous Authentication = Disabled
ASP.NET Impersonation = Enabled
Forms Authentication = Disabled
Windows Authentication = Enabled

IIS App Pool Settings

Managed Pipeline = Integrated
Identity = htu\srv-htu-iis (domain service account)

Active Directory Settings

The domain service account htu\srv-htu-iis has had a service principal name set which associates our site with the account.

Active directory has

Allow Delagation to any service

SQL Server is running under its own SQL Domain Service account.

Tests

I ran the following code tests:

System.Web.HttpContext.Current.User.Identity.Name

this correctly return the windows credentials of the user accessing the site

System.Security.Principal.WindowsIdentity.GetCurrent().Name

return the domain service account "htu\srv-htu-iis" which is what the app pool identity is running under.

Can anyone provide direction as to where I might be going wrong?

Answer

oceanexplorer picture oceanexplorer · Feb 20, 2014

Well to anyone visiting this question in the future. I resolved this by restarting the IIS Service...doh! Seems my settings were fine just need a full restart of the service!