Setup Kerberos Constrained Delegation for Group Managed Service Accounts

Kerberos delegation is not a new concept in Active Directory; however, setting it up for Group Managed Service Accounts (gMSA) can be a bit confusing. Unlike normal domain accounts, gMSAs do not have a GUI for configuring delegation. Those configuraitons will need to be handled through PowerShell.

A common use for Kerberos delegation is to solve the authentication double hop. For example, a user accesses a report which is configured to use a remote data source. That’s one hop from the user’s workstation to the webserver and then a second hop from the webserver to the data source.

Our environment will be setup with the following configuration.

  • User
    • Workstation = SOLO
    • Login = GOVLAB\patrick-admin
  • PowerBI Report Server (on-premise)
    • Hostname = CORELLIA
    • URL = https://powerbi.govlab.corp/reports/
    • gMSA Account = GOVLAB\CORELLIABI1$
  • Data Source
    • SQL Server = DEATHSTAR\SQL2019
    • Database = DBA
    • gMSA Account = GOVLAB\DEATHSTAREN5$

The data source credential is configured to login “As the user viewing the report”.

If we try to run the report without configuring Kerberos delegation, then we’ll get the following error.

And testing the data source, we should get a login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

The first step in setting up Kerberos delegation is we need to use SETSPN with the “-S” option create the SPNs for both the SQL Server and PowerBI services. The “-S” option only creates the SPN if a duplicate does not already exist.

SETSPN -S MSSQLSvc/DEATHSTAR.govlab.corp:SQL2019 GOVLAB\DEATHSTAREN5$
SETSPN -S MSSQLSvc/DEATHSTAR.govlab.corp:11004 GOVLAB\DEATHSTAREN5$
SETSPN -S HTTP/CORELLIA.govlab.corp GOVLAB\CORELLIABI1$ 

You can verify them using SETSPN and the “-L” option. This option just lists any current SPNs.

SETSPN -L GOVLAB\DEATHSTAREN5$
SETSPN -L GOVLAB\CORELLIABI1$

Next, we need to configure delegation for the PowerBI service account. This is where we need to use the PowerShell commandlet Set-ADServiceAccount. If you are familiar with the GUI, this enables the options “Trust this user for delegation to specified services only” and “Use Kerberos only”, and only allows delegation to the specified SQL Server. We can verify the settings by using Get-ADServiceAccount.

Set-ADServiceAccount -Identity CORELLIABI1$ -Add @{'msDS-AllowedToDelegateTo'='MSSQLSvc/deathstar.govlab.corp:SQL2019','MSSQLSvc/deathstar.govlab.corp:11004'}
Get-ADServiceAccount -Identity CORELLIABI1$ -Properties TrustedForDelegation,TrustedToAuthForDelegation,msDS-AllowedToDelegateTo

At this point, you will need to stop and restart both services so they can pick up the SPNs and the delegation options, and then we can test the data source in the report.

Since that was successful, let’s test the report.

We can even verify from within SQL Server that we’re using Kerberos delegation by searching for connections coming from the PowerBI server (CORELLIA) with the user’s login (GOVLAB\patrick-admin).

SELECT
	 s.login_name
	,s.host_name
	,s.host_process_id
	,*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.host_name = 'CORELLIA'
AND s.login_name = 'GOVLAB\patrick-admin';

Using the host_process_id value from the above query, we can open Task Manager on the PowerBI server to verify the process ID (PID) of the PowerBI service is 3944.

The steps above outlined how to setup constrained delegation, but if you want to change to unconstrained delegation or constrained delegation using any authentication, then you would need to use both Set-ADAccountControl and Set-ADServiceAccount.

#TURN ON CONSTRAINED DELEGATION (Use any authentication protocol)
Set-ADAccountControl -Identity CORELLIABI1$ -TrustedForDelegation $false -TrustedToAuthForDelegation $true
Set-ADServiceAccount -Identity CORELLIABI1$ -Add @{'msDS-AllowedToDelegateTo'='MSSQLSvc/DEATHSTAR.govlab.corp:SQL2019','MSSQLSvc/DEATHSTAR.govlab.corp:11004'}

#TURN ON UNCONSTRAINED DELEGATION
Set-ADAccountControl -Identity CORELLIABI1$ -TrustedForDelegation $true -TrustedToAuthForDelegation $true
Set-ADServiceAccount -Identity CORELLIABI1$ -clear 'msDS-AllowedToDelegateTo'

Finally, to turn off delegation, you can use the following commands.

Set-ADAccountControl -Identity CORELLIABI1$ -TrustedForDelegation $false -TrustedToAuthForDelegation $false
Set-ADServiceAccount -Identity CORELLIABI1$ -clear 'msDS-AllowedToDelegateTo'

Keep in mind there are other prerequisites to make Kerberos delegation work within Active Directory, including ones that may be specific to your environment. We’re not covering those in this article; however, I have listed a few things to check that are specific to our example.

Edit the rsreportserver.config file to change the AuthenticationTypes value to RSWindowsNegotiate. This file will be located in the PowerBI install directory. Our example is located in the following path.

On the PowerBI report server (CORELLIA), make sure the PowerBI service account has the privilege “Impersonate a client after authentication”.

In Active Directory, make sure the user account (GOVLAB\patrick-admin) has the option “Account is sensitive and cannot be delegated” unchecked.

Additional reference:

https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/configure-kerberos-delegation-group-managed-service-accounts

https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/cc731241(v=ws.11)

Share