SQL Impesonate Logon Problem
Hi.
I am currently working on an ASP.NET application which has to log on to SQL using a specific windows user account. As far as I am aware the only way to do this is by adding theIntegrated Security=true; property to the connection string. If you specify a user id and password then it will try to log you in as a SQL user. Is this all correct?
To be able to login to the SQL server we explicitly call theLogonUser function from theadvapi32.dllthen open a SQL Connection using theIntegrated Security=trueproperty. When you look at SQL Profiler it seems that there is two logon calls made when the connection is opened. One for the ASP.NET machine account and one for the user that we impersonated using theLogonUser function. Does anyone know why this happens? And how to avoid it?
Any information will be good.
Thanks in advanced
You are correct. If you specify a username/password in the connection string, you are using SQL auth.
I would recommend against using P/Invoke to call LogonUser. If you're using ASP.NET, simply set the <identity impersonate="true"/> in your web.config file. That will impersonate the incoming user for access to all resources: file, database, or otherwise.
If the web application was to use just <identity impersonate="true"/> then there would be 1000's of users requiring a logon to sql server which would cause all sorts of implementation troubles.
We currently have one generic database user.
All application users impseronate this user before opening a connection.
What i would like information on is WHY when this connection is created the asp.net machine account tries loggin into sql server (when it shouldnt) at the same time the impersanting user makes a connection to sql server.
If ASP.NET is impersonating a user why would it send 2 requests for connection to sql server using the asp.net machine account and the impersonating users account.
Do you know anyone/website that could help me.
I'm uncertain why you are seeing both the ASP.NET machine account and impersonated user are logging into the database. I tried it on a simple ASP.NET application and when impersonation was turned on, I only saw the logged in user authenticating against SQL Server, not the ASP.NET machine account. When I turned off impersonation, I saw only the ASP.NET machine account logging on and off. Hopefully someone else has some insight on this matter.
As for impersonating a known user... I agree, you probably don't want to assign 1000+ users access to the database. (You won't scale well for one thing since every user would need their own DB connection. Security on a connection is negotiated when the connection is established. So if your credentials are different, you need a new connection. Database connection pooling will only help for repeated connections by the same user.) So using a common domain account makes sense. Since you have concerns about storing domain user/pwd in the web.config file (and you should have concerns about doing this!), I would recommend storing them encrypted in the registry.
<identity impersonate="true"
userName="registry:HKLM\Software\AspNetIdentity,Name"
password="registry:HKLM\Software\AspNetIdentity,Password" />
You then set the user/pwd in the registry using aspnet_setreg.exe, which can be found here along with complete details on the technique:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329290
I used the web.config to impersonate the incoming user and saw only one login event to SQL Server. I haven't tried Win32 LogonUser. (It might be caused by the type of token - primary or impersonation - which you get back from LogonUser depending on the logonType requested.) Given that you can encrypt the user/pwd in the registry with the web.config, is there any impetus for using LogonUser rather than web.config?
