I am trying to set up a linked server on Server A to Server B using windows authentication.
Using SSMS, I can connect to both Server A (from ServerB) and Server B (From Server A) using Windows Authentication and the username
Additionally, when I log into Server A from Server B or vice versa and run the following command:
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
the result tells me the auth_scheme is
The SQL Server service on ServerA is running as the same domain account as I am trying to log in as:
Get-ADUser UserAccount -Properties trustedfordelegation|select trustedfordelegation
When trying to set up the linked server on Server A, I get an error message which is different depending on which option is selected in the For a login not defined in the list above, connections will: section.
To me this suggests that the config in the Local server login to remote server login mappings: is being ignored.
I have set the Local Login to
DOMAIN\User and clicked the Impersonate box
Server A is SQL Server 2008R2 and Server B is SQL Server 2016 SP1
Can someone confirm what I need to do to be able to set up the linked server to connect using the