Active Directory Data into SQL Table

How to populate an SQL Server table with users information from Active Directory?

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)

How best to retrieve user data from Active Directory in conjunction with SQL Server data

It's pretty simple actually (assuming you are already authenticated). Use this as your template for the GET request:

{baseUrl}/{tenantId}/users/{oid}?api-version={api-version}

Don't forget to add your bearer token to the Authorization header:

Authorization: Bearer {accessToken}

Also, here is an example of an object you might use for the response (in Java), with some helpful methods for retrieving the signup email (assuming you are using built in B2C users instead of third party, like google):

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.ArrayList;
import java.util.List;

@JsonIgnoreProperties(ignoreUnknown = true)
public class GraphApiUserExample{

    @JsonProperty("objectId")
    private String id;

    private Boolean accountEnabled;

    private com.brmic.azure.graph.api.client.model.PasswordProfile PasswordProfile;

    private List<SignInName> signInNames;

    private String surname;

    private String displayName;

    private String givenName;

    @JsonProperty("userPrincipalName")
    private String userPrincipalName;

    public String getId(){

        return id;
    }

    public void setId(final String id){

        this.id = id;
    }

    public Boolean getAccountEnabled(){

        return accountEnabled;
    }

    public void setAccountEnabled(final Boolean accountEnabled){

        this.accountEnabled = accountEnabled;
    }

    public com.brmic.azure.graph.api.client.model.PasswordProfile getPasswordProfile(){

        return PasswordProfile;
    }

    public void setPasswordProfile(final com.brmic.azure.graph.api.client.model.PasswordProfile passwordProfile){

        PasswordProfile = passwordProfile;
    }

    public List<SignInName> getSignInNames(){

        return signInNames;
    }

    public void setSignInNames(final List<SignInName> signInNames){

        this.signInNames = signInNames;
    }

    public String getSurname(){

        return surname;
    }

    public void setSurname(final String surname){

        this.surname = surname;
    }

    public String getDisplayName(){

        return displayName;
    }

    public void setDisplayName(final String displayName){

        this.displayName = displayName;
    }

    public String getGivenName(){

        return givenName;
    }

    public void setGivenName(final String givenName){

        this.givenName = givenName;
    }

    public String getUserPrincipalName(){

        return userPrincipalName;
    }

    public void setUserPrincipalName(final String userPrincipalName){

        this.userPrincipalName = userPrincipalName;
    }

    @JsonIgnore
    public String getSignInEmail(){

        String email = "";
        if(signInNames != null){
            for(SignInName signInName : signInNames){
                if(signInName.getType().equals("emailAddress")){
                    email = signInName.getValue();
                    break;
                }
            }
        }
        return email;
    }

    @JsonIgnore
    public void setSignInEmail(String signInEmail){

        if(signInNames == null){
            signInNames = new ArrayList<>();
            signInNames.add(new SignInName("emailAddress", signInEmail));
            return;
        }

        for(SignInName signInName : signInNames){
            if(signInName.getType().equals("emailAddress")){
                signInName.setValue(signInEmail);
                break;
            }
        }
    }
}

If you want to pull multiple users, you can add a query instead of the 'oid' to filter and page through the results.

{baseUrl}/{tenantId}/users?api-version={api-version}&$skiptoken={skiptoken}&$top={top}&$filter={attributeOne} eq '{valueOne}'
{baseUrl}/{tenantId}/users?api-version={api-version}&$skiptoken={skiptoken}&$top={top}$filter=signInNames/any(x:x/value eq '{email}')

Which returns a JSON object like this:

{
    "users":[...],
    "odata":{
        "nextLink": "{baseUrl}/{tenantId}/users?api-version={api-version}&$skiptoken={skiptoken}&$top={top}&$filter={attributeOne} eq '{valueOne}'"
        "metadata": "I forget if this is just a string or a parsable JSON object."
    }

}

You will still have the problem of matching the results of the query to your DB results, which is going to be a heavy operation.
I suggest that you use cache the results in a table for joins if you need to run faster operations.
It does feel kludgey, but only because it is.

There are also some powershell commands from the B2C azure powershell module you could use, and you could use ADAL to create a job in SQL Server for updating the contents of your table or view.

Documentation is here: https://learn.microsoft.com/en-us/azure/active-directory-b2c/active-directory-b2c-devquickstarts-graph-dotnet

Importing Active Directory Users and Groups into SQL Server (possibly via c#)

Arry,

I don't know exactly, but found some links that may help you. I think the hottest track is this expression:

"(&(objectCategory=Person)(memberOf=DN=GroupName, OU=Org, DC=domain,
DC=com))"

I found it in LDAP Query for group members on a ColdFusion community's site. I'm more or less sure the filter can easily be applied to your query. I'm sorry, but I cannot test it, because I have no AD around here.

This one could also be a bit (but less) interesting:

http://forge.novell.com/pipermail/cldap-dev/2004-April/000042.html

Hope this helps, cheers,

Matthias

---

Related Topics