Revision d094264e-05e5-47ee-9a80-938626c8fb93 - Database Administrators Stack Exchange

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`.

[![sql server service user][1]][1]

-------------------

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` ?


  [1]: https://i.sstatic.net/HpCPL.png