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?

• John: What is your actual question? What exactly are you trying to find out?
• SE1986: I've added anther line to the end. Simply, I want to create a linked server on server A to server B and use windows authentication as the login
• John K. N.: When you state that it works with SSMS is this from the SQL Server A or from your laptop/computer? (Think firewall) Have you tried doing it from SQL Server B (2016) to SQL Server A (2008 R2)? (Think drivers) Doe your SQL Server have permission over their own Active Directory object? (Thnik Kerberos). And do your SQL Servers have an SPN set? (Kerberos again).
• SE1986: @hot2use whilst I haven't finished working through all your suggestions I have updated the question with further info. Firewalls look unlikely to be the issue but the connection is using kerberos
• Bob Klimes: does the service account running sql server on your source have the trusted for delegation property set(Posh: Get-ADUser serviceaccount -Properties trustedfordelegation|select trustedfordelegation)? you can user kerberos config tool to validate setup microsoft.com/en-ca/download/details.aspx?id=39046

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. enter image description here

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

• SE1986: My SQL Server service for the instance I am connecting to is running as an AD account (the same on I am logging into SQL as) so presumably that is the account I would check this information for?
• Bob Klimes: Yes (which ever account is returned by: SELECT servicename,service_account FROM sys.dm_server_services WHERE filename LIKE '%sqlservr.exe%';).
• SE1986: Thanks. The AD account is not trusted for delegation. I don't know if I have permissions to change so will look into that. Thanks for your help

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

• SE1986: This hasn't worked unfortuantely. When I run it on a session connected as DOMAIN\user, the query succeeds but when I test the remote server, I get "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' (Microsoft SQL Server, Error 18456)"
• Kin Shah: Check if SPNs are configured properly - use dbatools.io/functions/test-dbaspn
• H.79: @kin is correct. If your SPNs aren't registered for your SQL Server Engine service accounts then it will result in the NT AUTHORITY\ANONYMOUS error.
• Bob Klimes: @H.97, if sys.dm_exec_connections is returning connections as kerberos, then you can assume SPNs are set correctly, otherwise that DMV would return NTLM.
• H.79: @BobKlimes - good point on the making sure the service account is set to be trusted for delegation in your post. There are configs that have to happen up front in the preparation stages of building out a server for all of this to work.