SQL Server – Creating Linked Server with Windows Authentication Impersonation

linked-serversql serversql-server-2008-r2sql-server-2016

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 DOMAIN\User.

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 KERBEROS

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

returns False

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 DOMAIN\User account?

Best Answer

Check to see if service account on source server is trusted for delegation. If it is not, enable it. You can identify which account needs this setting with the following query

SELECT servicename,service_account FROM sys.dm_server_services WHERE filename LIKE '%sqlservr.exe%';

you can do so via powershell

Get-ADUser serviceaccount -Properties trustedfordelegation|select trustedfordelegation
Set-ADUser -Identity serviceaccount -TrustedForDelegation $true

or with AD users and computers: on the properties of service account, delegation tab. enter image description here

A restart of SQL Server may be required after making this change.

Related Question