Its a frequent situation where you want your ASP.NET application to connect to the SQL Server using the integrated security account. For example, you have all of your roles setup on the SQL Server and want your web application (secured via IIS integrated authentication) to get data specific to your role on the server.
In a windows application this is easily achieved by just making your SQL connection string use the integrated account (whichever windows account is running the app). But in a web application, the account that your SQL Server will open the connection under is the \ASPNET [or \Network Service in IIS6] not the account that is used for IIS authentication. To make it use the authenticating account you could turn on impersonation within your web.config file with the following:
<authentication mode="Windows" /> <identity impersonate="true" />
When I ran it with impersonation = true it just seemed to work perfectly (you can run a SQL trace to spot who the connection is coming in as). But be warned it doesn’t work for all server configurations apparently. I’ve just formatted and have a fresh install of SQL (running under the local system account) so I would think that I’ve got a “default” install…
An MSDN article sheds some light on why it won’t always work:
Why can’t I enable impersonation for the Web application, so that I can secure the resources accessed by my Web application using ACLs configured against the original caller?
If you enable impersonation, the impersonated security context will not have network credentials (assuming delegation is not enabled and you are using Integrated Windows authentication). Therefore, the remote call to SQL Server will use a NULL session, which will result in a failed call. With impersonation disabled, the remote request will use the ASP.NET process identity.
You will notice that it says that “assuming delegation is not enabled” (which it isn’t by default) these problems will occur. So obviously we might want to work out how to get this enabled… Keith Brown wrote an article detailing what you need to do to get this enabled – which should in theory get things working seamlessly.
A key problem with using impersonation is that you might now have security considerations that you weren’t thinking about, e.g. access to file resources, etc… Remember that all of your code is now executing as that user – not the ASPNET account…