By: Daniel Calbimonte | Comments (5) | Related: > Integration Services Security
Problem
My company just acquired another company and I have a large list of users to add to Active Directory. I want to see if there is a way to automate this process using SQL Server Integration Services.
Solution
In this tip, we will show how to create an Active Directory User and add it to an Active Directory Group using the SSIS Script Task.
Requirements
- SQL Server 2012 or 2014
- SSIS installed
- SQL Server Data Tools Installed (SSDT)
- We will use a Domain Administrator Account
- The machine connected to an Active Directory domain
Introduction
In this tip, we will add a new user named Benji Price. He is a new colleague and we need to add him to our AD Domain as a User and we want to add him to the DBA AD Group. In our company, we have several AD Groups already created:
These groups are already added in SQL Server:
The SQL Logins already have Server and Database permissions assigned:
What we are going to do next is to create an AD User using the SSIS Script task with project parameters and assign the User to an AD Group.
Getting started
1 - Open SQL Server Data Tools (SSDT).
2 - Create a SSIS project.
3 - In the Solution Explorer, double click in Project.Params and create 3 parameters:
- The FirstName of the user (this is the first name used for the AD User).
- The LastName of the user (this is the last name used for the AD User).
- The Group (this is the AD group that will be assigned).
4 - We will create 2 packages. One to create the AD User and the other to assign the AD User to the AD Group.
5 - In the package to create a user, drag and drop the script task, open it and in the ReadOnlyVariables, click the browse button:
6 - Select the Project parameters created in step 3:
7 - In the references section, add the System.DirectoryServices.AccountManagement. This is used for AD operations related to AD Users and Groups:
8 - In the Script Task, expand the namespace and add the System.DirectoryServices.AccountManagement namespace:
9 - Add the following code to the script:
public void Main()
{
// TODO: Add your code here
PrincipalContext ouContex = new
PrincipalContext(ContextType.Domain
,"paladin.com","CN=Users,DC=paladin,DC=com");
try
{
string firstName = Dts.Variables["$Project::FirstName"].Value.ToString();
string lastName = Dts.Variables["$Project::LastName"].Value.ToString();
UserPrincipal up = new UserPrincipal(ouContex);
up.SamAccountName = firstName + lastName;
up.DisplayName = firstName + " " + lastName;
up.Surname = lastName;
up.GivenName = firstName;
up.SetPassword("password");
up.Enabled = true;
up.ExpirePasswordNow();
up.Save();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
10 - The code create an AD user using the Project Parameters. The first part of the code defines the context. The context is the place where we will create the AD User. In this example, we are creating the AD User in the paladin.com domain in the Users container.
PrincipalContext ouContex = new PrincipalContext(ContextType.Domain ,"paladin.com","CN=Users,DC=paladin,DC=com");
11 - In the second part we are storing the SSIS Project variables in local variables:
string firstName = Dts.Variables["$Project::FirstName"].Value.ToString(); string lastName = Dts.Variables["$Project::LastName"].Value.ToString();
12 - Finally, using the context explained in the step 10, we add the properties to the AD User and save it:
UserPrincipal up = new UserPrincipal(ouContex);
up.SamAccountName = firstName + lastName;
up.DisplayName = firstName + " " + lastName;
up.Surname = lastName; ;
up.GivenName = firstName;
up.SetPassword("password");
up.Enabled = true;
up.ExpirePasswordNow();
up.Save();
13 - Run the package to test the code:
14 - If everything is successful, you will have created an AD User in the Users Container. In this example, the name is BenjiPrice:
15 - If you check the users' properties, you will be able to see the information specified:
16 - Now, we are going to add this user to a specified AD Group. Note that the AD group must be created. In the other package, use the Script Task:
17 - In the script task, add the following Project values:
18 - Repeat steps 7 and 8 and add the following code:
public void Main()
{
try
{
// TODO: Add your code here
string firstName = Dts.Variables["$Project::FirstName"].Value.ToString();
string lastName = Dts.Variables["$Project::LastName"].Value.ToString();
string name = firstName+lastName;
string group = Dts.Variables["$Project::Group"].Value.ToString();
AddUserToGroup(name, group);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public void AddUserToGroup(string userId, string groupName)
{
try
{
using (PrincipalContext pc = new
PrincipalContext(ContextType.Domain,
"paladin.com"))
{
GroupPrincipal group = GroupPrincipal.FindByIdentity(pc, groupName);
group.Members.Add(pc, IdentityType.Name, userId);
group.Save();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
19 - The code adds the AD User in the specified AD Group. The first part of the code, assigns the SSIS project parameters to local variables:
string firstName = Dts.Variables["$Project::FirstName"].Value.ToString();
string lastName = Dts.Variables["$Project::LastName"].Value.ToString();
string name = firstName+lastName;
string group = Dts.Variables["$Project::Group"].Value.ToString();
AddUserToGroup(name, group);
20 - In the context you specify the domain. The FindByIndentity looks for and finds the AD group specified and the group.members.add adds the user to the group:
using (PrincipalContext pc = new
PrincipalContext(ContextType.Domain,"paladin.com"))
{
GroupPrincipal group = GroupPrincipal.FindByIdentity(pc, groupName);
group.Members.Add(pc, IdentityType.Name, userId);
group.Save();
}
21 - Run the script task with the code:
22 - This code added the user Benji Price to the DBA group. The DBA was assigned in step 3. In the Active Directory User and Computers, go to the DBA group:
23 - Open the DBA properties and go to the Members tab. You will notice that the AD User was created:
Conclusion
In this tip, we created C# code to add an AD User using the SSIS script task. The information about the users were stored in Project Parameters. We learned how to use Project Parameters in an SSIS Script task. Finally, we added the User as a member of DBA AD group.
This should give you the basis to build an SSIS package to read the list of users from a database table or text file and automate the process of adding users to Active Directory.
Next Steps
For more details about the namespaces, Active Directory, SSIS Scripts, refer to these links:
- Getting started with the SSIS Script Task
- System.DirectoryServices.AccountManagement Namespaces
- Integration Services (SSIS) Parameters
- SQL Server Integration Services Tips
- SQL Server Integration Services Tutorial
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
