Creating Linked Server - Impersonation of Windows Authentication

Question

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?

Answer 1

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.

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

Answer 2

As long as your AD account is on both servers, and has access to the database(s) in question this will work for you. Run this script on server A, replace LinkServerName with whatever you want to call it, and update the @datasrc aspect of this script to be your server B.

USE [master]
GO

/****** Object:  LinkedServer [LinkServerName]    Script Date: 12/5/2017 5:12:57 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'ServerName or ServerName\InstanceName'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkServerName',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkServerName', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO