As you are probably aware of, configuring constrained delegation requires some steps that involve domain admin permissions.
In many larger organizations where there is a clear separation of duties this is can be problem since the engineers and developers need to specify what type of delegation need to be configured, and domain admins need to execute on that, not knowing what this is for and in many cases not knowing how and why Kerberos Constrained Delegation (KCD) operates.
Anyone that has tried to work with KCD knows what I am referring to.
This post is not about how to configure KCD but rather how to give a group of non-domain admin persons the ability to setup KCD without giving them other unnecessary domain-wide permissions.
This can be accomplished in a few steps, using an Organizational Unit (OU), a group for membership and a Global Policy applied to the OU.
Below is a description of how to achieve that.
For the sake of this article let us suppose that we have a group of DBAs that need to enable KCD for their BI developers so they can use row level security on their data sources (SSAS or SQL Server databases).
Let’s start by creating an OU where the servers we need to delegate can be placed. I name this OU SQL Server Data Sources
In that OU, let’s place the servers for which we need to be able to configure Kerberos Constrained Delegation. In my example I am placing my two database servers, SQL1 and SHP in the newly created OU.
The next step is to create a Security Group in which the users that need to be able to configure KCD for those servers will become member. I call this group DBA and it has one member called regbac.
Now we need to find the exact security settings that will allow members of this DBA group to configure Kerberos Constrained Delegation. For that purpose we need to view the advanced security properties of the newly created OU.
Properties à Security à Advanced
Next step is to Add a principal by clicking on Add and in the “select a principal” link, choosing the DBA security group.
The first thing to do now is to give this DBA group permissions to modify some properties on all the computer objects that we will put in the OU (our data sources).
Now, switch the “Applies to” setting to “All descendant objects” (This means all objects within the OU) and give DBA permissions to Write all properties. Click OK.
Now I need to add some special permissions to computer objects, so I click Add again. Once again, I’ll select the DBA group, then I need to switch to Descendant Computer objects. I click Write and then scroll down until I see Validated write to service principal name. I’ll click the box to enable it, and then OK, OK, and OK.
The end result looks like below :
2 permissions for DBA group,
- All descendants objects : Write all properties
- Descendant computer objects : Validate write to Service Principal Name
Now that we have managed the permission part, we need to create a group policy to enable thrust for delegation. This requires that I modify the Group Policy for the OU.
In group policy management, create a new Group Policy called SQLDBAforKCD.
Choose Edit (right-click), this brings up the Group Policy Management editor. The policy I need to change is under the Computer Configuration. I’ll expand Policies, Window Settings, Security Settings, and Local Policies. I’m looking for User Rights Assignments. Now I see a list of all the policies. I’m looking for Enable computer and user accounts to be trusted for delegation.
Add the DBA group to this policy.
Now back in Group Policy Manager, let’s find the OU. I’ll locate the SQLDBAforKCD policy and drag it to the OU. Yes, I do want to link it there, and I’ll also enforce it. Now the members of the DBA group have rights to set up Kerberos delegation.
Let’s test it by logging onto the server, running Active Directory Users and Computers as regbac and validate that the delegation is active for that user,
by trying to set or alter an active delegation:
Trying to do the same with an object placed in other OU doesn’t work, thus proving that we have successfully enabled KCD on that OU only.
In a next post I will suggest a OU design for Kerberos and BI.
If you missed my talk about Kerberos for BI please come to a SQL Saturday near you….
Special thanks to Niels for reading the article and suggesting edits before publishing it!