SQL Server and windows authentication in IIS7

Question

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!

Answer 1

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:

• How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0
• Configure ASP.NET Impersonation Authentication (IIS 7)
• Configuring Servers for Delegation

Answer 2

By default, the IIS server is not allowed to impersonate you towards the SQL Server. There's an MSDN article on how to configure it. The configuration process is complex and error prone.

If your production ISS and SQL Server run on different servers, you'll need a domain admin to configure impersonation trust between the two servers. This is typically a no-go in a big organization.

In addition to not being deployed by normal admins, impersonation also prevents users from sharing their connections in the SQL connection pool. This results in a very noticeable performance penalty for even small (5+ users) websites.