Database connection

[Filip]

Hi,

I have a website runnning on Windows 2003 Web Server edition that needs to
connect to an MS SQL2000 database.
The web server is NOT part of the domain, but can talk to my database via
IP and retrieve data when using SQL server login.
This however means there is a User ID /Password in clear text.

I would like to use SSPI, so I did the following:

1. created local account on my Web server with known password
2. using aspnet_setreg I encrypted and inserted the User ID/Password into
registry
3. ACL set on the registry key to Read
4. In Web config I set
<identity impersonate="true"
userName="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,userName"
password="registry:HKLM\Software\TestApp\Identity\ASPNET_SETREG,password"
/>
User is being correctly impersonated
5. I gave permissions to my new user to have access to files/folders required
by ASP.NET
6. Created "mirrored" local account on my database server.

However, when I run a page that contains database connection/data retrieval
I get the following error:

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."

obviously my User ID / Password are not being passed through.

Can anybody suggest, what I need to do, obviously I don't want to have the
User ID and Password in clear text.
Please keep in mind Web server and Database server are NOT in tha same domain
(can't use domain logins!)

Thanks in advance,
Fili

 

[Paul Glavich [MVP ASP.NET]]

Are you sure the impersonation is working correctly? How did you verify
this? This would be the first thing I would check.

 

[Dominick Baier [DevelopMentor]]

Hello Paul Glavich [MVP ASP.NET],

try inspecting WindowsIdentity.GetCurrent() after impersonating - this little
tools may also help:

http://www.develop.com/technology/resourcedetail.aspx?id=00e7be6c-69a1-44a5-8790-7969ea94df4d

 

[Dominick Baier [DevelopMentor]]

Hello Filip,

another approach would be (and IMO a much better)

1. configure your worker process identity to a custom account (via the AppPool
feature in IIS6)
2. create a "mirrored" account for the app pool account on the sql box
3. give SQL access to this account
4. don't impersonate
5. connect to SQL

 

[Filip]

Hello Dominick,

Thank you very much for the info and the aspx page.
Looks like the issue was elsewhere, but I will give the "AppPool" a go.

Filip