SQL Server Integrated Authentication Mode

Question

I was wondering when you use Windows authentication mode in the connection string from a web application. The application itself uses Windows authentication for authorization. Which account will be used to log in to SQL Server.

Is a web application pool account? User account logged in to web application using windows auth? Any other account?

The application runs under Win Ser 2008 64 bit and IIS 7. The application pool account is Network Service.

Answer 1

It depends on how you configure it. From http://msdn.microsoft.com/en-us/library/ms998292.aspx and http://msdn.microsoft.com/en-us/library/bsz5788z.aspx ...

ASP.NET applications do not impersonate by default. As a result, when they use Windows authentication to connect to SQL Server, they use the web application process ID. With this approach, your external web application authenticates and authorizes its users and then uses a trusted identifier to access the database. The database trusts the application identifier and trusts the application to authenticate and allow callers correctly. This approach is called the trusted subsystem model.

An alternative model, called the impersonation / delegation model, uses the original identifier of the calling Windows user to access the database. This approach requires your ASP.NET application to be configured to use impersonation. See the "Impersonation / Delegation vs. Trusted Subsystem" section in this document.

So, depending on how you configured it, it can use either the application pool account (and not when not to use impersonation), or the user account logged in that uses the web application (when using impersonation).

See http://msdn.microsoft.com/en-us/library/134ec8tc.aspx for impersonation information.

Answer 2

This is the application pool user who connects to the database if you specified Integrated Security in your connection string.

Answer 3

The problem I ran into was that my application pool account in SQL Server had to be installed in the db_owner role before it worked. I tried to figure this out for a long time.

I used Windows Authentication, Windows 7 home premium and IIS on the same computer. I post this if someone encounters a similar problem. The book I used did not say to use db_owner, but the reader and author accounts.