Here is what we have going on...
we have 2 servers, the web server (A), and a SQL cluster (B). When a user visits a website on A there is some stored procedures that get called on B. We would like those stored procedures to be executed using the "network users" login. We try to implement this by doing the following:
the connection string looks like this
<add key="ConnectionString" value="Workstation ID=UTMPLMDBT;Packet Size=4096;Integrated Security=SSPI;Data Source=XXX.XXX.XXX.XXX,xxxx;Persist Security Info=False;Initial Catalog=XXXX" /
we added this in the web.config
<authentication mode="Windows" />
<identity impersonate="true"/>
-The web server is running IIS 6 on windows 2003 server
-We currently do not used Active Directory
-SQL cluster is running SQL 2000 on windows 2003 server
The error we are currently getting is:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
This error occurs when we try sqlConn.Open()
Any help would be greatly appreciated,
TreyHere is some other information...I wrote the following program...
Dim authUserName As String
Dim aspUserName As String
authUserName = User.Identity.Name
aspUserName = Principal.WindowsIdentity.GetCurrent.Name
authUserPrincipalLabel.Text = "You are: " & authUserName
aspPrincipalLabel.Text = "This page runs as: " & aspUserName
it displays:
You are:MYDomain\144205
This page runs as: MYDomain\144205
So I'm not sure what is going wrong|||This:
::We would like those stored procedures to be executed using the "network users" login
and this:
::<authentication mode="Windows" />
::<identity impersonate="true"/
contradicts. The statement will get executed using the identity of the user making the call, IF the page is protected, null (imho - am I correct here?) if anonymous access is allowed (or: the asp.net worker process identity, which is NOT network_users and a local account, unless reconfigured, which you did not say you did).|||When I said,
::We would like those stored procedures to be executed using the "network users" login
I mean I want to log into SQL using the users network username (ex) Mydomain\Trey
and password and execute the the procedure.
anonymous access is not allowed (not checked) in or IIS server.
I'm gettng kinda lost...how do I do this?
Thanks,
Trey|||OK after much reading...I was wondering if I can try the following
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod15.asp
without using active directory?
Thanks,
Trey|||::OK after much reading...
NOW you deserve a good answer :-) And this is not meant sarcastic. You proapbly learned a lot about how the WIndows Security System works, and this is sort of exactly what you need.
Could you describe the server side network setup?
Basically, what you can do is:
* One Server: create a custom asp.net account, give it rights in the database, do NOT use impersonation. Voila.
* Multiple Servers: no impersonation, no trusted connection, turn the server to "mixed mode", use username and password.
Unter 99% of the cases the real user should NOT map through to the database.|||Ok...what we did is created a minimum privileged domain account, "Me"
Added this to the web.config
<identity impersonate="true" userName="MyDomain\Me"password="Something" />
Just a note I am not going to leave the Username and Password in clear text.
And we connected successfully. Whoop!
So I tried something else... I changed the web.config file back to:
<identity impersonate="true" />
and logged in as the new "Me" account
I got the account null error again. I guess I'm getting lost on what is going on in the back end.
The reason I would like to use the real user is for auditing purposes. So we know who is doing every select, insert, ect. Would it be better just to pass the users info as a value in the stored procedure?
Thanks,
Trey
Roberot:
