IIS 7 not connecting to Sql2008

I've a really strange problem getting IIS7 to connect to Sql2008.

First, using the default web server in VS2010, in a HTTPModule I load a connection string from the web.config, and access the database. Works good. (Connection string uses Integrated Security = SSPI)

But because the website has some #includes, I need to debug it in IIS, so I switched from the default web server to IIS. Everything else is the same. However, when I try to open the same SqlConnection in the HTTPModule, I find that the credentials supplied are not the users, but rather the machine name.

Environment is Virtul Machine; Windo开发者_StackOverflow社区ws 7 64 bit; IIS7; VS2010; SQL2008

Can anyone give me any idea where or how this is being set? Thx

When you run your application in Visual Studio, the application runs as "you". If you're credentials are sufficient to connect to the database, then using Integrated Security you'll be able to log in.

When you move over to IIS, IIS runs the process under a different identity depending on your configuration (but most likely not "you"). You have several options:

1) Give the default user IIS is running under permission to access the database (not recommended)

2) Configure IIS to run your application as "you", or as a dedicated user configured specifically to run your process.

http://technet.microsoft.com/en-us/library/cc771170(WS.10).aspx

3) Don't use integrated security, use SQL security.

Here is an article that describes how you can figure out what user IIS is running under.

http://blogs.iis.net/davcox/archive/2009/08/12/what-is-my-iis-code-running-as.aspx

For anyone that runs into the same problem I did.

First, the place where I was trying to run a stored procedure was in a HTTPModule. Also I was using my local copy of IIS to debug. Note that in the default webserver that comes with VS2010, it ran fine, but in IIS, it broke.

You apparently cannot access a remote Sql2008 in the BeginRequest method of a HTTPModule. It must be in the PostRequestHandlerExecute method.

Second, I set the IIS authentication to include AnonymousAuthentication with the user IUSR.
Plus I added for the Website only, ASP.NET Impersonation and that user had permission on the database