Login failed for user ''NT AUTHORITY\ANONYMOUS LOGON''.
-
Afternoon All,
Ah! I hear you all sigh, that old chestnut. Just go and check the permissions to solve the problem.
Well. I have run out of things to check! The situation is as follows.
I have a user on server 3 with a login of domainName/username. He also has exactly the same login on server 1.
Server 3 has a linked server of server 1 and when I look at the linked server properties I see that the same login is shown, with the impersonate box ticked.
The user is listed as dbo on the server 1 Database that is to be interrogated.
The following query is run "select all from server1.database.dbo.tableName" and this produces the error in the heading.
As the person in question has a login on both servers, is listed in the linked server and is the dbo of the table being queried I cannot see why SQL returns "Anonymous login" and the query fails.
Clearly I am missing some vital stage, but no amount of googling produces any help. I therefore throw myself on the tender mercy of this group, which is the best I have dealt with!
Thank you
Colin
-
Hi,
I always make the following error:
I do have good user mappings. Say, mylogin to mylogin, or mylogin - impersonate etc. in the Linked Server Properties. BUT... I have a server (server3 in your case) registered in Enterprise Manager as login2. So I open my EM with a focus on Server3 ->Tools->Query Analyzer and run a query. And it will fail because the query window after all my actions opens as Login2 as a security context. And this Login2 does not have any mappings. The resolution is not to skip steps and in the Query Analyzer use File->Connect and connect to the Server3 as a login that has valid mappings in Linked Server properties for Linked Server1. Or you may change Server3 registration properties to be registered in the same security context as the person who has mappings in Linked Server Properties
Regards,Yelena Varsha
-
Thanks for the quick response. However, this does not solve the problem. The top margin of QA always shows the server name that it is logged into and iit clearly shows server 3. All logins are Windows authentication.
Is there a way to find the login that is being used by QA? That would assist in testing your point.
Colin
-
Have just spotted the Window drop down menu which has an option WINDOWS. This confirms that the user login is the same as that shown on both servers and in "linked server". Looks like the issue lies elsewhere.
Colin
-
This is what I found so far except for Impersonate advice:
this is from
http://www.talkaboutsoftware.com/group/microsoft.public.sqlserver.connect/messages/41047.html
who is replying to her own question:
Regards,Yelena Varsha
-
This sounds like a "double-hop" issue. When a client goes from the workstation to the first
SQL Server, that's the first hop. When you go from the first SQL Server to the second SQL
Server, that's the second hop. Under NT4.0, the only authentication method allowed is
NTLM. NTLM did NOT allow double-hops. We used to see these issues all the time before folks
started converting to Active Directory.
Active Directory attempts to use Kerberos as the authentication method first. Failing that
it drops back to NTLM to support legacy clients. However, unless you specifically make
some configuration settings, Kerberos does NOT allow double-hops, either. Kerberos does do
so through delegation, but you have to do some setup. Here are the basic steps, but
before going through them, make sure everyone involved has read the appropriate documentation
on Kerberos delegation. Kerberos delegation adds risk to your security posture and should
not be done haphazardly.
1) You have to configure the first SQL Server as being allowed to delegate. This needs to
be done by your directory (domain) admin. In Windows 2000 Active Directory it's simply a
checkbox. In Windows 2003 Active Directory there's the concept of constrained delegation
and so there a whole lot more settings.
2) You have to configure the service account on the first SQL Server to be allowed to
delegate. You also need this done by your directory (domain) admin. There is an option
under the Account tab saying "Account is trusted for delegation" that must be checked.
3) The SPNs on the SQL Servers must be set properly. Here's a knowledgebase article that
details how to do it. Again, directory (domain) admin help needed.
How to use Kerberos authentication in SQL Server (319723)
There are additional articles that may provide some help:
How to troubleshoot the "Cannot generate SSPI context" error message (811889)
Oh, and one more thing... kerbtray is your friend when troubleshooting.
K. Brian Kelley
@kbriankelley -
Just an FYI...I currently have a support case in with Microsoft about this issue. Although it may not be the same scenario, we started experiencing this problem when we updated our "middle-hop" SQL server to a SQL 2005 x64 machine. I think it has something to do with the 64-bit part, but I can't be sure. We've almost exhausted all available troubleshooting options. The only odd thing is that the double-hops will work for a period of time (from 10-minutes to several weeks) after the SQL service is restarted. The case is still open, so I'll post any solutions...
-Dan -
dj meier I'm currently experiencing the same issue with SQL 2005. Although, it's not the 64 bit version. I have a linked server set up on Server A (2005) that points to Server B (2000).
I have sql 2005 installed locally and Server A is a registered server. When I execute a query locally on Server A that references my Server B I get the error. But if I execute the query directly on Server A (logged in to the physical machine) I don't have any problems.
I'm not sure if this is a double hop issue or not. I've never had this problem when all my machines were running SQL 2000. Any word from Microsoft?
Any help is much appreciate. -
dj & Jason,
We also are experiencing this issue. How did you resolve it?
i.e. consider:
A. Workstation w/Query Analyzer
B. Server2005 with a linked server pointing to...
C. Server2000 with data in a table
Scenario1: Sitting at A, query thru B to C fails
Scenario2: Sitting at B, query thru B linked server to C succeeds
Scenario3: (after scenario2 is executed); Sitting at A, query thru B to C succeeds.
Scenario4: (restart sesssion/server) Sitting at A, query thru B to C fails
-
Hi,
This issue had to do with a double hop scenario, where windows can't pass
along the windows based authentication. I found the following link
very helpful in understanding this issue (nice diagrams with detailed
explanations).
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx
Solution:
First, setup a SQL Login with the appropriate permissions on the "linked server". Then use the following extended proc on the server that connects to the linked server via query:
EXEC sp_addlinkedsrvlogin 'linkedservername', 'false', 'Domain\Jay',
'sqllogin', 'sqlpassword'
This will configure the server to use the sql login and password to
connect to the linked server when user Domain\Jay is logged on. Hope
this helps. Let me know if you have any trouble with this, I'll gladly help out.
Jay
-
Thanks, Data Demon for the link and the idea. However, we are very much hoping to work this Double Hop issue out using Windows Authentication. I could also have pointed out that we are introducing SQL 2005 into a group of 40+ SQL 2000 boxes. We are hoping to use theSQL 2005 box(s) to link to all the 2000 boxes for management/reporting via linked servers.
We have gone thru the ringer getting account delegation set up and want it to work on the 2005 boxes.
There is a nice summary of how to do this here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59204
But, there seems to be some additional trick on Win2003/SQL2005 boxes.
Steve
-
K. Brian Kelley, thanks a lot. That was the issue for me.
-
WOW, this is interesting I though would be good to share it since it is related.
SQL 2005 SP3, Server A has link server to Server B (be made using current Security context)
running a link server query within Management studio (with My Domain account) against Server B like this.
(1) My workstation-->server A-->Link server B---failed
(2) on Server A--> Link Server B--> success.
until now it is OK, I know it is double hub and ..........
if I do step (1) again after step (2) now I am getting success!!!
if I try step (1) after few minutes getting fail again!!!
so step (1) only succeed if I do step (2) first???/
what the ........
-
shaneat,
When you log on in step 2, you are on Server A and logging on to Server B. In doing this, Server A caches the credentials to Server B (typically for around 10 minutes).
When you then (remotely) login to Server A and execute a linked server to B, Server A is using the cached credentials from the earlier connection to B. You can only do this for a limited amount of time. The Kerberos set up is still not accurate.
Steve
-
Can you please explain in detail how to implement your 3 options you mentioned ?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply