connection string accessing wrong server

[tburrows]

I have created a web form and have a database on a sql server. That server is seperate from the web server. The connection string is:

Conn="Provider=sqloledb;" & _
     "Data Source=<database server name>;" & _
     "Initial Catalog=<database>;" & _
     "UserID=IUSR_PUBLIC;" & _
     "Password=;"

However when I try to run the form I get an error message that the says that the IUSR_<web server name> doesn't exist. Somehow the IUSR_web server name seems to be getting substituted for IUSR_PUBLIC.

 

[vongrunt]

Huh?

This conn string attempts SQL Server authentication. Provided userID/password must exist in server.

IUSR_* is usualy built-in OS account used by IIS web server... so I'm slightly confused.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

Have you created the account iusr_public with the SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)

 

[tburrows]

Right, iuser_public is the guest account on the sql server and I am getting the error message that Login failed for the production web server. There are other forms on the website that use the same connection string as I am and are not having a problem.

 

[mrdenny]

You could setup a trace to show all login attempts and see what's going on that way.

Are you sure that it's actually using the correct connection string?

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)

 

[tburrows]

Here is my connection:
If Request.ServerVariables("REQUEST_METHOD")="POST" Then
Connect="Provider=SQLOLEDB;Data Source=KCISQL;Network Library=DBMSSOCN;Initial Catalog=complaintSQL;UserID=IUSR_PUBLIC;Password=;"
'set connection string
Set complaint=Server.CreateObject("ADODB.Recordset")
compSQL="SELECT * FROM complaints"
complaint.Open compSQL,Connect,3,2,1

It stops when it gets to the open statement and says:
Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user 'IUSR_KCWPPUB1'.

This is the web server, where my connection is to the sql server.

Thanks Tom

 

[mrdenny]

ok, so yeah it's definettly using the correct string. I can't imagine why it's trying to login with the wrong account.

Normally I would have you check the authentication settings on the SQL Server to make sure that Windows only isn't selected, but we know that it's not because other pages are working correctly.

I've got no idea if any of this will work, but I've got no better ideas at the moment.

Try removing the network library from the connection string. It's technically not needed since it will get that from the Provider. (See

http://www.sqlstrings.com/SQL-Server-connection-strings.htm

for all the syntaxes.)
Try making the connection string bunk (make the UserID or password invalid, use an invalid provider, etc) and see what happens.
Check global.asa and make sure there isn't anything stupid in there.
Run the trace with profiller that I mentioned earlier and see if you can see the connection comming in. Mabey you are picking up some SQL pooling that's using NT Accounts to authenticate.

I'm totally running out of ideas here. Let me know.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(My very old site)