Extracting Users from AD using SSIS

I’ve recently been working on a project which required KPI level security alongside the traditional row level security secured at a geography level. This would limit what financial data a user could see within a cube, without having to create multiple cubes or use perspectives (which would not actually secure the data).

To achieve this, I needed to populate a set of ‘KPI User/Role’ tables stored in Master Data Services (MDS) with a list of users who were stored in a particular AD group. I would need these tables updated on a regularly basis to grant/revoke access. We could then use these names along with the USERNAME() function in DAX to filter.

The Solution

One method to solve my problem would be by using SSIS. The package could be setup to run as part of a SQL Agent Job, either by a schedule or on demand. My list of users were stored in an AD group called LH_FIN.

To start with you will need to truncate and clear your MDS staging tables that you are about to populate. You can then use the data flow to process the majority of the logic, by creating a script component task. The purpose of this is to loop through Active Directory and pick up the user details that belong to the specified AD Group or set of AD groups if dealing with multiple roles.

A number of variables are defined which the task uses to complete the lookup.

strLDAP – the LDAP directory on which to perform the lookup
strDomain – the domain on which the AD group(s) belong
strADPrefix – the AD group prefix from which to return user information about
strADParent – the parent group which contains the AD groups which you are looking up (may not need to be used if only looking up a single AD group)

To extract users from multiple groups, make sure the prefix stored in the variable strADPrefix covers both groups. Once the rows are extracted it would then be a case of using SSIS to split the data accordingly on the AD Group Name.

The following code can be used in the script:

The first section sets up the objects required to interrogate the directory, and the fields we expect to return from the accounts – the most important of which is memberof which is used to check versus our AD Prefix. It also filters out items such as service accounts and disabled accounts to speed up the interrogation process.

    Public Overrides Sub CreateNewOutputRows()

        Dim domain As String = Variables.strDomain
        Dim searchRoot As New DirectoryEntry(Variables.strLDAP, Nothing, 
                                             Nothing, AuthenticationTypes.Secure)
        Dim dirSearch As New DirectorySearcher(searchRoot)
        dirSearch.SearchScope = SearchScope.Subtree
        'LogonName, GroupsUserBelongsTo, Department, JobTitle, MailAddress, DisplayName
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.PropertiesToLoad.Add("memberof")
        dirSearch.PropertiesToLoad.Add("department")
        dirSearch.PropertiesToLoad.Add("title")
        dirSearch.PropertiesToLoad.Add("mail")
        dirSearch.PropertiesToLoad.Add("displayname")
        'filter to user objects
        dirSearch.Filter = "(objectCategory=person)" 
        'filter to user objects
        dirSearch.Filter = "(objectClass=user)" 
        'filter out disabled accounts
        dirSearch.Filter = "(!userAccountControl:1.2.840.113556.1.4.803:=2)" 
        'filter out password never expires accounts, i.e. service accounts
        dirSearch.Filter = "(!userAccountControl:1.2.840.113556.1.4.803:=65536)" 
        'sets chunk size for retrieving items
        dirSearch.PageSize = 1000

The next section of code performs the search, and for any LDAP objects it finds within the filter set, returns the properties requested. These properties are then stored in key/value pairs.

        Dim props As ResultPropertyCollection
        Dim values As ResultPropertyValueCollection
        Dim key As String
        Dim userAccountName As String
        Dim departmentHome As String
        Dim jobtitle As String
        Dim GroupName As String
        Dim email As String
        Dim displayName As String
        Dim groups As New ArrayList

        Using searchRoot
        'Return all LDAP objects, LDAP://acl/CN=Tristan Robinson,OU=Employees,DC=ACL,DC=local
        'CN = Common Name, OU = Organisational Unit, DC = Domain Component
            Using results As SearchResultCollection = dirSearch.FindAll()
                For Each result As SearchResult In results
                    'For each object return properties, i.e. displayname, memberof, etc
                    props = result.Properties
                    For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        'For each property, inspect the property and record its value
                        'Logon Name
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            userAccountName = CType(values.Item(0), String)
                        End If
                        'Department
                        If key = "department" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            departmentHome = CType(values.Item(0), String)
                        End If
                        'Job Title
                        If key = "title" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            jobtitle = CType(values.Item(0), String)
                        End If
                        'E-Mail
                        If key = "mail" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            email = CType(values.Item(0), String)
                        End If
                        'Display Name
                        If key = "displayname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            displayName = CType(values.Item(0), String)
                        End If
                        'Groups User Belongs To (array/collection)
                        If key = "memberof" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groups = GetGroups(values)
                        End If
                    Next

The final section filters the data into the output buffer if from the array list we’ve extracted above, we have matching strings from our original AD Prefix variable. It will then reset, and loop round for the next account.

                    'Export user details to buffer if it passes the logical test
                    For Each item As String In groups
                        'Avoids computer accounts, i.e. ending with $
                        If userAccountName.EndsWith("$") = False 
                        And item.ToString.StartsWith(Variables.strADPrefix) 
                        Then 'And item.ToString <> (Variables.strADParent) 
                            Output0Buffer.AddRow()
                            If String.IsNullOrEmpty(userAccountName) Then
                                Output0Buffer.UserAccountName_IsNull = True
                            Else
                                Output0Buffer.UserAccountName = userAccountName
                            End If
                            If String.IsNullOrEmpty(domain) Then
                                Output0Buffer.Domain_IsNull = True
                            Else
                                Output0Buffer.Domain = domain
                            End If
                            If String.IsNullOrEmpty(item.ToString) Then
                                Output0Buffer.GroupName_IsNull = True
                            Else
                                Output0Buffer.GroupName = item.ToString
                            End If
                            If String.IsNullOrEmpty(jobtitle) Then
                                Output0Buffer.JobTitle_IsNull = True
                            Else
                                Output0Buffer.JobTitle = jobtitle
                            End If
                            If String.IsNullOrEmpty(email) Then
                                Output0Buffer.Email_IsNull = True
                            Else
                                Output0Buffer.Email = email
                            End If
                            If String.IsNullOrEmpty(displayName) Then
                                Output0Buffer.DisplayName_IsNull = True
                            Else
                                Output0Buffer.DisplayName = displayName
                            End If
                        End If
                    Next
                    groups.Clear()
                    userAccountName = ""
                    departmentHome = ""
                    jobtitle = ""
                    GroupName = ""
                    email = ""
                    displayName = ""                    
                Next
            End Using
        End Using
    End Sub

I also required a function to split the list of groups a user belonged to and store them in another array list.

    Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList
        Dim valueList As ArrayList = New ArrayList()
        For Each Item As Object In values
            Dim memberof As String = Item.ToString()
            Dim pairs As String() = memberof.Split(",".ToCharArray)
            Dim group As String() = pairs(0).Split("=".ToCharArray)
            valueList.Add(group(1))
        Next
        Return valueList
    End Function

End Class

Once a list of users has been extracted, you will need to do a lookup against the existing list and only stage those that are new. This can be achieved through a simple lookup component . You can then move the rows into the MDS staging table ready for the load into MDS.

After the data flow has processed successfully, the next stage is to sweep the records into MDS using one of the built in stored procedures in the product. The results of which can be seen in the MDS Import View on the MDS site.

The following entities can then be used to power the DAX:

User (Username, Team, AD Login – populated from the script above)
Role (Role – populated manually)
User Role (User, Role – joins users to roles, populated manually)
KPI (KPI – populated manually)
KPI Role (Role, KPI – joins roles to KPIs, populated manually)

These are then processed to the DW by ETL, and a UserKPI view is written across the tables to provide an AD Login to KPI pair.

For filtering the measures in the cube, you can then apply the following piece of DAX to the original measure – Gross Sales in the example below:

Gross Sales:=
IF(
    CONTAINS(
        CALCULATETABLE( 'UserKPI', UserKPI[KPIName] = "Gross Sales"),
        UserKPI[ADLogin], USERNAME()
    ),
    [Gross Sales (ACTUAL)]
    ,
    BLANK ()
)

This concludes this blog post – hopefully this will be useful for anyone that wants to extract users from AD to control access / security within an application. Data could also be loaded direct into DB tables rather than MDS if required.