Login Failed for NT Authority Anonlymous Logon

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-16 : 18:20:44

I have a linked server which will not authenticate to another linked server using integrated Windows Security. It is a double hop scenario. eg connecting to the SQL ServerA from the client, SQL ServerA then connects to SQL ServerB to retrieve data. All done in a single sproc. Here is what I have verified so far.

1.) Verified both SQL Server, and SQL Agent services are logging in with a user with administrative rights on the domail for both ServerA and ServerB

2.) Have verified, that there are Correctly Configured Security Principal Names for Both.

3.) Forced Authentication to use TCP/IP on both Servers.

4.) Verified, that procedure runs fine from SQL ServerA, which is where it is stored.

5.)Used a SQL Server Account instead of Integrated Auth. (It works fine. However this is not acceptable for my situation.)

6.)Checked to see that the SQL Server Service account is trusted for delegation.

7.) Rebooted both SQL Servers.

8.) Walked through every MS KB Article, that i could find on the MS Support sight dealing with Kerberos, and Authentication.

Can anyone think of anything that I have missed. I have workarounds but none of them will work for my particular scenario.

P.S. - On another occasion I ran into this scenario except the second hop was an IIS Server, and I straightened out the SPN's and forced the IIS Server to use Kerberos, and not fall back to NTLM. It worked. So I guess if there were a way to force SQL to use Kerberos, it would work as well.

Thanks,

TSQLMan

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-16 : 19:22:34

are the SQL services on ServerA running under the same account as the SQL services on ServerB?

-ec

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-16 : 19:24:10

Yes, they are both running under an account that has domain administrators rights.

Thanks,

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-16 : 20:07:05

From serverA can you make a windows authenticated connection to ServerB?

use data sources cpanel or QA or whatever to test this.

-ec

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 07:47:58

Yes, I only run into a problem, when I enter a 3rd machine into the equation. Also known as a double hop.

jason
Posting Yak Master

164 Posts

Posted - 2005-03-17 : 10:22:07

Are you using the same account at the client?

I'm really guessing here, but it seems if you are executing a procedure under one account and then connecting to another server under a seperate account (in the same session) it might cause a conflict.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 10:29:29

Here is the scenario.

User x opens Enterprise Manager from his workstation, and Opens ServerA goes to the linked Server selection under security, and attempts to see tables on ServerB. He then sees the error message "Login Failed for NT Authority/Anonymous Logon" Server A is configured to authenticate to Server B using Windows Integrated Authentication. This should pass his user name from Client x, to Server A, and then to Server B. (Double Hop) This is not happening, hence the error message.

Thanks for you comments.

TSLMan

jason
Posting Yak Master

164 Posts

Posted - 2005-03-17 : 10:50:04

Ah, Ok. Didn't read that close enough. The 'Anonymous' failure is the indication it is falling back to NTLM.

I think the SQL server computer must also be trusted for delegation.

http://support.microsoft.com/?scid=http://support.microsoft.com%2Fservicedesks%2Fwebcasts%2Fen%2Fwc082702%2Fwct082702.asp

Do a search on 'kerberos' and read the surrounding paragraphs. Seems to go step by step.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 10:53:12

I checked everything there, but I will take a second look.

Thank You,

TSQLMan

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 11:19:17

I can't get your link to open. Is there a KB Article Number.

Thanks,

TSQLMan

jason
Posting Yak Master

164 Posts

Posted - 2005-03-17 : 11:21:54

No, this is a support webcast transcript.

Try cutting and pasting into address box.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 12:11:01

Nothing there that helps.

Thanks

jason
Posting Yak Master

164 Posts

Posted - 2005-03-17 : 12:59:48

Ok, so Server A is set for delegation and the service account used by Server A is set for delegation?

Have you setup SPNs for both Server A and the service account?

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 13:19:04

Yes, I also checked for duplicate SPNs'. The problem definitely seems to be that it is falling back to NTLM, but I have no idea why. We run AD in Mixed Mode, and the sight where Server A is has both a 2000 and NT 4.0 Domain Controller. I am beginniing to wonder if we are not authenticating throught the old NT Domain Controller. That woudl explain the NTLM.
What do you think?

Thanks,
TSQLMan

jason
Posting Yak Master

164 Posts

Posted - 2005-03-17 : 14:06:59

You could stop the netlogon service on the NT 4.0 DC to test that I think.

Also, you should be able to see if the client is using Kerberos.

http://support.microsoft.com/kb/262177

TSQLMan
Posting Yak Master

160 Posts

Posted - 2005-03-17 : 14:09:20

Thanks, I had seen that article before, but forgot to actually turn it on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources