Link to home
Start Free TrialLog in
Avatar of LuiLui77
LuiLui77

asked on

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' when running store procedure

Hello,

We have implemented a new application "A" that connects to a Database in instance "A" on server "A". The OS of database server "A" is Windows 2008 R2 using SQL 2008 R2.

We have another application "B" that connects to a Database in instance "B" on server "B". The OS of database server "B" is Windows 2003 R2 using SQL 2005.

Some data needs to be transferred from Database "B" to Database "A" in a weekly basis and for this we have setup a store procedure on Database "B" which calls a job which maps and allocate the data on a specific table on database "A".

When I try to run this store procedure from my computer (connecting to the Instance and Database B using SQL Management Studio) the following error message appears: Msg 18456, Level 14, State 1, Line 1, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Now if I RDP directly into the instance B server (with the same user as before) and run the store procedure from there, it runs successfully.

In order to run the store procedure I do not want to have to log into the server itself every time, as a matter of fact I am thinking about delegating this task.

It looks like the SQL permissions have been set correctly on both databases, but most probably there is something that I am missing in order to make it work from a third application or computer.

Any help will be greatly appreciated.

Thanks!
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

You are experiencing the double hop Kerberos problem, whereby a server is unable to pass the credentials from your client that are used to authenticate access to server b to authenticate access to server a. i.e. you can only have one hop. The hop you are using is between the client and server b, this Kerberos ticket can not be passed on to authenticate to server a to anonymous user is used and thus fails.

If you google "double hop Kerberos problem" you will find a myriad of scenarios and ways of fixing this, essentially you need to setup delegation on server b so that it is able to forward your authentication to server a.
ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LuiLui77
LuiLui77

ASKER

Thank you Paul, I will be looking into this right away
Some data needs to be transferred from Database "B" to Database "A" in a weekly basis and for this we have setup a store procedure on Database "B" which calls a job which maps and allocate the data on a specific table on database "A".
Which user is running the job? There's any linked server from database "B" to "A"?
What data are you transferring? A set of data, a full table or a join between tables?
Hello , I think I am on the right track with your advises Paul. I have spotted that the SPN for server A has not been created since the service account that I am using on this instance does not have any permission to create one, it is only a normal domain user.
Now, the weird thing is that there's a SPN registered for server B (this service account is a domain admin) but when I issue the query to see the authentication method (select net_transport, auth_scheme FROM sys.dm_exec_connections where session_id = @@SPID) it comes up that the authentication scheme is NTLM and the net_transport field is "Shared memory", should not  be Kerberos and "TCP" since the SPN is registered?  note: this server is 2003 with SQL 2005

Vitor, I am not sure which user runs the job but I will get that information. What I know is that is a smart connect job. The smart connect was configured to map the fields from one database to the other. Yes there is a linked server object configured.
The SPN registered for server b will be for the linked server that you mention has been created.
You don't need to create an SPN for server b just server a as this is the server that requires the delegation.
Yes there is a linked server object configured.
Also get the information about this linked server specially the security configuration.
Hi Paul, I have registered the SPN successfully, but if I run the query: "select net_transport, auth_scheme FROM sys.dm_exec_connections where session_id = @@SPID", it now says net_transport= Shared memory, auth_scheme= NTLM
instead of TCP, KERBEROS (how it should be). I wonder if this is normal until propagation has been completed.

Hi Vitor, I am getting the linked server info for you.

Thanks!
it now says net_transport= Shared memory, auth_scheme= NTLM
It sounds like you are running that on the server and not the client...
That's was correct Anthony, I ran it from the client and now is reflecting TCP KERBEROS, I will proceed with the additional steps now and keep you all posted.

Thanks!
Alright Gentlemen, the SPN setup for kerberos authentication + delegation worked. I can now run the store procedure from the SQL management studio installed on my system.

Thank you All, specially Paul!