29 January 2016

Getting Active Directory Users and Groups in a SQL Server Integration Services Solution

In SQL Server Business Intelligence projects, one usual thing to do is to develop a dynamic security in Analysis Services solution based on user access in order to restrict Dimension Data. In this cases one possible solution is to load a bridge table mapping users and dimension members that are allowed or denied to certain user.

In one of our projects, it was necessary to get the groups and their users directly from the Active Directory Domain. The requirement was that those groups were located in a specific and well known container in the AD.

Given this situation, the goal was to build a dtsx package to extract data (like any other in the SSIS solution) and load it in a temporary staging table. In this case the source data is the Active Directory instead of a most typical relational database or flat file source. When transferring data from a usual SSIS data source, we would use the Data Flow Task component. For this purpose we use a Script task in the Control Flow replacing the Data Flow Task.

Using de script task it was possible to write some C# code to allow the use of the AccountManagement namespace in .Net Framework (3.5 and above). This namespace is a simpler way of managing users and groups than the previously and existing DirectoryServices namespace that required a most extensive knowledge on his use and required much more code to implement.

In the following code, implemented in the SSIS Script Task, in the first step we want to identify all groups in a given OU that is meant to contain all the groups which members are users that must be loaded in the staging table.

//Creates the context against witch all queries are performed. The first two parameters are used to define the domain context and the third one is used to define a specific container (OU) where the BI groups are

PrincipalContext ouContext = new PrincipalContext(ContextType.Domain, “domain.com”, “OU=BIGroups,DC=domain,DC=com”);

//Creates a GroupPrincipal object that will be use as a query filter in the next statement. Is passed the domain context that will be applied in the search as well as the SamAccountName (Unique name in AD) to search. In this case (using *) will be searched all groups in the specified OU

GroupPrincipal allGroupsInContext = new GroupPrincipal(ouContext, “*”);

//Encapsulates the methods and search patterns used to execute a query against the specified context

PrincipalSearcher ps = new PrincipalSearcher(allGroupsInContext);

//Executes the query beeing the result a collection of all the principal objects that match the the query filter

PrincipalSearchResult<Principal> groupResults = ps.FindAll();

In the second step, we obtain all users that are members of the previously identified groups. A DataTable is used to store them while the search is being made. After iterating all groups and users the data is finally inserted in the staging table in the Sql Server database using the SqlBulkCopy class.

//Creates the DataTable to store the Users and Groups

DataTable table = new DataTable();

table.Columns.Add(“User”, typeof(string));

table.Columns.Add(“Name”, typeof(string));

table.Columns.Add(“Email”, typeof(string));

table.Columns.Add(“Group”, typeof(string));

//Iterate all the groups in the specified OU

foreach (var group in groupResults)

{

    GroupPrincipal gr = (GroupPrincipal)group;

   //Get the members of all the identified groups

    PrincipalSearchResult<Principal> userResults = gr.GetMembers();

    //Iterate all the group members

    foreach (Principal result in userResults)

    {

         //We are looking for users. Other members are ignored

        if (result.StructuralObjectClass == “user”){

            UserPrincipal user = (UserPrincipal)result;

             //Saves the user and the respective group

            table.Rows.Add(user.SamAccountName, user.Name, user.EmailAddress, group.Name);

        }

    }

}

//Insert the Users and Groups in the Sql Server staging table

using (SqlConnection conn = new SqlConnection(“Connection”))

{

    conn.Open();

    SqlBulkCopy bulk = new SqlBulkCopy(conn);

    bulk.DestinationTableName = “StagingTable”;

    bulk.WriteToServer(table);

}

So, that is no need to be an expert in Active Directory in order to accomplish this goal. Using this .Net Namespace and writing a half dozen lines of C# code turned out to be quite simple to query and extract user and group information from de Active directory.

This was an example for this specific situation in our BI project, however, there are other operations we can do about accessing and manipulating users and groups in the AD using this objects.

There are another ways of processing Active Directory information in the SSIS (if you want to avoid writing the C# code) using DataFlow or other ControlFlow tasks as explained in this article. However, in my opinion, is not as simple, intuitive and flexible as using the .Net / SctiptTask solution just described.

 

.
.
.
.
.
.
    João Cordeiro
       Consultant
Blog