Extracting object ownership information from Active Directory into SQL

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm

There could be many reasons why tracking Active Directory objects ownership might be important: audit requirements, identity management processes enforcement, just to name a few.

This blog will outline an approach of using SQL Server Integration Services (SSIS) for exporting ownership information from AD into a relational format (SQL table), for the purposes of subsequent report generation.

This solution relies on SSIS Active Directory Source Component.

The approach

A combination of SSIS transformation components will be utilized to accomplish this task

Ownership of an AD object is stored within an object itself in the binary attribute called nTSecurityDescriptor.

nTSecurityDescriptor can be parsed using .NET libraries found in the System.DirectoryServices namespace, hence we will use a script component to extract the owner.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (!Row.nTSecurityDescriptor.IsNull)
        {
            byte[] sd = Row.nTSecurityDescriptor.GetBlobData(0, (int)Row.nTSecurityDescriptor.Length);
            var activeDirectorySecurity = new ActiveDirectorySecurity();
            activeDirectorySecurity.SetSecurityDescriptorBinaryForm(sd, AccessControlSections.Owner);
            Row.Owner = activeDirectorySecurity.GetOwner((typeof(SecurityIdentifier))).Value;
        }
    }

The script will inject a new column into the data flow task which will contain the objectSID of the owner of an object in question.

Once the objectSID of the owner is determined it is just a matter of replacing it with a another attribute like sAMAccountName. This can be accomplished by performing a merge join on the OwnerSID of an object and the objectSID of the object which is the owner of the object in question.

Sample output

Caveats

• If an account is created with a user who is part of a Domain Admins group, the owner of the object will be set to Domain Admins, and not to the actual use who created it. Yet another reason to keep Domain Admins group small.
• If an account is created via a System account (ObjectSID S-1-5-18) the merge join will not find a match, so the NULL value will be found in the onwersAMAccountName. The reason for this is that there is not actual object SYSTEM in the AD, at least it is not being imported when querying for user objects. You will need to deal with such accounts as a special case.

Complete SSIS project is found here