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!
LuiLui77Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
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.
0
Paul JacksonSoftware EngineerCommented:
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/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LuiLui77Author Commented:
Thank you Paul, I will be looking into this right away
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
LuiLui77Author 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.
0
Paul JacksonSoftware EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes there is a linked server object configured.
Also get the information about this linked server specially the security configuration.
0
LuiLui77Author 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!
0
Anthony PerkinsCommented:
it now says net_transport= Shared memory, auth_scheme= NTLM
It sounds like you are running that on the server and not the client...
0
LuiLui77Author 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!
0
LuiLui77Author 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.