February 10, 2013

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




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





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




                '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.Name = name


                                ListofusersoutputBuffer.MemberOf = group



                        End If






            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()



        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("=")





        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!

No comments:

Post a Comment