Some users fail to query linked server with "Login failed for user NT AUTHORITY\ANONYMOUS LOGON"

Question

When I try to run a simple query involving a linked server it fails:

SELECT * FROM [server2].[DWH].[dbo].[SomeTable]

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

But, there is another user who can run this query without problems.

---

Our company doesn't have a SQL Server DBA and we have inherited a few SQL Servers from another company after acquisition.

I'm a developer trying to run some queries and I'm having hard time trying to figure out how to configure access correctly. I don't really know where to look, so I'll try to explain the current setup to the best of my ability.

There is Server1:

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) 
    Aug 15 2017 10:23:29 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

There is Server2:

Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 
    Jun 15 2019 07:56:34 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Our company has a domain MAIN_DOMAIN and I log into my Windows laptop as MAIN_DOMAIN\my.name. When I run SSMS on my laptop I can connect to both Server1 and Server2 using Windows authentication.

As far as I understand, my login in both SQL Servers has pretty much all the permissions:

Server1:

CREATE LOGIN [MAIN_DOMAIN\my.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [setupadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [processadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [diskadmin] ADD MEMBER [MAIN_DOMAIN\my.name]

Server2:

CREATE LOGIN [MAIN_DOMAIN\my.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [MAIN_DOMAIN\my.name]
ALTER SERVER ROLE [setupadmin] ADD MEMBER [MAIN_DOMAIN\my.name]

The Server1 and Server2 themselves are not in MAIN_DOMAIN, they are in other_domain.com

I can Remote Desktop to both of them using other_domain\my.name user with a different password than my main domain user.

This is how the linked server is configured on Server1:

EXEC master.dbo.sp_addlinkedserver @server = N'server2', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'server2', @optname=N'remote proc transaction promotion', @optvalue=N'true'

On Server1 the MSSQLSERVER service (sqlservr.exe) runs under user called NT Service\MSSQLSERVER.

On Server2 the MSSQLSERVER service (sqlservr.exe) runs under user called NT Service\MSSQLSERVER.

---

Here is output of setspn -l. I get the same output when I run it on server1 or server2:

C:\Windows\system32>setspn -l SERVER2
Registered ServicePrincipalNames for CN=SERVER2,OU=Azure Resources,OU=Corporate,DC=other_domain,DC=com,DC=au:
        MSSQLSvc/SERVER2.other_domain.com.au:1433
        MSSQLSvc/SERVER2.other_domain.com.au
        WSMAN/SERVER2
        WSMAN/SERVER2.other_domain.com.au
        TERMSRV/SERVER2
        TERMSRV/SERVER2.other_domain.com.au
        RestrictedKrbHost/SERVER2
        HOST/SERVER2
        RestrictedKrbHost/SERVER2.other_domain.com.au
        HOST/SERVER2.other_domain.com.au

C:\Windows\system32>setspn -l SERVER1
Registered ServicePrincipalNames for CN=SERVER1,OU=Azure Resources,OU=Corporate,DC=other_domain,DC=com,DC=au:
        MSSQLSvc/SERVER1.other_domain.com.au:1433
        MSSQLSvc/SERVER1.other_domain.com.au
        Microsoft Virtual Console Service/SERVER1.other_domain.com.au
        Microsoft Virtual Console Service/SERVER1
        Microsoft Virtual System Migration Service/SERVER1.other_domain.com.au
        Microsoft Virtual System Migration Service/SERVER1
        Hyper-V Replica Service/SERVER1.other_domain.com.au
        Hyper-V Replica Service/SERVER1
        WSMAN/SERVER1
        WSMAN/SERVER1.other_domain.com.au
        TERMSRV/SERVER1.other_domain.com.au
        TERMSRV/SERVER1
        RestrictedKrbHost/SERVER1
        HOST/SERVER1
        RestrictedKrbHost/SERVER1.other_domain.com.au
        HOST/SERVER1.other_domain.com.au

Unfortunately, I don't understand what it means. The only thing that I understand here is that MAIN_DOMAIN is not mentioned anywhere.

---

A different user can run the query involving linked server without problems.

He logs into Server1 using Remote Desktop and using other_domain\his.name. He runs SSMS on that Server1 and connects to SQL Server using Windows authentication.

His login on Server1 has all permissions as well:

CREATE LOGIN [other_domain\his.name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [other_domain\his.name]
ALTER SERVER ROLE [serveradmin] ADD MEMBER [other_domain\his.name]

---

What do I need to configure, so that I could run a query involving linked server while I'm connected to SQL Server from my laptop using MAIN_DOMAIN\my.name ?

Answer 1

You have to verify these requirements:

• AD Trust between source domain and target domain
• Proper AD SPN registration at least for the linked server source instance
• You have to verify proper DNS resolution in both domains for TGT requests (Are they in the same AD forest?

Sorry but there is not an easy answer and to set all these things you must have Domain Admin right (or even forest admin)

You can verify by yourself spn for instances with this cmd:

setspn -l server1$
setspn -l server2$

Take a look at this info about kerberos:

how to stop using sql server login credentials in a linked server?