Login failed for user '(null)'.

Question

Login failed for user '(null)'.

Chi Chi Cabron

Ten Centuries

Points: 1087
More actions
November 10, 2008 at 2:00 pm

#196177

OK, I am sure there is a simple explanation for this, but it is driving me crazy.
We have 2 SQL Server 2000 servers, both in the same domain. Both are set for mixed-mode authentication.
Using SQL Query Analyzer, I can connect to either server using windows authentication. BUT when I connect to Server1 (using windows authentication) and try to run "SELECT * FROM [Server2].Database1.dbo.Table1" I get the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Permissions are correct for both servers. My question: why is it "user'(null)'"? Even if the permissions are wrong, shouldn't it at least see my windows login?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 1

Sounds like either an ownership chain issue or your Linked Server is set up wrong between the two.

EDIT: Sorry. To clarify, check your Linked Server to make sure it has all the correct security settings and is actually referencing the Server2 database in question.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

This is a Kerberos delegation issue because you don't have the appropriate SPN's set in Active Directory for MS SQL Service on both or either servers.

It's a frustrating thing.

It's most likely due to the fact that you are running SQL server as a domain account and not local system (good for you!)

There are a million articles on it in the MS Kbase but you'll end up using Setspn.exe to set the spn

Make sure you understand that it's 1 spn per host/service/serviceaccount. IF you change the service account your SPN breaks and nobody logs in.

Cheers!

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 3

It's not always a Kerberos issue, SQLBOT. I've actually encountered this issue on other occasions.

But SQLBOT brings up a good point. Have you actually verified that your SQL Services are running as Domain accounts and not local system?

Likely they are, given the error, but it never hurts to double-check.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Chi Chi Cabron Ten Centuries Points: 1087 More actions · Answer 4

Thanks for the help. I double-checked the linked server security settings and that did the trick. I had read about the SPN issues and I worried that was the problem (I am using a domain account for SQL Services). Thanks again for pointing me in the right direction....

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 5

Glad we could help. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.