Linked Server Anonymous Login Error

Question

Here is the issue I am facing.

I have a SQL Server that run in an Azure VM, and I use Windows Authentication to log into the VM and when using SSMS.

When I create the Linked Server inside the Azure VM, I test the connection, and the connection works.

From my local, when I test the connection, I get the following Error: 'NT AUTHORITY\ANONYMOUS LOGON'

When I run from local:
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;
It returns TCP, NTLM

When I run SSMS inside the VM:
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;
It returns Shared Memory, NTLM

I did some reasearch, and it seems it is something related to "double hop", question is how do I fix this?

Both Local and VM are registered in the same domain.

Answer 1

When you connect to the linked server, SQL Server must be able to pass you Windows credentials to the other machine. This is not difficult, if you logged on to Windows on the same machine as SQL Server is running on.

But if you logged on to SQL Server remotely, it's a different matter. For this you need to have Kerberos set up correctly. Kerberos is more of Windows thing than SQL Server thing, so you should probably talk with you Windows administrator.

Here is a link to a presentation about Kerberos and SQL Server by David Postlethwaite which explains this in more detail. But I will need to add the caveat that he is only talking about on-prem scenarios. I have an uncanny feeling that the fact that the VM is in Azure may add further complications.

Answer 2

Hi @SUZUKI, MARKUS ，

In this doc, this error is explained as follows:
lient make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as 'ANONYMOUS LOGON'.

There is no SPN registered for the SQL Server

The workaround here is

a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.

b. use NP connection.

c. change your sql server to run under either localsystem account or networkservice account.

Here, a is recommended.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.