3

I am attempting to utilize Kerberos delegation in order to allow Active Directory queries using a Linked Server configured for ADSI to be filtered according to the end-user's Active Directory security rights. (The environment I am in has a heavily-restricted AD)

I can successfully use the Linked Server if I configure it with connections will Be made using this security context and giving it a service user account with permissions to Active Directory. Any queries against the Linked Server return the expected (limited) results from Active Directory that the service user has access to.

Linked server create script:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\SERVICEACCOUNT',@rmtpassword='***************'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

However, when I change the setting to connections Be made using the login's current security context any queries I submit come back with unhelpful messages:

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://*****.*****.***/DC=*****,DC=*****,DC=***''
WHERE objectClass = ''User''
')
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://*****.*****.***/DC=*****,DC=*****,DC=***'
WHERE objectClass = 'User'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".

I have validated that my connections are coming in via Kerberos:

SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Which returns: auth_scheme = KERBEROS

In this case, the Microsoft SQL 2022 server is configured to run with a group-MSA. As part of troubleshooting this, I configured the group-MSA account with both constrained and unconstrained delegation:

Set-ADAccountControl -Identity gMSA_SQL$ -TrustedForDelegation $true -TrustedToAuthForDelegation $false
Set-ADServiceAccount -Identity gMSA_SQL$ -Clear 'msDS-AllowedToDelegateTo'

The error messages are the same no matter constrained/unconstrained.

The gMSA_SQL$ object has the proper SPNs (gMSA_SQL$ has rights to self-configure SPNs and Microsoft SQL properly does so upon start).

In order to test the delegation configuration, I configured a SQL Server Linked Server to another SQL Server that was available. The SQL Server Linked Server worked just fine.

I'm beginning to think bouncing against ADSI in order to perform the query is somehow messing up Kerberos. I searched far and wide for any examples/discussion about configuring the ADSI provider using constrained delegation and I found nothing applicable.

2 Answers 2

4

I figured this out moments after I posted this - due to our Active Directory being locked down, one of the accounts I was using for testing had the Account is sensitive and cannot be delegated attribute set.

Figure I'll leave this question up since it consolidates a bunch of configuration details for anyone else wanting to do this in the future.

0

Constrained Delegation and Resource-Based Constrained Delegation require a bunch of things to get double-hop authentication to work.

You have a few different user accounts here

  1. Actual user logging in.
  2. Service user for SQL Server.
  3. Machine account for SQL Server.
  4. Service user for linked server.
  5. Machine account for linked server.

  • All of these need to be part of the domain, not local users.
  • All machines need line-of sight of the domain controller obviously.
  • #1 cannot be Account is sensitive and cannot be delegated as you have noted.
  • #2 SQL Server and #4 linked server need to have an SPN registered against #3 and #5 respectively, which will happen automatically if it has the right permissions.
  • For Constrained Delegation, #2 needs Trust this user for delegation to specified services and you need to select the SPN for #4/#5. For gMSA accounts you need to do this manually in the Attributes
  • For RBCD you need to use Powershell:
    $LinkedServerAccount = Get-ADUser -AuthType Negotiate -Identity LinkedServerUserHere;
    $UserToDelegate = Get-ADUser -AuthType Negotiate -Identity ActualUserHere;
    Set-ADUser -AuthType Negotiate -Identity $LinkedServerAccount -PrincipalsAllowedToDelegateToAccount $UserToDelegate;
    

You can use either method here, but RBCD is newer and often considered more secure, as it puts control of who can be delegated to the Linked Server in the hands of that server, not in the hands of the intermediate server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.