AD authentication on SQL Server - Kerberos Windows to Windows

 1. Register SPN for SQL service account

SetSPN -S MSSQLSvc/win2012r2-e424b.dynamic.sit.cloud.td.com:3341 D2-TDBFG\TCRBI084SQLS

2. Enable Delegate

in Active Directory, go to SQL server account Properties, on "Delegation" tab, select "Trust this user for delegation to any service (Kerberos only)"

3. Verify SPN and delegate

Get-ADUser -Identity TCRDM463SQLSS -Properties *

After Enabled

4. confirm Authentication use Kerberos

select ServerName =@@ServerName, Servicename, service_account, status_desc from sys.dm_server_services
select @@ServerName, auth_scheme from sys.dm_exec_connections where session_id =@@SPID

5. Create Linked Server use AD authentication, access based on account use linked server

USE [master]

GO

/****** Object:  LinkedServer [DEV3]    Script Date: 11/9/2021 12:12:02 PM ******/

IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DEV3')

BEGIN

EXEC master.dbo.sp_addlinkedserver @server = N'DEV3', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'crcrdmsddvfvk0.d2-tdbfg.com,3341'

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEV3',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

END

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DEV3', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO