How to populate an SQL Server table with users inf

Question

Using VB.NET in an SSIS package, how do I populate an SQL Server table with the users in multiple Active Directory domains (in the same forest)?

Answer 1

Linked Server approach:

Set up a linked server to connect to Active Directory:

Here is one possible option using Linked Server on SQL Server that does not actually require VB.NET, if that is an option for you. The queries given below are only for syntax references. Please read the links for proper usage of these SQL Server objects.

You could set up a linked server on SQL Server to connect to the Active Directory using OLE DB Provider for Microsoft Directory Services. Below is a sample script that would set up a linked server and map it with a login that has access to the Active directory.

Usage of sp_addlinkedserver (Transact-SQL)

Usage of sp_addlinkedsrvlogin (Transact-SQL)

Sample script to set up linked server:

USE [master];
GO

EXEC    master.dbo.sp_addlinkedserver 
        @server     = N'ADSI'
    ,   @srvproduct = N'ADSI'
    ,   @provider   = N'ADsDSOObject'
    ,   @datasrc    = N'adsdatasource'
    ,   @provstr    = N'ADSDSOObject';
GO

EXEC    master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname     = N'ADSI'
    ,   @useself        = N'False'
    ,   @locallogin     = NULL
    ,   @rmtuser        = N'<User account goes here...>'
    ,   @rmtpassword    = '<Password goes here...>';
GO

Use OpenQuery to query Active Directory:

You can then use OpenQuery to query the Active Directory for user information. Read the below link on MSDN for more information on how to formulate the query.

Search Active Directory - Distributed Query

Sample script to query Active Directory:

SELECT  *
FROM    OPENQUERY
        (           ADSI
            ,   '   SELECT  *
                    FROM    ''LDAP://<OU path goes here...>''
                    WHERE   objectClass = ''user'''
        );

How to use this data in SSIS package:

You could set up this query as an SQL Server view and then call the view from OLE DB Source available on Data Flow Task within SSIS package and then redirect the output to an OLE DB Destination to populate the database tables.

Answer 2

I went the route of using a query similar to Siva's approach except instead of a linked server query, I use the ADSI provider. Active Directory SSIS Data Source

SELECT
    distinguishedName
,   mail
,   samaccountname
,   Name
,   employeeNumber
,   objectSid
,   userAccountControl
,   givenName
,   middleName
,   sn
FROM
    'LDAP://DC=domain,DC=net'
WHERE
    sAMAccountType = 805306368
ORDER BY
    sAMAccountName ASC

The code in the referenced post will pull all users from a given domain. After converting from NTEXT to TEXT to String, I then use the distinguished name and the DirectoryServices assembly to enumerate all the first order groups for a user via their distinguished name. It does not address nested groups. Conveniently enough, that post covered a 2005 SSIS implementation so the logic is in VB.

Answer 3

Script Component (VB.NET) with System.DirectoryServices

Here is a sample logic that import Active Directory users information from one domain into database table with the help of VB.NET in Script Component configured as Source. This sample was tested in SSIS 2012 but should work in SSIS 2008 and above. This logic will not work in SSIS 2005 because the namespace System.DirectoryServices.AccountManagement was introduced only in .NET framework 3.5 and SSIS 2005 uses .NET Framework 2.0

• Create an SSIS package. This sample uses SSIS 2012.

• Create an OLEDB Connection Manager that would connect to the SQL Server database. If you created a data source, add the data source to the package's connection manager tab.

• Drag and drop a Data Flow Task onto the Control Flow tab.

• Double-click the Data Flow Task to switch to the Data Flow tab.

• Drag and drop a Script Component onto the Data Flow tab.

• Check Source on the Select Script Component Type dialog and click OK.

• Double-click the Script Component to open the Script Transformation Editor. Click Inputs and Outputs tab page.

• Rename the Output to ActiveDirectory to give a meaningful name.

• Select Output Columns and click Add Column to add each of the below mentioned columns. This is only to illustrate this example. You might need to add columns of your preference.

Column definition within script component

Name              Data Type                Length
----------------- ------------------------ ------
FirstName         Unicode string [DT_WSTR]    255
LastName          Unicode string [DT_WSTR]    255
SAMAccountName    Unicode string [DT_WSTR]    255
UserPrincipalName Unicode string [DT_WSTR]    255

• After defining the columns, click Script tab page

• Change the ScriptLanguage to Microsoft Visual Basic 2010

• On the Solution Explorer, right-click the Script Component project and click Add Reference.... Add references to the following namespaces.

Namespaces to be referenced in the script component

System.DirectoryServices
System.DirectoryServices.AccountManagement

• Paste the below VB.NET code into the Script component. Replace the section <Your domain name goes here> with your appropriate domain name. The code initializes PrincipalContext and PrincipalSearcher objects in PreExecute method and then disposes them in PostExecute method. CreateNewOutputRows method loops through each of the row found in AD to fetch the user attributes information.

Script component code (VB.NET)

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.DirectoryServices.AccountManagement
Imports System.DirectoryServices

#End Region

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Dim principalContext As PrincipalContext = Nothing
    Dim principalSearcher As PrincipalSearcher = Nothing

    Public Overrides Sub PreExecute()
        principalContext = New PrincipalContext(ContextType.Domain, "<Your domain name goes here>")
        principalSearcher = New PrincipalSearcher(New UserPrincipal(principalContext))
        MyBase.PreExecute()
    End Sub

    Public Overrides Sub PostExecute()
        principalContext = Nothing
        principalSearcher = Nothing
        MyBase.PostExecute()
    End Sub

    Public Overrides Sub CreateNewOutputRows()

        For Each principal As Principal In principalSearcher.FindAll()

            Dim entry As DirectoryEntry = TryCast(principal.GetUnderlyingObject(), DirectoryEntry)

            With ActiveDirectoryBuffer
                .AddRow()

                If entry.Properties("givenName").Value IsNot Nothing Then
                    .FirstName = entry.Properties("givenName").Value.ToString()
                Else
                    .FirstName = "Unknown"
                End If

                If entry.Properties("sn").Value IsNot Nothing Then
                    .LastName = entry.Properties("sn").Value.ToString()
                Else
                    .LastName = "Unknown"
                End If

                If entry.Properties("samAccountName").Value IsNot Nothing Then
                    .SAMAccountName = entry.Properties("samAccountName").Value.ToString()
                Else
                    .SAMAccountName = "Unknown"
                End If

                If entry.Properties("userPrincipalName").Value IsNot Nothing Then
                    .UserPrincipalName = entry.Properties("userPrincipalName").Value.ToString()
                Else
                    .UserPrincipalName = "Unknown"
                End If

            End With

        Next
    End Sub

End Class

• Close the Script Transformation Editor.

• Drag and drop an OLE DB Destination onto the Data Flow tab. Connect the Script component to the OLE DB destination to redirect the source output. Select the appropriate OLE DB Connection Manager and the table where the data should be inserted into.

Ways to improve this approach:

This sample provides loading information from only one domain. If you have multiple domains, you could stored them in a table. Fetch information of all the domain lists and use Foreach Loop Container available on the Control Flow to loop through each domain and get the users information using the above mentioned approach. May be there is also a better way to do this within VB.NET.

Active Directory User Attributes

You can find the complete list of Active Directory user attributes in the below MSDN link. You need to click the links to find the LDAP-Display-Name.

All Attributes (Windows)

Here is another link that might help to get the user object attributes

User Object Attributes (Windows)