Import Active Directory (AD) Groups and user names via SSIS into SQL server table

Before getting into the technical details, I will explain the need for this.

1. If you manage SQL server Security via AD Groups due to high volume of business users then you would have already hit the problem and looking for solution to import the mapping details of groups vs users
2. If you are using SSRS suit for the reporting needs and if you have large user base then AD group based security is the best way to segregate user access. But once again to answer who has what rights you need this mapping information.

Step 1: Create the table structure necessary to import the SSIS Groups and Names in two tables

Table 1: ActiveDirectoryUserGroups – This tables gives you the relationship between users and their group name they belongs to

CREATE TABLE [dbo].[ActiveDirectoryUserGroups](

[id] [int] IDENTITY(1,1) NOT NULL,

[GroupName] [varchar](1000) NULL,

[username] [varchar](1000) NULL

)  

GO

Table 2:ActiveDirectoryGroupRelation – This table gives you the relationship between Groups

CREATE TABLE [dbo].[ActiveDirectoryGroupRelation](

[id] [int] IDENTITY(1,1) NOT NULL,

[GroupName] [varchar](1000) NULL,

[ParentGroupName] [varchar](1000) NULL

) 

GO

 

Step2: Create the necessary connection to your database where the above table exist

Step 3: Create SQL task to clear the data before load.

(Alternatively, you can import the changes alone via lookup or in other words use the incremental load approach . But that is not the point of this blog so to keep this simple I will just truncate the records before load)

Step 4: Create a data flow task in the control flow to fetch mapping between groups

(Name it suitably  E.g “DFL_ActiveDirectory_Group_relation_load”)

Create Three Variables in the data flow task scope

Note: Replace Domain with your mail Domain. E.g. “Pinnacle-Int” and “Extension” with your Domain extension E.g. “Com”

Create a “Source Script Component”. as shown below.

Make sure to declare all three variables as read only variables as shown below

Choose your choice of scripting language

 

Set the outputs correctly

Paste the below code in the Scripter

Public Overrides Sub CreateNewOutputRows()

        Dim de As New DirectoryEntry

        Dim searcher As New DirectorySearcher

        Dim search_result As SearchResultCollection

        Dim result As SearchResult

        Dim props As ResultPropertyCollection

        Dim MemberOfList As StringBuilder

        Dim values As ResultPropertyValueCollection

        Dim name As String

        Dim groups As ArrayList

        Using (de)

            de.Path = Me.ReadOnlyVariables("Path").Value.ToString

            Using (searcher)

                searcher.SearchRoot = de

                searcher.Filter = Me.ReadOnlyVariables("Filter").Value.ToString

                searcher.SearchScope = SearchScope.Subtree

                ' Retrive ActiveDirectory column list

                searcher.PropertiesToLoad.Add("samaccountname")

                searcher.PropertiesToLoad.Add("memberof")

                'sort the result

                searcher.Sort = New SortOption("samaccountname", SortDirection.Ascending)

                ' you can set your own limits

                searcher.PageSize = Me.ReadOnlyVariables("MaxRecord").Value

                'Retrieve the results from Active Directory

                search_result = searcher.FindAll()

                MemberOfList = New StringBuilder 'Members/Groups list.

                Dim entry As DictionaryEntry

                For Each result In search_result

                    props = result.Properties

                    For Each entry In props

                        values = entry.Value

                        If entry.Key.ToString = "samaccountname" Then

                            name = GetSingleValue(values)

                        End If

                        If entry.Key.ToString = "memberof" Then

                            groups = GetGroups(values)

                            For Each group In groups

                                ListofusersoutputBuffer.AddRow()

                                ListofusersoutputBuffer.Name = name

                                ListofusersoutputBuffer.MemberOf = group

                            Next

                        End If

                    Next

                Next

            End Using

        End Using

    End Sub

    Private Function GetSingleValue(ByVal values As ResultPropertyValueCollection) As String

        For Each val As Object In values

            Return val.ToString()

        Next

        Return Nothing

    End Function

    Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList

        Dim valueList As New ArrayList()

        For Each val As Object In values

            Dim memberof As String = val.ToString()

            Dim pairs() As String = memberof.Split(",")

            Dim group() As String = pairs(0).Split("=")

            valueList.Add(group(1))

        Next

        Return valueList

    End Function

Connect the Appropriate Destination as shown below

Do the Appropriate mappings for ActiveDirectoryGroupRelation

Test whether the load is successful

Step 5: Create a data flow task in the control flow to fetch mapping between user and groups

(name it suitably E.g “DFL_ActiveDirectory_user_group_relation_load”)

Follow the same similar steps as above. Only difference is load them with below Variables.

Create Three Variables in the data flow task scope

Note: Replace Domain with your mail Domain name. E.g. “Pinnacle-Int”  and “Extension” with your Domain extension E.g. “Com”

Also make sure to load “ActiveDirectoryUserGroups” table this time

Your Final solution should look like below

Hope that is helpful!