setting up sql 2000 and iis on diff servers

[henslecd]

Here is the environment.

IIS 5.0 on one server. The web site uses a security certificate. Windows Integrate Auth is checked. Anonymous is unchecked. We must use windows auth and not anonymous because I need to collect server variables. Basic also cannot be used.

SQL 2000 on another server. Windows Authentication is the authentication mode.

I use ASP.

I am currently getting the following error.

Microsoft OLE DB Provider for SQL Server error '80004005'

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

I believe the problem lies in the fact that sql is on one server and iis is on the other. However, after reading the many MS articles and other articles about this I have not found the solution.

Thanks for your time.

 

[mrdenny]

henslecd, I think the problem is that you are using Integrated Windows authentication. Switch to Basic authentication. (You can ignore the message about the "password is sent in clear text" as you are using a certificate.

That should allow you to have the users log into the SQL Server with there own accounts. Currently your web server should be trying to log into the SQL Server with the account that the web site is running under, which is probally a local account. You can test this by creating a domain account and setting the web server to run under that account. If you can then log into the SQL Server that will show that.

We use basic authentication on our internal sites with domain usernames and passwords, and I have several web based apps that pass that info accross to the SQL Server and allow access based on the domain account of the user.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[henslecd]

Thanks Denny.

I had already tried running basic (because i knew the certificate was there) and the connections worked fine. However, when another page ran the code...

logonuser = request.servervariables("LOGON_USER")

...logonsuer is blank. Therefore we have to use windows authentication. The request.servervariables("LOGON_USER") is extremely important because that is how we log who modifies the database.

Any ideas?

 

[mrdenny]

Try request.servervariables("REMOTE_USER") or request.servervariables("AUTH_USER").

Or you could use a trigger to update the table that says who updated the database. Since you are having them log into the SQL Server with there own accounts, the SQL Server will have that info.

If you are using a stored procedure to do the updating, it's even easier as you don't have to use a trigger, you can just make the change in the procedure so insert the name they logged into the database as.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[henslecd]

The other problem with Basic is that my entire division will have to login using their username and password into the web page. That isn't going to fly. Thanks for the suggestions but we have to use windows authentication. If we can't figure this out then we will have to go to mixed auth on the db.

Let me know if you have something else you can think of. I really appreciate it.

 

[henslecd]

We have put a band aid on the problem by switching to sql mixed authentication mode and setting up a user for the conn string. Everything works now but we want to figure out how to just use windows authentication.

Thanks for all the help.