trusted connection error from Query Analyzer

[cjblake]

On one of my SQL Server installations I have a linked server defined. I have created a stored proc that queries the linked server. If I log into the server as me (I'm an administrator) and use Query Analyzer to run the SP, it runs fine. If I am using QA from my own PC, I get: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Any ideas on what is causing this? I will be wanting to have this sp run from a .net application with another (non admin account), but I figured I'd start here at this point.

Thanks so much for any input!

CJ

 

[SQLDenis]

It looks like you don't have mixed authentication enabled-->

http://sqlservercode.blogspot.com/2006/01/login-failed-for-user-sa-reason-not.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cjblake]

Hi, Denis.

Thanks for your post. I checked and unfortunately it is already mixed authentication. I was hoping it would be so easy! Any other ideas?

Thanks!
CJ

 

[SQLDenis]

Is your domain user account added to that SQL box (domain\username)
Look under Security/Logins

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Another question
Are these 2 boxes on the same domain and if not is a trust created between the 2 domains?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cjblake]

The 2 servers are in the same domain. My user is added in both servers. (I can run this fine logged in as me from the SQL Server, but not from my machine, so I was thinking that the issue couldn't be related to my account.)

Thanks again,
CJ

 

[SQLBill]

Check that the MDAC level is the same on all the systems. Microsoft has a program for download called Component Checker. That will tell you the level of MDAC on the system it's run on.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

You need to setup the linked server to use a SQL Account when logging into the remote SQL Server.

You are butting up against the NTLM double hop.

Here is the MSKB on the issue.

http://support.microsoft.com/kb/238477/en-us

Yes, yes, I know that it says SQL 7 and NT 4.0 on it, but it's still valid to day with SQL 2005 and Windows 2003, and will continue to be valid until microsoft changes the way NTLM works (which probably won't ever happen).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com

 

[cjblake]

Denny, Thanks so much for your post!

Finally! It works now that I added that.

Cheers!

CJ

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com