We help IT Professionals succeed at work.

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!
Comment
Watch Question

Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
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.
Software Engineer
Top Expert 2011
Commented:
There is a good article here on setting up Kerberos delegation:

http://www.seangallardy.com/2014/05/using-kerberos-with-sql-server-part-1-double-hop/

Author

Commented:
Thank you Paul, I will be looking into this right away
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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?

Author

Commented:
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.
Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes there is a linked server object configured.
Also get the information about this linked server specially the security configuration.

Author

Commented:
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!
Top Expert 2012

Commented:
it now says net_transport= Shared memory, auth_scheme= NTLM
It sounds like you are running that on the server and not the client...

Author

Commented:
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!

Author

Commented:
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!