Get Active Directory Users and Groups with SQL Server Integration Services

By:   |   Comments (44)   |   Related: > Integration Services Security


Problem

We have a requirement to implement security in our data warehouse to limit what data a user can see on a report.  As an initial step we have created tables for users and roles; we also have a user role table where we specify the role(s) that a user is assigned.   We would like to update the user, role, and user role tables automatically from Active Directory.  Ideally we'd like an SSIS package that we could schedule and/or run on demand to take care of the update.  Can you provide an example of how to do this?

Solution

An SSIS package is a convenient choice to synchronize your security tables with Active Directory.  From a database standpoint, let's assume your security tables are as shown in the following schema diagram:

user role

The DimUser table has a row for each user that is allowed to see any data in the warehouse.  The DimRole table contains our list of roles that a user may be assigned; there is a one-to-one relationship between a role and an Active Directory group.  The DimUserRole table contains the users and their roles via the foreign key relationships to the DimUser and DimRole tables.  You query the DimUserRole table to determine what role(s) the user belongs to then use that to filter the data that the user can see.  If you're familiar with .NET programming you might ask why not just call the method WindowsPrincipal.IsInRole() to check whether a user is in a particular role?  While you certainly could do that, there are a number of reasons why having the users and roles in tables is beneficial:

  • You can create table-valued functions (TVF) that you can join with other tables to filter based on role and/or other rules; I'll give an example in this tip.
  • Sometimes the "current user" is different than the the "calling user"; e.g. in SQL Server Reporting Services you may configure a report to run as a particular user but filter the data based on the global variable User!UserID (the calling user).
  • You often come up with additional user or role data that isn't in Active Directory and it's not a simple task to extend the Active Directory schema; you can easily incorporate this data into your own SQL Server tables.  For instance users in certain roles can see all Key Performance Indicators (KPIs) for all offices; users in other roles can only see the company-wide KPI (no office-level detail).

As shown above the schema is very simple.  Let's continue on with the details on how to retrieve users and their groups from Active Directory then implement an SSIS package to perform the update.

.NET Code to Query Active Directory

In order to retrieve the list of users and their groups from Active Directory, we will need to write some .NET code.  The following method shows the steps to write out every user and their group memberships to a tab delimited file:

get user groups

The main points about the above code are:

  • Step 1 sets up the parameters for the Active Directory search.  DirectoryEntry is a class in the System.DirectoryServices namespace that you use to specify where in Active Directory to begin the search.  In my case I used "LDAP://dc=vs,dc=local" as the path variable value to get all users in the domain since my domain is vs.local.  DirectorySearcher is used to perform the actual search; it is also in the System.DirectoryServices namespace.  The SearchScope property is set to search objects and their hierarchies.  You specify the attributes you want to retrieve by calling the PropertiesToLoad Add method.  The Filter property is set to return any object that represents a person.  The PageSize property sets the chunk size for retrieving items from the Active Directory.  Without specifying a PageSize you will only get the first 1,000 items.
  • Step 2 executes the search by calling the FindAll method on the DirectorySearcher object, which returns a collection of objects.
  • Step 3 creates the flat file to output the results.
  • Step 4 iterates through the result collection.  Each item is a collection itself.
  • Step 5 iterates through each item collection pulling out either a single value (e.g. samaccountname) or multiple values (e.g. memberof).
  • The samaccountname attribute is the user's login name.
  • The memberof attribute is a multi-valued collection which contains each Active Directory group that the user is a member of.
  • The above code is part of a class called User contained in a class library called ADHelper.DLL.  By packaging the code in a class library rather than embedding it in the SSIS package, we can call it from the SSIS package as well as any .NET code.

Note that there are two requirements for deploying the above code so that it can be called from an SSIS package:

  • ADHelper.DLL must be deployed to the Global Assembly Cache (GAC).  You can use the GACUTIL utility to do this or simply drag and drop the DLL to the \Windows\Assembly folder.
  • ADHelper.DLL must be copied to the folder \Program Files\Microsoft SQL Server\90\SDK\Assemblies

SSIS Package Control Flow

We will use the following SSIS package control flow to synchronize our security tables with Active Directory:

truncate

Extract Users and Group Memberships from AD

Extract Users and Group Memberships from AD is a Script task that retrieves users and their group memberships from Active Directory and writes out a tab delimited file.  The Script task  contains the following code:

Dim user As New ADHelper.User
Dim filename As String
filename = Dts.Variables("ADUserGroupsFileName").Value.ToString()
user.GetUserGroups("VS", "LDAP://dc=vs,dc=local", filename)
Dts.TaskResult = Dts.Results.Success

ADUserGroupFileName is a package variable that holds the full path to the tab delimited file where the results are stored.  The code in the Script task is minimal since the GetUserGroups method contains the majority of the code.  You need to add a reference to the ADHelper DLL in the Script task.

Truncate stg_UserGroupList

Truncate stg_UserGroupList is an Execute SQL task that truncates the stg_UserGroupList table used during processing.  The stg_UserGroupList table is created with the following script:

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
)

The Domain column is populated by the value of the domain parameter passed in to the GetUserGroups method described above.  The AccountName and Group columns are set from the Active Directory attributes samaccountname and memberof.  The DomainUser computed column concatenates the Domain and AccountName columns in the DOMAIN\ACCOUNT format.  The FK_DimUser and FK_DimRole columns will be set to the primary key values of the respective tables.

Import Users and Groups into stg_UserGroupList

Import Users and Groups into stg_UserGroupList is a Data Flow task that loads the stg_UserGroupList table from the tab delimited file created in the initial Script task.

Synchronize DimUser and DimUserRole

Synchronize DimUser and DimUserRole is an Execute SQL task that calls the stored procedure that updates our security tables from the stg_UserGroupList table.  The update is essentially a merge operation that inserts rows for any new users and their group memberships, and deletes users and their group memberships that are no longer in the Active Directory.  The stored procedure is shown below:

-- Step 1
UPDATE  dbo.stg_UserGroupList 
SET FK_DimUser = u.DimUserID
FROM dbo.stg_UserGroupList f 
JOIN dbo.DimUser u ON u.UserName = f.DomainUser
 
UPDATE  dbo.stg_UserGroupList 
SET FK_DimRole = r.DimRoleID,
 FK_DimPlan = r.FK_DimPlan
FROM dbo.stg_UserGroupList f 
JOIN dbo.DimRole r ON r.Role = f.[Group]
 
-- Step 2
INSERT INTO dbo.DimUser (UserName)
SELECT DISTINCT DomainUser
FROM dbo.stg_UserGroupList
WHERE FK_DimRole IS NOT NULL
AND FK_DimUser IS NULL
 
-- Step 3
UPDATE  dbo.stg_UserGroupList 
SET FK_DimUser = u.DimUserID
FROM dbo.stg_UserGroupList f 
JOIN dbo.DimUser u ON u.UserName = f.DomainUser
 
-- Step 4
INSERT INTO dbo.DimUserRole (FK_DimUser, FK_DimRole)
SELECT f.FK_DimUser, f.FK_DimRole
FROM dbo.stg_UserGroupList f
LEFT JOIN dbo.DimUserRole r ON r.FK_DimUser = f.FK_DimUser
AND r.FK_DimRole = f.FK_DimRole
WHERE f.FK_DimUser IS NOT NULL
AND f.FK_DimRole IS NOT NULL
AND r.DimUserRoleID IS NULL

-- Step 5
DELETE FROM dbo.DimUser
WHERE DomainUser NOT IN (
 SELECT DISTINCT DomainUser
 FROM dbo.stg_UserGroupList
)
 
-- Step 6
DELETE FROM dbo.DimUserRole
FROM dbo.DimUserRole r
LEFT JOIN dbo.stg_UserGroupList f ON f.FK_DimUser = r.FK_DimUser 
AND f.FK_DimRole = r.FK_DimRole
WHERE f.AccountName IS NULL

The main points about the above stored procedure are:

  • Step 1 looks up the primary key values for the DimUser and DimRole tables and saves them in the FK_DimUser and FK_DimRole columns.  When the FK_DimUser column is NULL we have a new user; when the column isn't NULL we have an existing user. 
  • Step 2 inserts any new users into the DimUser table.  Note that we only insert new users if they are in a role in the DimRole table.
  • Step 3  looks up the primary key value for the DimUser table and saves it in the FK_DimUser column.  This is done to get the primary key of any users that were added in Step 2.
  • Step 4 inserts any new user role assignments into the DimUserRole table.  Note that a LEFT JOIN is used because we only want to insert rows that are not already in the table.
  • Step 5 deletes any rows from the DimUser table that have been removed from Active Directory; i.e. any user not in the staging table.
  • Step 6 deletes any rows from the DimUserRole table where the user is no longer in the Active Directory group.

Implementing KPI Security

Let's finish up by implementing a common security requirement.  Assume we want to only allow users in a certain role to see KPI values in detail.  All roles can see the KPI values for the company, but only users in a certain role can see KPIs at the office level of detail.  We can implement this security by creating a table-valued function that will take the user as a parameter, lookup his role, then return the list of KPI values that the user is allowed to see.

We'll use the following schema to implement the KPI values:

dim office

The FK_DimOffice column in the FactKPIValue table will have a value of 1 when the row is the KPI value for the entire company.

We will use the following table-valued function to implement the KPI security:

CREATE FUNCTION [dbo].[udf_get_filtered_kpi]
(
  @username NVARCHAR(256)
)
RETURNS @kpi_t TABLE (
  FactKPIID   INT
, FK_DimKPI  INT
, FK_DimOffice  INT
, KPIValue  DECIMAL(18,2)
)
AS
BEGIN
DECLARE @UserID  INT
DECLARE @ShowDetails BIT
SELECT @UserID = DimUserID
FROM dbo.DimUser
WHERE DomainUser = @username
IF @UserID IS NULL
    RETURN
 
IF EXISTS (
   SELECT * 
   FROM dbo.DimUserRole u
   JOIN dbo.DImRole r ON r.DimRoleID = u.FK_DimRole
   WHERE u.FK_DimUser = @UserID
   AND r.[Role] = 'Senior Leadership'
) SET @ShowDetails = 1 ELSE SET @ShowDetails = 0
 
-- everyone gets to see the kpi values for the company
INSERT INTO @kpi_t
  (FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue)
SELECT FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue
FROM dbo.FactKPIValue  
WHERE FK_DimOffice = 1
-- 'Senior Leadership' role can see the detailed kpis
IF @ShowDetails = 1
   INSERT INTO @kpi_t
     (FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue)
   SELECT FactKPIID, FK_DimKPI, FK_DimOffice, KPIValue
   FROM dbo.FactKPIValue  
   WHERE FK_DimOffice > 1
RETURN
END

Main points about the above function:

  • The @username parameter must be formatted as DOMAIN\USERNAME.  This is the format of the User!UserID global variable in a SQL Server Reporting Services report.
  • Every user can see the company KPI values.
  • If the user is in the Senior Leadership role then he can see the KPI details at the office level.

Here is the sample output from executing the function with a user that is in the Senior Leadership role and another user that isn't in the role:

select * from dbo. udf_get_filtered_kpi ('VS\jones')
select * from dbo. udf_get_filtered_kpi ('VS\smith')
results
Next Steps
  • SSIS can be used for all sorts of tasks as this tip has shown.
  • Take a look at the sample code here to experiment on your own.  Here is a revised version of the code.
  • Keep in mind that the Script task can execute code that is packaged in an external DLL in addition to executing VB.NET code embedded in the task.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, February 12, 2013 - 5:22:00 AM - Ray Barley Back To Top (22063)

I just remembered what the FK_DimPlan column was used for.  This tip was taken from a project I did for a health care claims processing system.  FK_DimPlan is a foreign key to a table that specifies the health care plan that the user is associated with. FK_DimPlan is used in the data warehouse as a filter so that a user can't see data for another plan.  

Monday, February 11, 2013 - 9:05:23 PM - Ray Barley Back To Top (22052)

I don't remember anything about the FK_DimPlan column.

Monday, February 11, 2013 - 5:36:36 PM - Shilpa Back To Top (22049)

Thansk so much for the contents.

I have a question regarding the stored proc thtough. The DomainUser columns contains the Domain+'\'+Username. What is the FK_DimPlan column which exists in the DimRole table?

Shilpa

Sunday, February 10, 2013 - 2:24:21 PM - Raymond Barley Back To Top (22038)

Sorry it's been a long time since I did this (4+ years); I copied the stored proc you asked for below.  Not sure if I manually populated the DimRole table or not.  Since there is nothing in the stored procs below that inserts into DimRole I have to assume that I manually populated it.  I think the idea was that you manually populate DimRole with just the roles (i.e. AD groups) that you care about.

 

There is a database backup in the download; the file is mssqltips_1657.bak.  

 

Here are both of the stored procs that were used:

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[usp_edb_get_user_role_cross_join]

AS

BEGIN

SET NOCOUNT ON;

 

SELECT 

 u.AccountName

, u.Domain  

, u.DomainUser

, r.DimRoleID

, r.Role 

, r.FK_DimPlan

FROM dbo.stg_UserList u

CROSS JOIN dbo.DimRole r

ORDER BY AccountName, FK_DimPlan

 

END

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[usp_edb_sync_security]

AS

BEGIN

 

--

-- NOTE: This stored procedure assumes that ALL current users and their

--       AD role memberships are included in the dbo.stg_UserGroupList table.

--       The DELETE logic would remove users from DimUser if this is not true.

--  

 

SET NOCOUNT ON;

 

-- Lookup FKs

UPDATE  dbo.stg_UserGroupList 

SETFK_DimUser = u.DimUserID

FROMdbo.stg_UserGroupList f 

JOINdbo.DimUser u ON u.UserName = f.DomainUser

 

-- Insert new users but only if they're in one of the Roles in DimRole

INSERT INTO dbo.DimUser (UserName)

SELECT DISTINCT DomainUser

FROM dbo.stg_UserGroupList

WHERE FK_DimUser IS NULL

 

-- Update FK_DimUser again to get any newly inserted users

UPDATE  dbo.stg_UserGroupList 

SETFK_DimUser = u.DimUserID

FROMdbo.stg_UserGroupList f 

JOINdbo.DimUser u ON u.UserName = f.DomainUser

WHERE FK_DimUser IS NULL

 

-- Insert any new User and Role combinations into DimUserRole; only get rows that  

-- do NOT indicate plan membership

INSERT INTO dbo.DimUserRole (FK_DimUser, FK_DimRole)

SELECT f.FK_DimUser, f.FK_DimRole

FROM dbo.stg_UserGroupList f

LEFT JOIN dbo.DimUserRole r ON r.FK_DimUser = f.FK_DimUser

AND r.FK_DimRole = f.FK_DimRole

WHERE f.FK_DimUser IS NOT NULL

AND f.FK_DimRole IS NOT NULL

AND r.DimUserRoleID IS NULL

AND f.FK_DimPlan = 0

 

-- Insert any new User and Plan combinations into DimUserPlan; only get rows

-- where FK_DimPlan > 0

INSERT INTO dbo.DimUserPlan (FK_DimUser, FK_DimPlan)

SELECT f.FK_DimUser, f.FK_DimPlan

FROM dbo.stg_UserGroupList f

LEFT JOIN dbo.DimUserPlan p ON p.FK_DimUser = f.FK_DimUser

AND p.FK_DimPlan = f.FK_DimPlan

WHERE f.FK_DimUser IS NOT NULL

AND f.FK_DimPlan > 0

AND p.DimUserPlanID IS NULL

 

-- Delete any users that are no longer active

DELETE FROM dbo.DimUser

WHERE UserName NOT IN (

SELECT DISTINCT DomainUser

FROM dbo.stg_UserGroupList

)

 

-- Delete any roles that have been taken away from a user in staging

DELETE FROM dbo.DimUserRole

FROM dbo.DimUserRole r

LEFT JOIN dbo.stg_UserGroupList f ON f.FK_DimUser = r.FK_DimUser 

AND f.FK_DimRole = r.FK_DimRole

WHERE f.AccountName IS NULL

 

-- Delete any plans that have been taken away from a user in staging

DELETE FROM dbo.DimUserPlan

FROM dbo.DimUserPlan p

LEFT JOIN dbo.stg_UserGroupList f ON f.FK_DimUser = p.FK_DimUser 

AND f.FK_DimPlan = p.FK_DimPlan

WHERE f.AccountName IS NULL

 

 

END

 

GO

 

 

 

If you want to restore the database, here are the steps I used

I downloaded the revised code into the folder e:\mssqltips\mssqltips_1657.  I restored the database to my SQL Server 2012 instance.  

Run this to get the logical names and the physical names of the data file and log file

restore filelistonly from disk='E:\mssqltips\mssqltips_1657\mssqltips_1657.bak'

 

Run this to restore the database; since I'm using SQL 2012 the folder names are different than the physical names in the backup

restore database mssqltips_1657

from disk = 'E:\mssqltips\mssqltips_1657\mssqltips_1657.bak'

with

move 'mssqltips_1657' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mssqltips_1657.mdf',

move 'mssqltips_1657_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mssqltips_1657_log.ldf'

Saturday, February 9, 2013 - 3:51:55 PM - Shilpa Back To Top (22030)

Hi,

I am trying out the revised code that is listed above. I have gotten all the users into stg_UserList. The next step is a dataflow task which populates stg_UserGroupList. It references a stored proc which is called exec [dbo].[usp_edb_get_user_role_cross_join](which is supposed to be a cross join between stg_UserList and Dim_Role). My question is, where is the data in DimRole. We have not populated it yet. Can you give me teh contents of the stored proc?

Thanks so much

Friday, August 24, 2012 - 11:04:57 PM - Ned Back To Top (19222)

This was very helpful.

 

Thanks so much

 

Ned

Thursday, March 25, 2010 - 5:06:11 PM - raybarley Back To Top (5116)

You can go back to the posting on Fri, Mar 27 2009 7:25 AM for this thread which describes the revised download that makes some changes to the original including getting rid of the flat file.

 As far as writing changes back to AD I don't know how to do that.  

 

Thursday, March 25, 2010 - 11:06:21 AM - kkhan Back To Top (5113)

I have a situation where I have to read AD data make changes to it after implementing business logic and write the changes back to AD. I would like to seek your suggestions what will be the neat way of doing it.

 Also, I would want to know if a write to flat file can be avoided if I use SSIS. Can I just read from AD and dump into SQL tables.

 Much appreciated!!

Friday, January 1, 2010 - 8:03:55 AM - raybarley Back To Top (4625)

In your previous post you mentioned that your destination was a flat file.  I don't know how NULL is represented in a flat file.  You should use an actual value rather than NULL then when you read the data translate that value to NULL.  Not sure if NULL itself is the problem but it might be.

Thursday, December 31, 2009 - 6:54:23 PM - ChadwickSmith Back To Top (4620)

Actually the workaround didn't work :(   But I found a better resolution.  If you don't use the DimPlan table, this may also happen to you.

When I added a derived column, it also came out NULL (meaning I had two NULL columns at the end).  After some playing around, I noticed that if I move the FKDimPlan column to the first position, all the columns come out NULL:

     Private Sub AddOutputRow(ByVal Row As InputBuffer)
        OutputBuffer.AddRow()
        OutputBuffer.FKDimPlan = Row.FKDimPlan
        OutputBuffer.Role = Row.Role
        OutputBuffer.DimRoleID = Row.DimRoleID
        OutputBuffer.Domain = Row.Domain
        OutputBuffer.AccountName = Row.AccountName
        OutputBuffer.BogusColumn = Row.BogusColumn
   End Sub 

Because I am not using DimPlan, this FKDimPlan column always has a NULL value.  For some reason, it causes all the columns that come after it to be NULL also.  This is easily resolved by putting this column last:

      Private Sub AddOutputRow(ByVal Row As InputBuffer)
        OutputBuffer.AddRow()
        OutputBuffer.Role = Row.Role
        OutputBuffer.DimRoleID = Row.DimRoleID
        OutputBuffer.Domain = Row.Domain
        OutputBuffer.AccountName = Row.AccountName
        OutputBuffer.FKDimPlan = Row.FKDimPlan
   End Sub 


Thursday, December 31, 2009 - 5:49:02 PM - ChadwickSmith Back To Top (4619)

Ahh the workaround.  Yes I will have to resort to this.  And I did verify the column name is mapped properly -- I had to set it up again in order to direct the output to a flat file.  That didn't make a difference.

I'll use a workaround and be done with it.  There may be something funky here that other people haven't seen because I'm running this as an upgraded 2005-2008 package in SQL 2008 on a Windows 2008 R2 server.  I run into strange issues all the time because of this.

Thanks again for your help!


Tuesday, December 29, 2009 - 2:27:12 PM - raybarley Back To Top (4616)

I haven't seen the behavior you describe.  I assume you've verified that the destination is properly mapped; i.e. the column name is mapped to the right column of your destination table.

I would add a column to the data flow before the script (e.g. use a derived column), add another output column in the script, update it with the derived column, and do it as the last line in AddOutputRow. 

 

Tuesday, December 29, 2009 - 1:08:38 PM - ChadwickSmith Back To Top (4612)

I have been working on this for 10 hours and I am almost there!  I just need a tiny bit of help!  I'm working with the revised version of code, which I finally realized has to be run on Windows 2003+.  When I was testing on my XP machine, it just wouldn't work.  I figured that out, now I am stuck on something else.

The "role" column comes out as a NULL every time from the "Check User Role Memberships" script.  All the other columns are correct:

     Private Sub AddOutputRow(ByVal Row As InputBuffer)
        OutputBuffer.AddRow()
        OutputBuffer.AccountName = Row.AccountName
        OutputBuffer.DimRoleID = Row.DimRoleID
        OutputBuffer.Domain = Row.Domain
        OutputBuffer.FKDimPlan = Row.FKDimPlan
        OutputBuffer.Role = Row.Role
    End Sub

I discovered that it always happens to the last column, no matter which one I put last.  In other words, if I change it to make AccountName the last column, then Role returns the correct value but AccountName is NULL:

     Private Sub AddOutputRow(ByVal Row As InputBuffer)
        OutputBuffer.AddRow()
        OutputBuffer.Role = Row.Role
        OutputBuffer.DimRoleID = Row.DimRoleID
        OutputBuffer.Domain = Row.Domain
        OutputBuffer.FKDimPlan = Row.FKDimPlan
        OutputBuffer.AccountName = Row.AccountName
    End Sub

This happens the same whether I direct the output to a SQL table or a Flat File.

 

I'm stumped.  Any help is very very appreciated.  And thanks for putting this together in the first place!

Tuesday, October 6, 2009 - 2:33:07 PM - Joeman Back To Top (4145)

Here's a VB.NET script which extracts all groups for a user. I got some of the code from the revised version and som from here (I think):  http://www.wwwcoder.com/Default.aspx?tabid=68&site=5946&parentid=272&type=art

Thanks again for the revised version. Please feel free to suggest improvments since I'm not a VB.NET programmer. For instance I do not like to initiate the search for every user and maybe there is a way for me to filter the search for a specific group whithout having to see if a group name starts with "someting".

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Security.Principal
Imports System.Collections
Imports System.Windows.Forms
Imports System.DirectoryServices
Public Class ScriptMain
Inherits UserComponent
 
Public Overrides Sub Input_ProcessInput(ByVal Buffer As InputBuffer)
'Get new row whith a user
' AccountName Username GroupName
' simson ad/simson <the Groupname is emty>
While Buffer.NextRow()
Input_ProcessInputRow(Buffer)
End While
If Buffer.EndOfRowset Then
OutputBuffer.SetEndOfRowset()
End If
End Sub

'Extract user roles

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
 
'Added this row initiate the search
Dim dirEntry As New DirectoryEntry(LDAP://dc=ad,dc=dummy, Nothing, Nothing, AuthenticationTypes.Secure)
Dim dirSearcher As New DirectorySearcher(dirEntry)
dirSearcher.Filter = "(&(objectClass=user)(samAccountName=" + Row.AccountName + "))"
dirSearcher.PageSize = 1000
dirSearcher.PropertiesToLoad.Add("memberOf")
Dim propCount As Integer
Try
Dim dirSearchResults As SearchResult = dirSearcher.FindOne()
propCount = dirSearchResults.Properties("memberOf").Count
Dim group As String
' MessageBox.Show("User" + Row.AccountName + "No of groups:" + CType(propCount, String), "Search Error", _
' MessageBoxButtons.OK, MessageBoxIcon.Error)
For i As Integer = 0 To propCount - 1
Row.GroupName = CType(dirSearchResults.Properties("memberOf")(i), String)
Row.GroupName = TrimToName(Row.GroupName)
group = Row.GroupName
 
' I don't want all groups
If group.StartsWith("Nice") = True Then
' MessageBox.Show(GroupName, " a group which i want to add to my outputrows", _
' MessageBoxButtons.OK, MessageBoxIcon.Error)
AddOutputRow(Row)
ElseIf group.StartsWith("Good") = True Then
AddOutputRow(Row)
End If
Next
Catch ex As Exception
'Ignore all Exceptions
' If ex.GetType Is GetType(System.NullReferenceException) Then
' ' MessageBox.Show("Selected user isn't a member of any groups " & _
' ' "at this time.", "No groups listed", _
' ' MessageBoxButtons.OK, MessageBoxIcon.Error)
'they are still a good user just does not
'have a "memberOf" attribute so it errors out.
'code to do something else here if you want
' Else
' MessageBox.Show(ex.Message.ToString, "Search Error", & _
' MessageBoxButtons.OK, MessageBoxIcon.Error)
' End If
Finally 'these prevent other memory leaks
 
dirSearcher = Nothing
If Not dirSearcher Is Nothing Then dirSearcher.Dispose()
dirEntry = Nothing
If Not dirEntry Is Nothing Then dirEntry.Dispose()
End Try
End Sub
Private Sub AddOutputRow(ByVal Row As InputBuffer)
OutputBuffer.AddRow()
OutputBuffer.UserName = Row.UserName
OutputBuffer.AccountName = Row.AccountName
OutputBuffer.Group = Row.GroupName
OutputBuffer.DimUserID = Row.DimUserID
'MessageBox.Show("UserName:" + Row.UserName + "Group:" + Row.GroupName, "Add a row", _
'MessageBoxButtons.OK, MessageBoxIcon.Error)
End Sub
 
Private Function TrimToName(ByVal path As String) As String
Dim parts() As String = path.Split(CType(",", Char))
Return parts(0).Replace("CN=", String.Empty)
End Function
 
End Class 

 

Friday, October 2, 2009 - 3:44:30 PM - raybarley Back To Top (4119)

This tip exhausted just about all of my knowledge/understanding of Active Directory.  I would suggest you look here: http://technet.microsoft.com/en-us/magazine/cc299431.aspx  This is a list of articles written for TechNet magazine by the Microsoft Scripting Guys.  A number of them talk about Active Directory. 

You may also get some other hints by looking at PowerShell articles that deal with Active Directory; e.g. http://technet.microsoft.com/en-us/magazine/2007.06.powershell.aspx

 

 

Thursday, October 1, 2009 - 7:38:51 AM - syale Back To Top (4116)

First thanks for your help so far Ray. my C# is not great!

I have gone back to ADHelper.dll  and I can get the email attribute fine, but when I poll for other attributes I get null values. Can you shed any light on this? I have tried SN (lastName), givenName (firstName), distinguishedName and telephoneNumber

I know these attibutes exist from running dsget/dsquery

using System;

using System.Collections.Generic;

using System.Text;

using System.DirectoryServices;

using System.Collections;

using System.Xml;

using System.IO;

namespace ADHelper

{

public class User

{

/*

* Get all users and the groups that they are a member of; write out to tab delimited file

*

* Example: GetUserGroups("VS", "LDAP://dc=vs,dc=local")

*/

public void GetUserGroups(string domain, string path, string filepath)

{

// Step 1

DirectoryEntry searchRoot = new DirectoryEntry(path, null, null,

AuthenticationTypes.Secure);

DirectorySearcher dirSearch = new DirectorySearcher(searchRoot);

dirSearch.SearchScope = SearchScope.Subtree;

dirSearch.PropertiesToLoad.Add("samaccountname");

dirSearch.PropertiesToLoad.Add("memberof");

dirSearch.PropertiesToLoad.Add("mail");

dirSearch.PropertiesToLoad.Add("distinguishedName");

//dirSearch.PropertiesToLoad.Add("SN");

//dirSearch.PropertiesToLoad.Add("givenName");

//dirSearch.PropertiesToLoad.Add("telephoneNumber");

dirSearch.Filter = "(objectclass=person)";

dirSearch.PageSize = 1000;

string samaccountname = String.Empty;

string mail = String.Empty;

string dN = String.Empty;

//string lastName = String.Empty;

//string firstName = String.Empty;

//string phone = String.Empty;

ArrayList groups;

int personcount = 0;

int objectcount = 0;

using (searchRoot) { // Step 2

using (SearchResultCollection results = dirSearch.FindAll()) {

objectcount = results.Count;

using (StreamWriter sw = new StreamWriter(filepath)) { // Step 3

foreach (SearchResult result in results) { // Step 4

++personcount;

groups = new ArrayList();

// Step 5

ResultPropertyCollection props = result.Properties;

foreach (DictionaryEntry entry in props) {

ResultPropertyValueCollection values = entry.Value

as ResultPropertyValueCollection;

if (entry.Key.ToString() == "samaccountname")

samaccountname = GetSingleValue(values);

if (entry.Key.ToString() == "memberof")

groups = GetGroups(values);

if (entry.Key.ToString() == "mail")

mail = GetSingleValue(values);

if (entry.Key.ToString() == "distinguishedName")

dN = GetSingleValue(values);

//if (entry.Key.ToString() == "SN")

// lastName = GetSingleValue(values);

//if (entry.Key.ToString() == "givenName")

// firstName = GetSingleValue(values);

//if (entry.Key.ToString() == "telephoneNumber")

// phone = GetSingleValue(values);

}

foreach (string group in groups) { // Step 6

sw.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", samaccountname, domain, group, mail, dN);

//sw.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}", samaccountname, domain,

// group, mail, lastName, firstName, phone);

}

}

sw.Flush();

}

}

}

}

ArrayList GetGroups(ResultPropertyValueCollection values)

{

ArrayList valueList = new ArrayList();foreach (object val in values)

{

string memberof = val.ToString();

string[ pairs = memberof.Split(',');string[ group = pairs[0].Split('=');

valueList.Add(group[1]);

}

return valueList;

}

string GetSingleValue(ResultPropertyValueCollection values)

{

foreach (object val in values)

{

return val.ToString();

}

return null;

}

}

}

 

Wednesday, September 30, 2009 - 8:56:20 AM - raybarley Back To Top (4112)

The revised code checks whether the user is a member of any role in the Roles tables.  In the Populate stg_UserGroupList with User Roles data flow task the first step is to cross join the users from AD with the Roles table.  The script transform component that follows then simply leverages the WindowsPrincipal.IsUserInRole method to check whether a user is in a particular role or not.

The check in the Script transform Component for the role = domain\user was a hack so that you could specify a user as a role.  The project that was the basis of this tip had some users who needed access but weren't in any of the roles that allowed access.

Not sure if this is accurate but I was told that the original code did not pickup users when they were members of nested groups.

You do have to put all of the roles you care about in the Roles table.

 

 

Wednesday, September 30, 2009 - 8:04:52 AM - syale Back To Top (4111)

For some reason the revised version of the code without the DLL does not give me the groups the user is a member of so hopefully it is something I have done incorrectly. Do I need to add any entries in the roles table? DoI copy all of the domain user accounts into the roles table to get all groups that a user is a member of or is there an easier way

I made the changes as follows

 I changed the following lines in Get User List from Active Directory (names changed to protect the innocent):

' TODO: Replace "vs" with your domain name

Dim domain As String = "corp"

' TODO: Replace "LDAP://dc=vs,dc=local" with your dc

Dim searchRoot As New DirectoryEntry(LDAP://dc=corp,dc=company,dc=com, Nothing, Nothing, AuthenticationTypes.Secure)

 I changed the following lines in Check User Role Memberships (names changed to protect the innocent):

' TODO: replace "@vs.local" with your domain

upn = Row.AccountName + "@corp.company.com"

 

BTW, thanks so much for the prompt reply :-)

Wednesday, September 30, 2009 - 7:23:12 AM - raybarley Back To Top (4110)

The original code sample posted with the tip was written in C# and deployed as a DLL.  The revised sample was written in VB.NET and and was executed in a Script component within the SSIS package, eliminating the need to deploy the DLL.  They both do essentially the same thing.  The one advantage to the original code is that it's much easier to debug; you can call the method from your own test harness and step right in to the code.  I'm not sure how to do that from an executing SSIS package although it may be possible.

Another point is that it just seems that more people are comfortable with VB.NET than C#; just my own unscientific observation.

Wednesday, September 30, 2009 - 7:03:32 AM - syale Back To Top (4109)

I need to get AD Groups, AD Users and the relationships between them. I will be using a filtered result (group membership) to apply the users login to an application. Using this approach user management is in AD.

 I also want to capture other attributes about the login such as mail for email address and phone for telephone number.

 Which solution is best for me to start from

Monday, July 20, 2009 - 4:03:44 AM - trk Back To Top (3758)

While I would prefer using the tsql approach, it is unsatisfactory since it is limited to pagesize of 1000 and pagesize really should not be altered. It would appear that some other approach such as .net is mandatory.thanks

Saturday, July 18, 2009 - 9:28:37 AM - raybarley Back To Top (3754)

You can certainly edit the sample to capture additional fields from Active Directory.  However, that does require that you have experience with writing VB.Net code.

If you want to try that, make sure that you download the updated sample code.  The instructions are in this thread back on Fri, Mar 27 2009 7:25 AM.  The original sample code was in C# and required an external DLL.  The revised sample uses VB.Net and eliminates the use of the external DLL. 

Since you've stated that you don't have much coding experience I think you would be better off trying to use OPENQUERY in lieu of the VB.Net code.  This sample was suggested earlier in this thread (by jerryhung):

SELECT
Name, mail, telephoneNumber
,cn as LoginName, givenName as FirstName, sn as LastName
,title, company, whenCreated, whenChanged
FROM OPENQUERY( ADSI, 'SELECT
Name, mail, cn, givenName, sn, title, company, whenCreated, whenChanged, telephoneNumber
FROM ''LDAP:// DC=XXX, DC=ca'' WHERE objectCategory = ''Person''')
WHERE mail IS NOT NULL
ORDER BY Name 

You would execute this type of query in a data flow source component (I use SQL Server OLEDB) which would replace the Script Component Source that I used where I executed the VB.Net code to query AD.

I wasn't aware of this OPENQUERY approach myself; I would rather do this than write VB.Net code.

Friday, July 17, 2009 - 1:04:26 PM - trk Back To Top (3750)

Your code solution is great. Is it possible to pull additional active directory columns with it?

I am hoping to pull lastlogin,department(and or description),displayname,distinguishedname as well.

Unfortunately I am a .net novice(or less). Found your .net when I learned that tsql has a pagesize problem.

thanks

Tuesday, May 26, 2009 - 5:26:28 PM - frankb Back To Top (3452)

good idea-I'll give it a try.  Thanks

Tuesday, May 26, 2009 - 2:14:09 PM - Joeman Back To Top (3451)

Hi

Remove the try and catch statments. Your problem might be that you get an error all the time which is ignored. I had an error like this. It appeard to be that the user running the package didn't have the right permissions to perform prinicpal.IsInRole(Row.Role). /Joeman

Monday, May 18, 2009 - 1:15:43 PM - raybarley Back To Top (3392)

Your question about this line: If Row.DomainUser = Row.Role Then    'when can this be true??

I had a scope change where they wanted to be able to do security based on individual users in addition to groups.  We put domain\user in the role table.  If you get a match you don't need to do the principal.isinrole() check.

I don't have any insight as to what might be wrong.

I did get the following code from a former colleague; he used this to query AD for a list of users in a particular group.  You'll probably have to change it some but the general idea may be useful.  This is C#; you need VB.Net for SSIS unless you're using SQL 2008. 

        /// 
        /// Gets a list of members in the specified group.
        /// 
        public List GetGroupMembers(string groupName)
        {
            List users = new List();

            string domain = "somedomain.com";
            string filter = string.Format("(&(ObjectClass={0})(sAMAccountName={1}))", "group", groupName); // principal);
            string[ properties = new string[ { "fullname" };
            DirectoryEntry adRoot = new DirectoryEntry("LDAP://" + domain, null, null, AuthenticationTypes.Secure);
            DirectorySearcher searcher = new DirectorySearcher(adRoot);
            searcher.SearchScope = SearchScope.Subtree;
            searcher.ReferralChasing = ReferralChasingOption.All;
            searcher.PropertiesToLoad.AddRange(properties);
            searcher.Filter = filter;
            SearchResult result = searcher.FindOne();

            if (result != null)
            {

                DirectoryEntry directoryEntry = result.GetDirectoryEntry();
                foreach (object dn in directoryEntry.Properties["member"])
                {
                    DirectoryEntry member = new DirectoryEntry("LDAP://" + dn);

                    //Add users from other groups within this group (only go 1 level deep).
                    if (!IsGroup(member))
                    {
                        users.Add(member);
                    }
                }
            }
            return users;

        }

        /// 
        /// Determine whether the object is a group.
        /// 
        private static bool IsGroup(DirectoryEntry de){
            return de.Properties["objectClass"][1].ToString() == "group";
        }

 

 

 

 

Monday, May 18, 2009 - 11:35:22 AM - frankb Back To Top (3390)

Ray- Usiing VB.net to access AD is huge for a project I am on.  I loaded the 'revised' version of the code and configured it for my domain.  I'm doing something wrong because I only get so far.

I get the list of users to populate the user staging table.  I supplied a list of roles in the role table that match one to one with groups in my AD (I put in ten roles).   After the users populate I can see the cross join working and have reviewed the output to see a role to user reference for each user and role combination.  But I never get a match!  The UserRole table does not populate.  The code tht I am having trouble understanding and that is in the script task that should find matching roles is below:

' I added some comments fsb

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim upn As String

If Row.DomainUser = Row.Role Then    'when can this be true??  fsb

AddOutputRow(Row)

Else

Try

' TODO: replace "@vs.local" with your domain

upn = Row.AccountName + "@hws.edu"       'I added some msgbox outputs to troubleshoot to no avail.  fsb

' MsgBox(Row.Role)  

If upn <> prev_upn Then  'if I REM out the IF statement I get a row for each combo in the cross join so the issue is with the match. fsb

id = New WindowsIdentity(upn)principal = New WindowsPrincipal(id)

prev_upn = upn

End If

If principal.IsInRole(Row.Role) Then

AddOutputRow(Row)

Else

End If

Catch Ex As Exception

' IGNORE any exception

End Try

End If

End Sub

 I'm anxious to get this working- any help is really appreciated.  frankb

 

Tuesday, March 31, 2009 - 11:26:31 PM - Joeman Back To Top (3102)

Fantastic! I can't wait until I have the time to try it. Many thanks!

Friday, March 27, 2009 - 7:25:45 AM - admin Back To Top (3086)

A revised version of the code has been uploaded and can be found in the Next Steps section of the article with a link called "revised version".  Here are some notes for this revised version:

Here are the contents of the README.txt file in the this new zip attachment that has been uploaded:

This SSIS package is a revision to the sample originally published with the
tip How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005
http://www.mssqltips.com/tip.asp?tip=1657

Here are some notes about the revised package:

- The original used an external DLL; in this one all of the code is in the package.  This eliminates
  having to deploy the external DLL to the GAC.

- The Import User from AD into stg_UserList data flow uses a script source component to
  extract all users from Activie Directory and write them out to the table stg_UserList

- The Populate stg_UserGroupList with User Roles data flow does a cross join of stg_UserList
  table with the DimRole table then checks for users in any of the roles in a script
  transform component

- Any users in any role in the DimRole table are written out to the stg_UserGroupList table

- The final step in the package calls a stored proc to synchronize the database with what's in
  Active Directory

- To run the package restore the included database backup (mssqltips_1657.bak)


Thursday, March 26, 2009 - 2:16:29 PM - raybarley Back To Top (3083)

If you need to get the groups that the users belong to you will have to stick with the original code that's posted with the article. The Script Source that you refer to came from a later version of the SSIS package where I made alot of changes.  Instead of getting the list of every group that a user belongs to (this was what the original package did) I changed it around to just get the list of users then check if they are a member of certain groups that I care about. 

The output of the Script Source above gets cross joined with a table called DimRole which has the AD groups I care about.  Then I have another Data Flow with a Script Transform that creates a WindowsPrincipal for each user and calls the IsInRole() methodto check if the user is a member of the roles I care about.  I had a DimRole table with a list of the roles.

I will check on how to post the revised project log another reply to this thread.

 

Thursday, March 26, 2009 - 11:26:05 AM - Joeman Back To Top (3082)

Hi

Thanks a lot for your code. Could you please explain how I should implement the code below in your Script Source component solution(Works great by the way).

 'Your AD helper code

if (entry.Key.ToString() =="memberof")

groups = GetGroups(values);

} //foreach

foreach (string group in groups) { //Step 6sw.WriteLine("{0}\t{1}\t{2}",samaccountname,domain,group);

}

 

Tuesday, March 17, 2009 - 5:53:33 AM - raybarley Back To Top (3022)

Are you sure you are using the Script Component in a Data Flow?  It's in the Data Flow Transformations group in the Toolbox.  There is also a Script task in the ControlFlow - that is not the one you want.  When you drag the Script Component from the Toolbox and drop it in the Data Flow you will a dialog pops up and you have to selectSource, Destination or transformation.  You want Source for this example.

The error about OutputBuffer not declared;  rename the Output; the default is Output 0; just change it to Output to match the code.

Tuesday, March 17, 2009 - 3:03:41 AM - Brain_Killer Back To Top (3021)

Here is my code. now i having a problem with two things...

- the Public Overrides Sub CreateNewOutputRows() give me a message saying that "sub'CreateNewOutputRows' cannot be declared 'overrides' because it does not override a sub in a base class"

-Outputbuffer give me a message saying that "Name 'Outputbuffer' is not declared"      

        OutputBuffer.AddRow()
        OutputBuffer.AccountName = accountName
        OutputBuffer.Domain = domain 

 

 code for the script task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System.Collections
Imports System.DirectoryServices
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime


Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Overrides Sub CreateNewOutputRows()
        '
        ' Add your code here
        '
        Dim domain As String = "mydomain"

        '------- update LDAP://mydomain as appropriate for your environment
        Dim searchRoot As New DirectoryEntry("LDAP://mydomain", Nothing, Nothing, AuthenticationTypes.Secure)
        Dim dirSearch As New DirectorySearcher(searchRoot)
        Dim props As ResultPropertyCollection
        Dim values As ResultPropertyValueCollection
        Dim key As String
        Dim accountName As String

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.Filter = "(objectclass=person)"
        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
                        'For Each entry As DictionaryEntry In props
                        key = CType(entry.Key, String)
                        If key = "samaccountname" Then
                            values = CType(entry.Value, ResultPropertyValueCollection)
                            accountName = CType(values.Item(0), String)
                            If accountName.EndsWith("$") = False Then
                                OutputBuffer.AddRow()
                                OutputBuffer.AccountName = accountName
                                OutputBuffer.Domain = domain
                            End If
                        End If
                    Next
                Next
            End Using
        End Using


        Dts.TaskResult = Dts.Results.Success
    End Sub

    End Sub
   
End Class

Monday, March 16, 2009 - 9:16:19 AM - raybarley Back To Top (3017)

Here is the Books on Line reference for the Data Flow ScriptComponent:

http://msdn.microsoft.com/en-us/library/ms137640(SQL.90).aspx

The way I'm using it in my earlier post is to execute the code to retrieve some information from Active Directory and push that data into the Data Flow.


Monday, March 16, 2009 - 8:14:52 AM - Brain_Killer Back To Top (3014)

 raybarley,

 

i'm sorry to make you a lot of questions, but, I didn't understand very well what you said in the last post.  you said that I need to add this:

- add output columns AccountName and Domain; both are Unicode string [DT_WSTR] length 50 -Add this where? how?

- add a reference to the System.DirectoryServices DLL - How can I do That?

- add Imports System.DirectoryServices to the script - How can I do That?

I have managed to put this Project working like descrived in the Tip. everything is ok and working. Now as I said, i need to add more fields to the project, initialy I said that I need to add the department but i need to add the first name and the last name of the user to. My problem here is that i'm not a developer and the code is hard to me to understand and put in practice what i want. I know that I have to add more collums to the table and add some more things to the SSIS package to accept the new fields, i think that is not a problem for me, the only problem is really only the code... 

 regards... 


 

Monday, March 16, 2009 - 5:58:00 AM - raybarley Back To Top (3013)

I changed the code in the tip to use a Script Source component in the data flow instead of the ADHelper DLL.  The reason was to eliminate having to deploy the external DLL. It may be easier to use a Script Source than having to rework the ADHelper DLL; that's your call.  At least you have everything in the SSIS package as you asked can you do that.

You will have to research the ADSI documentation to find out what other column(s) you need to pull in.  Working with Active Directory is not a trivial task.

To use this code in the Script Source component:

- add output columns AccountName and Domain; both are Unicode string [DT_WSTR] length 50

- add a reference to the System.DirectoryServices DLL

- add Imports System.DirectoryServices to the script

 - paste in the code below

    Public Overrides Sub CreateNewOutputRows()

        Dim domain As String = "YOUR DOMAIN GOES HERE"

        -------- update LDAP://dc=itrp,dc=local as appropriate for your environment
        Dim searchRoot As New DirectoryEntry("LDAP://dc=itrp,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 accountName As String

        dirSearch.SearchScope = SearchScope.Subtree
        dirSearch.PropertiesToLoad.Add("samaccountname")
        dirSearch.Filter = "(objectclass=person)"
        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)
                            accountName = CType(values.Item(0), String)
                            If accountName.EndsWith("$") = False Then
                                OutputBuffer.AddRow()
                                OutputBuffer.AccountName = accountName
                                OutputBuffer.Domain = domain
                            End If
                        End If
                    Next
                Next
            End Using
        End Using

    End Sub
 

Monday, March 16, 2009 - 5:06:05 AM - Brain_Killer Back To Top (3012)

 I found the problem for the errors...the problem was that the option "Protection Level" was set with the value of "DonsaveSensitive" i have to change this value. Now i have other errors, but i will restore the db to check if everything is configured correctly. Now i have other question... I'm using this project because I need to retrive group members from AD to a dataview. My chief want's to see members from an AD group, and check what the department of this members belong to. looking for this project, I only can see members from a determined group and nothing more. How can I add more values (like department) to this project? need to edit the ADHelper.DLL? how can I do that? can I put the code directely in the SSIS package and don't use the dll file? how can i manage that? 

sorry for the noob questions, 

 

regards


Friday, March 13, 2009 - 9:44:34 AM - raybarley Back To Top (2998)

You should probably restore the mssqltips database backup then you can script out all of the database objects and create them in your database.  The issue you are having with the diagram - did you create the foreign keys?  To script out all of the databaseobjects use SQL Server management Studio.  Right click on the mssqltips database, then select Tasks, Generate Scripts.  Just check everything.  You can get everything into a single query window or file then selectively create the obejcts in your database.

 Also to get started it might be easier to work with the mssqltips database, get everything working, then migrate to your own database.


Friday, March 13, 2009 - 9:34:53 AM - raybarley Back To Top (2997)

Based on your error message take a look at the ConnectionString property of the MSSQTIPS connection manager.  This is what's there when I created the package:

Data Source=localhost;Initial Catalog=mssqltips;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Make sure that the value you have doesn't include mssqltips as the Initial Catalog.

 

 

Friday, March 13, 2009 - 3:41:23 AM - Brain_Killer Back To Top (2994)

 hey,

 

I'm trying to use this in my company, maybe I need to change some thing but now, the first step that i'm corrently trying is to implement equal to that you demonstrate here in the tip "How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005".

I have downloaded the SSIS Package, and i created a database with a diferent name (CitrixADInfo), created the 3 tables DimUser, DimRole and DimUserRole, All the FK in the tables (DimUserID, DimRoleID and DimUserRoleID) are configured as int, and the other field from the tables (DomainUsers, Role, FK_DimUser and FK_DimRole) are configured as nvarchar (max) are this correct? after that I tryied to create the diagram for the tables but I can't manage to do the relationships with the tables like the ones that are in this tip document, What is the right way to do this?

After that, I downloaded the SSIS package, puted the ADHelper.DLL in the right places, and I tryed to run the SSIS package control flow step by step, to see if I have some problem with the SSIS Package.

I first start the "Extract Users and Group Memberships from AD" step, and it run well, it created in the c:\temp folder the right file with info in them, so for me this step is ok. After that started the real problems, the other steps don't run sucessfuly, "Truncate stg_UserGroupList" step and "Import Users and Groups into stg_UserGroupList" step  give me an error, and can't manage to figure the problem. I changed in the Connection manager the MSSQLTIPS connection pointing to the right database that I have, chaged the "Put stg_UserGroupList" in dataflow to point to the right database to, need to change something else? in the code, i need to exchange anything?

 

Update:

Step: Truncate stg_UserGroupList errors:

[Execute SQL Task] Error: Failed to acquire connection "MSSQLTIPS". Connection may not be configured correctly or you may not have the right permissions on this connection.

Task TRUNCATE stg_UserGroupList failed

 

Step: Import Users and Groups into stg_UserGroupList errors:

[Put stg_UserGroupList [39]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MSSQLTIPS" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. 

[DTS.Pipeline] Error: component "Put stg_UserGroupList" (39) failed validation and returned error code 0xC020801C. 

 

regards,  



Thursday, January 8, 2009 - 4:13:23 AM - r5d4 Back To Top (2527)

Takes me back to a previous employment where I did the same.

http://sqlsolace.blogspot.com/2006/06/querying-active-directory-group.html

I achieved it using a linked server and TSQL. This example looks at AD group membership.
The dbas had little control over what groups sysadmin staff placed users in, hence running this periodically kept us informed :)

I do recall having 'fun' getting it to work with different versions of AD (windows 2000, 2003 etc)

One 'gotcha' was that you couldnt pull back date columns or multi-valued columns in this way.

r5d4

Wednesday, January 7, 2009 - 2:41:52 PM - jerryhung Back To Top (2522)

 couple years I ago I was using this to retrieve AD user info. I don't remember if I have to create a linked server ADSI

SELECT
Name, mail, telephoneNumber
,cn as LoginName, givenName as FirstName, sn as LastName
,title, company, whenCreated, whenChanged
FROM OPENQUERY( ADSI, 'SELECT
Name, mail, cn, givenName, sn, title, company, whenCreated, whenChanged, telephoneNumber
FROM ''LDAP:// DC=XXX, DC=ca'' WHERE objectCategory = ''Person''')
WHERE mail IS NOT NULL
ORDER BY Name

-- forgot what the below does now, it's in the same file..

xp_enumgroups 'Domain'


Saturday, January 3, 2009 - 11:30:57 AM - raybarley Back To Top (2493)
I'm not aware of a T-SQL solution. I would rather use a T-SQL solution. The sample could be repackaged as a CLR function which would make it available via T-SQL.

Saturday, January 3, 2009 - 9:20:55 AM - aspiringgeek Back To Top (2491)

This is a very interesting post.

There used to be--& my bet is that there remains--a means by which to interrogate AD via a straight T-SQL solution.  I invite you to share the latest-&-greatest with regard to that with us.  Thanks much.

Jimmy May, Aspiring Geek
http://blogs.msdn.com/jimmymay














get free sql tips
agree to terms