Resource Based Kerberos Constrained Delegation


By:   |   Updated: 2020-07-14   |   Comments   |   Related: More > Security

Problem

I am the SQL Server Database Administrator for an organization whose Active Directory structure now includes a forest with multiple domains. A number of users from the new domains mentioned that they are receiving errors when accessing web application that displays data from one of our SQL instances "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON". The Active Directory administrator checked the trust between the domains and each trusts the other. We set up constrained delegation after reading the tip on Understanding When SQL Server Kerberos Delegation is Needed when there was only one domain and all has been working well for those users. We want to continue using Kerberos for authentication to the SQL instance. What other options are available to us for cross-domain authentication?

Solution

There are a number of methods that can be used to retrieve data from databases via web applications. Of those, the most secure method is by using Kerberos authentication, but with it comes some complexity to implement. For Kerberos authentication to be successful, certain Active Directory objects (user/service accounts, computers, etc.) must be "trusted", or delegated authority, to act on behalf of application users to request a service ticket for those users within Active Directory. There are three delegation types: Unconstrained delegation, Kerberos Constrained Delegation, and, starting Windows Server 2012, Resource-Based Kerberos Constrained Delegation. Before we go into what Resource-Based Kerberos Constrained Delegation is and how to use it, let us review the history of delegation in Active Directory.

Unconstrained Delegation

When Windows 2000 and Active Directory were first introduced, only one delegation type was available-unconstrained delegation. Unconstrained delegation means that the Active Directory object is trusted to delegate, or act on behalf of, any object using any service that uses the Kerberos protocol. Since that time there has been a number of revelations about the risks of enabling unconstrained delegation on accounts, including How Compromise of a Single Server Can Compromise the Domain.

Example of an Active Directory object with unconstrained delegation enabled.

Kerberos Constrained Delegation

In Windows 2003 a new delegation type was introduced-constrained delegation. With this delegation type, delegation is created in each direction (i.e., between the web server and SQL instance and vice-versa) and is based on Service Principal Names. Service Principal Names can be registered either manually by someone with permission to change the Active Directory account running the SQL Server service, or by the service account itself if it is given the correct permissions. Constrained delegations must be created by an account with at least domain admin privileges. One of the limiting factors with constrained delegations is that it will only work for a single domain, and the fact that there might be a parent-to-child relationship and trust between domains, like in a forest, does not matter. So, if there are multiple domains in your forest then you would likely have to enable unconstrained delegation on at least two Active Directory accounts for a web application to work. That is, until Windows 2012 was introduced.

Resource-Based Kerberos Constrained Delegation

Resource-Based Kerberos Constrained Delegation (what my agency calls "RBKCD") was introduced in Windows 2012 and is a way of getting Kerberos authentication to work in a web application for users from multiple domains in a forest. There are a number of benefits to using this delegation type:

  1. Only one entry is required in Active Directory: This is fundamentally different from traditional Kerberos Constrained Delegation because the trust delegation had to be created in each direction. The direction in which the entry is made matters-it should be made for the account involved in hops 2 or greater to the previous hop. In the example below, a user is using a web application that uses Kerberos authentication to retrieve data from a SQL instance. The "second hop" occurs between the web server and SQL instance, so the RBKCD entry must be made from the SQL Server account to the domain account running the application pool for the web application (it is generally good practice to have one application pool for each web application in which Kerberos authentication is implemented):
Diagram of a typical web application, where the web server and SQL instance are on separate machines.
  1. Domain Admin permission is not required to create the delegation entry-only someone with the permission to modify the account
  2. Instead of using the Active Directory Users and Computers applet, a PowerShell script can be executed to create the delegation. PowerShell is also necessary to check the delegation because the resultant object in Active Directory is a NT Security Descriptor, which can only be read, at least in a form that is easy to understand, is via PowerShell

Below is an example of a PowerShell script that can be used to find RBKCD entries for a user, which calls the Get-ADUser function:

Clear
$User = Get-ADUser -AuthType Negotiate -Identity 424926 -Properties SamAccountName, DistinguishedName, ServicePrincipalNames, PrincipalsAllowedToDelegateToAccount
( $env:USERDOMAIN + "\" + $User.SamAccountName )
$User.DistinguishedName
""
"Service Principal Names:"
foreach( $SPN in $User.ServicePrincipalNames ){ ( "`t`t" + $SPN ) }
""
"RBKCD:"
foreach( $RBKCD in $User.PrincipalsAllowedToDelegateToAccount ){ ( "`t`t" + $RBKCD ) }

In order to create a RBKCD entry using PowerShell, objects representing both the SQL Server and application pool account should be created by calling the Get-ADUser function for each, then call the Set-ADUser function to create the entry itself, setting the value of PrincipalsAllowedToDelegateToAccount for the SQL Server account to the application pool account:

Clear
$SQLAccount = Get-ADUser -AuthType Negotiate -Identity 424926 -Properties *
$AppPoolAccount = Get-ADUser -AuthType Negotiate -Identity 200200 -Properties *
Set-ADUser -AuthType Negotiate -Identity $SQLAccount -PrincipalsAllowedToDelegateToAccount $AppPoolAccount

To clear the RBKCD entries for an account, the Set-ADUser function should be used, setting the PrincipalsAllowedToDelegateToAccount to NULL ($null):

Clear
$SQLAccount = Get-ADUser -AuthType Negotiate -Identity 424926 -Properties *
$AppPoolAccount = Get-ADUser -AuthType Negotiate -Identity 200200 -Properties *
Set-ADUser -AuthType Negotiate -Identity $SQLAccount -PrincipalsAllowedToDelegateToAccount $null

RBKCD entries do not appear in the Active Directory Users and Computers applet/Delegations tab. The only place, other than using PowerShell, where the entries could be viewed is ADSI Edit:

Example of what a Resource-Based Kerberos Constrained Delegation entry looks like in ADSI Edit.

Unfortunately, an error will be returned if an attempt is made to open the setting because the data type of RBKCD entries is NT Security Descriptor:

ADSI Edit error when an attempt is made to open the setting because the data type for RBKCD entries is NT Security Descriptor.
Next Steps
  • Keep in mind that Resource-Based Kerberos Constrained Delegation should only be used when there is a requirement to allow users from multiple domains to use Kerberos authentication. If there is only one domain, or if there is a web application that will be used by users from only one domain, then traditional Kerberos Constrained Delegation should still be used
  • In order to use the PowerShell cmdlets for Active Directory, follow the instructions on this site to install the necessary components
  • Find out more about the double hop scenario in Kerberos authentication
  • Read the overview on Kerberos Constrained Delegation, including Resource-Based Delegations
  • Review the Get-ADUser and Set-ADUser functions in PowerShell
  • Read more Security tips


Last Updated: 2020-07-14


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Encrypting passwords for use with Python and SQL Server

Is your SQL Server environment ready for GDPR?

Understanding SQL Server fixed database roles





get free sql tips
agree to terms


Learn more about SQL Server tools