Web app, IIS,Windows Intergrated Auth, Connect String ?

Question

Thanks in advance. I need some help deteming the correct Connect String and proper IIS setup to allow Windows Authenicated access to Sql Server 2005 for a Intranet Web app (all on same domain).
Environment:
Web Server - W2003, IIS 6, network name =MyDomain\WebBox1, web site=MyWebApp
Sql Server - W2003, Sql Server 2005, MyDomain\SqlBox1, database=MyDatabase

Error output:
 Login failed for user 'MYDOMAIN\WEBBOX1$'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'MYDOMAIN\WEBBOX1$'.

Source Error:
Line 23:     {
Line 24:         SqlConnection oConnection = new SqlConnection(msConnectionString);
Line 25:         oConnection.Open();
Line 26:         return oConnection;
Line 27:     }
 

My current connect string in web.config looks like this,
<add name="CA2"
     providerName="System.Data.SqlClient"
     connectionString="Driver={SQL Server};server=SqlBox1;database=MyDatabase;Integrated Security=SSPI" />

In a section of C# code where I use the connection string, the app  seems to pass "MyDomain\WebBox1$" as the user id to Sql Server for Authenication.  Is this the Network Service or ASP.NET account?  I was hoping for the logged in user on pc where the browser was launched, "MyDomain\JoeUser", to passed to Sql Server.  I have added a Sql login  for MyDomain\JoeUser to MyDatabase so he can he get access.  In looking at ConnectStrings there does not appear to be a big variation once you choose Provider and Authenication.  If that is true then is the problem my IIS setup or how I am using connction string or ....?

Thanks,
Brian-GA

---

B

Answer 1

In this scenario, you'll need Windows Authentication and ASP.NET Impersonation.

1. set IIS to enable Windows Authentication only, so that the ASP.Net application can get the user instead of IUSR.
2. set impersonate = "true" in web.config. More information on this please see http://msdn.microsoft.com/en-us/library/aa292118(VS.71).aspx

You'll get what you need.

Thanks,
Hooray