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

)  

 

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

image 

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

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
2jpmvrye

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

image 
Set the outputs correctly
image
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
image
Do the Appropriate mappings for ActiveDirectoryGroupRelation
image
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
m4fmfsat
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
image
Hope that is helpful!