Background

One of the most painful troubleshooting experiences for me has been trying to figure out how to setup SQL Server Reporting Services (SSRS) to use Kerberos Constrained Delegation. This is where you try to execute a report using Data from a SQL Server Instance on a different computer. As a result you receive the unhelpful and annoying ‘NT Authority\ Anonymous Logon’ error whenever you try to run your report. The issue stems from the fact that the server running reports cannot pass your authentication to the data source

The problem that I always ran into with this issue is that I could never find the right blog, or guide to get through the entire setup without the guide deviating from my config or becoming unclear. Then to add to the complexity you need to edit Service Principal Names (SPN) on Active Directory (AD) computer objects, which means you need a Domain Admin if you are not one already. After a lot of struggling and understanding how this works and finally getting it to work on SSRS. I felt I would never run into this problem again, until today.

I have a SQL 2016 Always On Availability Group cluster that needs a linked server to a SQL 2017 Server (a different but similar problem as the SSRS example above). The one variance from the normal Kerberos setup is that the 2016 cluster is using a Group Managed Service Account to run the SQL Service. This is a new type of domain controlled service account introduced in Server 2016. Instead of being a traditional AD user object (for which you may need to manually change the password from time to time) the gMSA is managed by AD and has it’s password changed automatically based on the domain policy. So unlike a standard user it does not have the delegation tab in Active Directory Users and Computers (ADUC) once an SPN has been set for it. (If you are interested in the processes of setting up gMSA with SQL I suggest this blog from Norm Eberly)

Methodology

The steps we are doing follows most of the same guiding steps you would need for other Kerberos Constrained Delegation setup…

• Ensure SPNs are configured on service account and Data Sources
• Delegate Access on the service account to the Data Source
• Test linked server (or other delegated service)

Checking SPNs

The Service Principal Name on a AD object helps advertise a service for a specific object in the network. When you install SQL on a computer and use the default service the HOST/ SPN is already setup on it and usually handles Kerberos authentication from one AD object to another. You should check each computer and service account used in your setup. To check the SPN of a Computer account or the gMSA use the following:

For your servers this should return services like HOST/ServerName HTTP/ServerName WSMAN/ServerName MSSql… If the gMSA returns nothing for you will need to add the MSSQLSvc for each of the nodes in the AG Cluster. Use the following (Note: you will need Domain Admin access or be delegated permissions to execute this against an object):

If you are using a named instance then you can add :Name to the end. In some instances you may need to give the port number as well if there are multiple instances on one box.

Configure Delegation

In a typical setup with a standard AD User Object you could open ADUC and click the delegation tab, but in this case of a gMSA no delegation tab exists after this step. The key of this delegation tab is that you are marking which service (on which computer) the current service account is allowed to pass a users credentials to. While the specific tab does not exist if you open the Advanced Options on ADUC you can view the Extension Attributes. (Note: You may also set all of these settings via PowerShell when you create the account via New-ADServiceAccount , or by updating the account Set-ADServiceAccount)

First: locate the msDS-AllowedToDelegateTo

• You need to make sure that you launch ADUC and have “Advanced Features” enabled.
  • This can be done by clicking the “view” > “Advanced Features.”
• Navigate to the gMSA, Right Click, and select “Properties”
• Select the “Attribute Editor” tab
• Navigate to “msDS-AllowedToDelegateTo” Attribute
• Click “Edit”
• Type the Service Name of the Data Source you want your Linked Server to pull data from and click “Add” and “OK”
  • Remember that the gMSA is running the SQL Service you are setting the Linked Server up on.

Second: Set the userAccountControl

• Still looking at the “Attribute Editor” tab on the gMSA account
• Navigate down to the “userAccountControl” attribute
• Click “Edit”
• Replace the Value with 16781312
  • This is not in the official list, but this value was discovered via configuring a standard AD User Object.
  • Edit: the value comes from combining the userAccountControl values of WORKSTATION_TRUST_ACCOUNT (4096) and TRUSTED_TO_AUTH_FOR_DELEGATION (16777216)
• Click “OK” and close all Dialogues

Testing LinkServer

You may need to restart the SQL Service before creating your Linked Server, but you should be able to create it after this and execute a query against it. If you are still receiving errors, you may need to make sure that you have the correct SPNs on the correct Service Account, and that you have permissions on the destination. It may also help to have both the NetBIOS name and FQDN of the destination server as an SPN.

Conclusion

This go around of figuring out Kerberos Constrained Delegation was not as difficult as my first go around (which lasted about 6 months of on and off troubleshooting), but it is nothing I would wish on anyone. The benefits of this if you get it right are the key – it allows you to setup a linked server (or even SSRS) with the constraints of AD credentials. This promotes least privileged access on your SQL Instance where you may have a shared environment and not everyone should have access to the remote system.