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