3/24/2011

Microsoft SSIS Package to Query Active Directory into SQL Server Table

Now here's an excellent reference on Microsoft SSIS Package to Query Active Directory into SQL Server Table.

It requires some tweaks but just about got it working today.

http://www.mssqltips.com/tip.asp?tip=1657

Our Active Dirctory stores the user info as well as all the Cost Centers associated with a user. So when a user goes to run a report, we have a hidden variable @User in the SSRS report, we run a query against our local SQL-Server table to see what cost centers are associated with the user, and display just those cost centers in the parameter drop down. Pretty groovy!!

So we use Active Directory Groups to restrict folder and report access in SSRS and will implement the cost center security on the data so the user can only see their stuff.

Here's what our package looks like:











And now the Script:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.DirectoryServices
Imports System.Collections

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        ' TODO: Replace "vs" with your domain name
        Dim domain As String = "enter_your_domain_here"
        ' TODO: Replace "LDAP://dc=vs,dc=local" with your dc
        Dim searchRoot As New DirectoryEntry("LDAP://168.123.45.67/DC=enter_your_domain_here,DC=local", Nothing, Nothing, AuthenticationTypes.Secure)

        Dim dirSearch As New DirectorySearcher(searchRoot)
        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 ArrayList

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.PropertiesToLoad.Add("memberof")
        dirSearch.PropertiesToLoad.Add("department")
        dirSearch.PropertiesToLoad.Add("title")
        dirSearch.PropertiesToLoad.Add("mail")
        dirSearch.PropertiesToLoad.Add("displayname")
        dirSearch.Filter = "(objectclass=person)"
        'dirSearch.Filter = "(objectCategory=group)"
        dirSearch.PageSize = 1000

        Using searchRoot
            Using results As SearchResultCollection = dirSearch.FindAll()
                For Each result As SearchResult In results
                    props = result.Properties
                    For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            userAccountName = CType(values.Item(0), String)
                        End If
                        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
                        'email
                        If key = "mail" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            email = CType(values.Item(0), String)
                        End If
                        'displayName
                        If key = "displayname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            displayName = CType(values.Item(0), String)
                        End If
                        'set the groups here (its in an array/collection
                        If key = "memberof" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groups = GetGroups(values)
                        End If
                    Next
                    'export all fields here
                    For Each item As String In groups
                        If userAccountName.EndsWith("$") = False Then
                            OutputBuffer.AddRow()
                            OutputBuffer.UserAccountName = userAccountName
                            OutputBuffer.Domain = domain
                            OutputBuffer.GroupName = item.ToString
                            'department (4 digits)
                            If Len(item.ToString) >= 5 Then
                                If item.ToString.Contains("-") Then
                                    If IsNumeric(Left(item.ToString, 4)) Then
                                        OutputBuffer.CostCenter = Left(item.ToString, 4)
                                    End If
                                End If
                                OutputBuffer.DepartmentHome = departmentHome
                                OutputBuffer.JobTitle = jobtitle
                                OutputBuffer.Email = email
                                OutputBuffer.DisplayName = displayName
                            End If
                        End If
                    Next
                Next
            End Using
        End Using

    End Sub

    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



And the other script:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.DirectoryServices
Imports System.Collections

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        ' TODO: Replace "vs" with your domain name
        Dim domain As String = "enter_your_domain_here"
        ' TODO: Replace "LDAP://dc=vs,dc=local" with your dc
        Dim searchRoot As New DirectoryEntry("LDAP://168.123.45.67/DC=enter_your_domain_here,DC=local", Nothing, Nothing, AuthenticationTypes.Secure)

        Dim dirSearch As New DirectorySearcher(searchRoot)
        Dim props As ResultPropertyCollection
        Dim values As ResultPropertyValueCollection
        Dim key As String
        Dim groupAccountName As String
        'Dim departmentHome As String
        'Dim jobtitle As String
        Dim groupDept As String
        Dim groupDeptName As String
        'Dim email As String
        'Dim displayName As String
        Dim groups As ArrayList

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.PropertiesToLoad.Add("memberof")
        'dirSearch.PropertiesToLoad.Add("department")
        'dirSearch.PropertiesToLoad.Add("title")
        'dirSearch.PropertiesToLoad.Add("mail")
        'dirSearch.PropertiesToLoad.Add("displayname")
        'dirSearch.Filter = "(objectclass=person)"
        dirSearch.Filter = "(objectCategory=group)"
        dirSearch.PageSize = 1000

        Using searchRoot
            Using results As SearchResultCollection = dirSearch.FindAll()
                For Each result As SearchResult In results
                    props = result.Properties
                    For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groupAccountName = CType(values.Item(0), String)
                        End If
                        '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
                        ''email
                        'If key = "mail" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    email = CType(values.Item(0), String)
                        'End If
                        ''displayName
                        'If key = "displayname" Then
                        '    values = CType(entry.Value, ResultPropertyValueCollection)
                        '    displayName = CType(values.Item(0), String)
                        'End If
                        'set the groups here (its in an array/collection
                        If key = "memberof" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            groups = GetGroups(values)
                        End If
                    Next
                    'export all fields here
                    For Each item As String In groups
                        'If userAccountName.EndsWith("$") = False Then
                        OutputBuffer.AddRow()
                        'OutputBuffer.UserAccountName = userAccountName
                        OutputBuffer.Domain = domain
                        OutputBuffer.GroupAccountName = groupAccountName
                        'OutputBuffer.GroupDept = item.ToString
                        'Groups
                        OutputBuffer.GroupDeptName = item.ToString
                        If Len(item.ToString) >= 5 Then
                            If item.ToString.Contains("-") Then
                                If IsNumeric(Left(item.ToString, 4)) Then
                                    OutputBuffer.GroupDept = Left(item.ToString, 4)
                                End If
                            End If
                            'OutputBuffer.DepartmentHome = departmentHome
                            'OutputBuffer.JobTitle = jobtitle
                            'OutputBuffer.Email = email
                            'OutputBuffer.DisplayName = displayName
                        End If
                    Next
                Next
            End Using
        End Using

    End Sub

    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

It's been working for almost a year now!

We can now query Active Directory from a SQL-Server table...

Not too shabby!

CREATE TABLE [dbo].[stg_UserGroupList](
 [Domain] [nvarchar](50) NOT NULL,
 [AccountName] [nvarchar](50) NOT NULL,
 [Group] [nvarchar](50) NOT NULL,
 [DomainUser]  AS (([Domain]+'\')+[AccountName]),
 [FK_DimUser] [int] NULL,
 [FK_DimRole] [int] NULL
)

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder