Login failed for user

[bluecjh]

Thought I had this one solved. (see previous thread of the same name)

My ASP page which makes a connection to SQL SERVER

works fine from the IIS server (remote desktop)
but not from my PC?

I get:

Microsoft OLE DB Provider for SQL Server error '80004005'

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

it's failing on the open connection line :

strReportsUsage = "Provider=SQLOLEDB;Integrated Security=SSPI;Trusted_Connection=yes;Initial Catalog=ReportsUsage;Data Source=HG033"
connHits.open strReportsUsage

why might this be?

thanks

BlueCJH

 

[mrdenny]

Are you the IIS Server and the SQL Server on the same domain? Is the IIS Server and the SQL Server on the same box, or seperate boxes? Are you getting this when connecting, or when running your query? Does your query include a linked server?

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[bluecjh]

IIS Server & SQL Server are on seperate servers. I am
not using a linked server, I get this error when
my asp page attempts to 'open' the connection not at the query stage. What really bothers me is that I can achieve the connection and query from my pc using asp.net so why not with an oledb connection and classic asp?

BlueCJH

 

[bluecjh]

The Servers (IIS & SQL Server) are on the same domain

BlueCJH

 

[mrdenny]

What authentication method do you have setup on the IIS server?

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[bluecjh]

Integrated Windows Authentication

BlueCJH

 

[mrdenny]

That's why you'r getting the null message. Integrated Windows Authentication doesn't work correctly when using Integrated Windows Auth. Change to Clear Text and it will work fine.

I beleive the problem is related to NTLM not being able to pass the authentication token to a third machine. You can test if this actually the problem by logging onto the console of the web server and trying again. If it works, then it's the NTLM issue.

The NTML not being able to pass the authentication token to a third machine is basically this. When using Integrated Windows Auth you are taking the NTLM token from your workstation and passing it to your IIS server. Your IIS server can not pass that NTLM token to the SQL Server because the SQL Server can not trust the token because the IIS Server didn't create it.

When you use clear text, the NTLM token is created by the IIS server so it can pass it to the SQL Server, and the SQL server will accept it, becauase the machine passing it is the machine which created it, so it can be trusted.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[bluecjh]

Denny,
As I undrstand it by 'Clear Text' you mean I have to
set the authentication to non 'Windows authentication'
but this is the critical bit, I want to log which users are accessing the page so i wanted WAuth 'ON'?
chris.

BlueCJH

 

[bluecjh]

Also Denny I can achieve a result with asp.net
so what's with classic asp?

BlueCJH

 

[mrdenny]

ASP.NET handles the authentication differently that the generic asp page does i think.

When you use clear text they still use thier windows account to log in, it just pops up a box to authenticate rather than getting the authentication from the workstation OS.



Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[bluecjh]

Danny,

But are you saying that 'Windows Authentication'
does not work properly with ASP, that would be
a very big problem, which would surely be well
documented. I'm a bit dumbfounded?

Chris

BlueCJH

 

[mrdenny]

Windows Authentication works fine with ASP. You just can't use that domain token to log into the SQL Server from the ASP server because the user never actually authenticated on the ASP server.

From the MSKB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;176377

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[bluecjh]

I don't really understand this, I don't want our
domain users to have to authenticate themselves again
I want to log the user in a db (discretely) as they use an asp page.

Can I not do this?

many thanks


BlueCJH

 

[mrdenny]

No you can not do this as the Microsoft KB article says unless you have them log in to the web site.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)