SQL Server – Windows Authentication in IIS7

asp.netiis-7sql-serverwindows-authentication

I'm trying to get an ASP.NET website running on Vista (IIS7), using SQL Server and Windows Authentication. No matter what I do, when I connect to the database, I get the exception:

    SqlException was unhandled
    Login failed for user 'MyDomain\MachineName$'.

It doesn't seem to matter what settings I apply, I cannot get IIS7 to pass through my Windows login credentials.

Extra details:

Both the SQL Server and my local machine are on ActiveDirectory
Vista Enterprise, IIS7
SQL Server 2005
Anonymous Authentication disabled, Windows Authentication enabled
Impersonation on/off makes no difference
All Identities (NetworkService, LocalSystem, etc) give the same result
Classic and integrated pipelines give the same result

Help!

Best Answer

Impersonation on/off makes all the difference, when properly configured. What you want is caled 'constrained delegation' and you need to configure IIS and ASP for it:

Related Solutions

ASP.NET – Windows Authentication to SQL Server

This is key = The IIS server and SQL server are on different machines. You're facing the classic double hop issue. Think of it in this way:

You access the web application under your credentials
IIS in this case has to present who you say you are to the SQL Server
IIS says, "Why hello SQL Server, I'm passing on Tony's kerberos creds, they are legit"
SQL Server says, "Hmmm, IIS I'm not sure I trust you, I need proof that you are trusted to present these credentials to me".

Thus once you enable delegation as mentioned by Remus, your SQL Server will trust the credentials your IIS server is presenting on your behalf.

In terms of security, under delegation, it would be wise to choose:

Trust this computer for delegation for specified services only | Use Kerberos only | and then underneath the "Services to which this account can present delegated credentials" make sure you explicitly set only the server/port you need.

C# Windows Authentication – Using Windows Authentication in ASP.NET with C#

You are confusing SQL authentication with Windows authentication.

In order for this web page to work based on Windows authentication, your web.config needs

<authentication mode="Windows">

When you deploy your page to a web server, you need to disable anonymous authentication to restrict external users. Below is a snippet from an IIS7+ web server's authentication section:

enter image description here

If you need to program against logged in user or its group, you need to use the WindowsIdentity Class.

Best Answer

Related Solutions

ASP.NET – Windows Authentication to SQL Server

C# Windows Authentication – Using Windows Authentication in ASP.NET with C#

Related Question