Sql-server – Some users fail to query linked server with “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”

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 ?