Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Adding an Active Directory User with the Integration Services (SSIS) Script Task


By:   |   Last Updated: 2016-06-10   |   Comments (4)   |   Related Tips: More > 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

  1. SQL Server 2012 or 2014
  2. SSIS installed
  3. SQL Server Data Tools Installed (SSDT)
  4. We will use a Domain Administrator Account
  5. 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:

Active Directory Users and Computers

These groups are already added in SQL Server:

Logins in SQL Server

The SQL Logins already have Server and Database permissions assigned:

SQL Server Login Properties

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).


    SSIS parameters


  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. SSIS packages


  6. In the package to create a user, drag and drop the script task, open it and in the ReadOnlyVariables, click the browse button:


  7. selecting project parameters in the script task


  8. Select the Project parameters created in step 3:


  9. List of variables and parameters


  10. In the references section, add the System.DirectoryServices.AccountManagement. This is used for AD operations related to AD Users and Groups:


  11. The system.directoryServices.AccountManagement reference


  12. In the Script Task, expand the namespace and add the System.DirectoryServices.AccountManagement namespace:


  13. The System.DirectoryServices.AccountManagement namespace

  14. Add the following code to the script:


  15.   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;
          }


  16. 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.


  17. PrincipalContext ouContex = new
    PrincipalContext(ContextType.Domain
    ,"paladin.com","CN=Users,DC=paladin,DC=com");


  18. In the second part we are storing the SSIS Project variables in local variables:


  19. string firstName = Dts.Variables["$Project::FirstName"].Value.ToString();
    string lastName = Dts.Variables["$Project::LastName"].Value.ToString();


  20. Finally, using the context explained in the step 10, we add the properties to the AD User and save it:


  21. 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();


  22. Run the package to test the code:


  23. SSIS Script task to create users

  24. If everything is successful, you will have created an AD User in the Users Container. In this example, the name is BenjiPrice:


  25. Verify the AD User is created


  26. If you check the users' properties, you will be able to see the information specified:


  27. The AD User Properties

  28. 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:


  29. Script task to add an AD user to an AD Group


  30. In the script task, add the following Project values:


  31. Select variables


  32. Repeat steps 7 and 8 and add the following code:


  33. 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;
      }


  34. 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:


  35. 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);


  36. 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:


  37. using (PrincipalContext pc = new
          PrincipalContext(ContextType.Domain,"paladin.com"))
                    {
                        GroupPrincipal group = GroupPrincipal.FindByIdentity(pc, groupName);
                        group.Members.Add(pc, IdentityType.Name, userId);
                        group.Save();
                    }


  38. Run the script task with the code:


  39. Running the script task


  40. 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:


  41. The DBA AD group


  42. Open the DBA properties and go to the Members tab. You will notice that the AD User was created:


  43. Verify the User is created in the AD Group
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:



Last Updated: 2016-06-10


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 01, 2018 - 6:02:58 PM - Jeff Back To Top

I made use of the code above and it worked great.  I'd like to however, write to other AD fields like Initials and employeeID.  I know this involves making use of Principal Extensions, but to date I've not found any good code that actually does this, and/or I can't seem to make sense of how to use the UserPrincipalsEx extended class.

Would you happen to be able to point me in a direction or provide samples of how to make use of them in the code you provided above?

 


Wednesday, March 22, 2017 - 1:42:15 PM - Pete Back To Top

It is also possible to add user and password in the PrincipalContext() constructor. Even if running SSIS with the AD Account is the best way.
I'm confronted with another problem, I succeeded in creating a user and a group programmatically using my PrincipalContext, but when I call group.Members.Add, I get a 1355 error code.

 


Wednesday, March 22, 2017 - 10:47:25 AM - Daniel Calbimonte Back To Top

 SSIS is running with the Active Directory account with privileges on AD.

 


Wednesday, March 22, 2017 - 10:06:12 AM - Pete Back To Top

 Hi Daniel,

Thanks for your post.
I have a question about the connection to the Active Directory.
If I have a dedicated account to connect to the Active Directory where can I set this up?

For instance I want to connect to the AD paladin.com using Account toto with password toto


Thanks for your help

 Pete


Learn more about SQL Server tools