CODEWITHSUNDEEP
sql-serverlinked-serverkerberos-delegation
Jonny_Bravo
Jonny_Bravo

Reputation: 88

SQL Linked Server issue with impersonation and constrained delegation

I am struggling with setting up a SQL Linked server on SQL2017 that uses impersonation with a windows domain account and Kerberos Constrained Delegation.

I have 4 SQL nodes in a cluster and a linked server (the same config) on each node that points to an AG listener in the cluster. I am trying to get a stored proc to run on each SQL node via SQL agent that connects to the listener (via the linked server) to connect to a primary version of a database.

The linked server is using the Microsoft OLE DB Provider for SQL Server provider. The linked server code is:

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'ListenerName.domain.co.uk,portnum', @provider=N'SQLNCLI', @datasrc=N'ListenerName.domain.co.uk,portnum'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=N'Domain\SQLProcUser',@rmtuser=NULL,@rmtpassword=NULL

As you can see the linked server is set up to be made using the logins current security context, unless 'Domain\SQLProcUser' is used where as it impersonates that user on the remote servers.

The stored proc is owned by and run as a windows domain account that is not an SA. The domain account has the correct permissions against the database it is trying to connect to (we will call this account SQLProcUser). This has been tested by running the same select statement locally against the database.

I have followed the guidance from MS here around setting up the Kerberos constrained delegation. Our scenario fits into scenario 2 from the document. All the SQL nodes SQL services are running under the same domain user as part of the same cluster (we will call it SQLServiceUser).

The SQLServiceUser in Active Directory Users and Computers has been set to 'Trust this user for delegation to specified services only (Use Kerberos only)' and the MSSQLSvc services has been added for all the SQL nodes FQDN (plus port number) and the Listeners FQDN (plus port number). 'Account is sensitive and cannot be delegated' is unticked.

ADCU delegation properties

All the SPNs are already registered for SQLServiceUser as the cluster is up and running already. In ADSI for the SQLServiceUser the attribute msDS-AllowedToDelegateTo has all the SQL nodes and the listener.

However.. Whenever I try and run either the stored proc on any SQL node against the linked server or an execute as login='Domain\SQLProcUser' using the linked server I get the following error:

Msg 7437, Level 16, State 1, Line 5
Linked servers cannot be used under impersonation without a mapping for the impersonated login.

It is infuriating as the impersonation mapping is there, the delegation is set-up and the permissions are all there. There are no messages in the SQL logs and the event viewer does not show anything either.

Google has failed me as it just goes round in circles about setting up impersonation in the security tab which I have already done. Can someone point me in the right direction of what next steps I should try and how I could troubleshoot/resolve this?

Upvotes: 0

Views: 1747

Answers (1)

Galrick Gak
Galrick Gak

Reputation: 11

enter image description here

When you select this option you do not need to create an explicit mapping for the user. Make sure delegation spns are setup correctly and the account in AD can be impersonated.

Upvotes: 1

Related Questions