none
Add/remove users to active directory not working RRS feed

  • Question

  • Hi,

    I am trying to add/remove users from active directory groups.The sql query results are captured by the dts object and is being used to fill adapter table.I am processing data in each row and depending upon whether Request is Add/Remove,I am calling Add/Remove functions.I dont know what is happening because there are no exceptions /errors thrown.The Add/Remove succeeds without  actually adding anyone in the AD.In fact,the log file gets messages printed  for users added or removed.

    Pls guide.Below is the code:

                                                                                    

    public void Main()
            {
                try
                {
                    string connectionString = @"Data Source=SQL1234;Provider=SQLOLEDB;Initial Catalog=ADUsers;Integrated Security=SSPI";
                    string sql = "UPDATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE RequestType = ? AND ADGroupName LIKE ? AND Submitted_For = ? AND ApprovalStatus = ?;";
                    using (OleDbConnection cnn = new OleDbConnection(connectionString))
                    {
                        cnn.Open();
                        var context = new PrincipalContext(ContextType.Domain, "xyz123", "svaccount", "abcd");
                        var table = new DataTable();
                        using (var adapter = new OleDbDataAdapter())
                        {
                            adapter.Fill(table, Dts.Variables["User::Users"].Value);
                            foreach (DataRow row in table.Rows)
                            {
                                string Request = row["RequestType"].ToString();
                                string RequestFor = row["Submitted_For"].ToString();
                                string User = row["sAMAccountName"].ToString();
                                string AD = row["ADGroupName"].ToString();
                                string Status = row["ApprovalStatus"].ToString();

                                if (Request == "Add")
                                {
                                    var groupname = GroupPrincipal.FindByIdentity(context,AD);
                                    AddUserToGroup(context, groupname, User);
                                    groupname.Save();

                                }
                                else
                                {
                                    var groupname = GroupPrincipal.FindByIdentity(context,AD);
                                    RemoveUserFromGroup(context, groupname, User);
                                    groupname.Save();
                                }

                                using (OleDbCommand command = new OleDbCommand(sql, cnn))
                                {
                                    command.Parameters.Add("@req", OleDbType.VarChar).Value = Request;
                                    command.Parameters.AddWithValue("@grp", "%" + AD + "%");
                                    command.Parameters.AddWithValue("@reqFor", RequestFor);
                                    command.Parameters.AddWithValue("@status", Status);

                                    command.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }

            catch (Exception exp)
                {
                    var ErrorFile = (string)Dts.Variables["$Package::ErrorLog"].Value;
                    Dts.Events.FireError(0, "Standard Permissions App Requests", exp.Message + "\r" + exp.StackTrace, String.Empty, 0);
                    File.AppendAllText(ErrorFile, "Date :" +
                    DateTime.Now.ToString() + "\t" + "Message:" + exp.Message + "\t" + Environment.NewLine);

                //Dts.TaskResult = (int) ScriptResults.Failure;
                }
             }

                                                   

    public void AddUserToGroup(PrincipalContext context, GroupPrincipal group, string result)
            {
                var ErrorFileAdd = (string)Dts.Variables["$Package::ErrorLog"].Value;

                    var user = UserPrincipal.FindByIdentity(context, IdentityType.SamAccountName, result.Trim().ToLower());

                    if (!group.Members.Contains(user))
                    {
                        group.Members.Add(user);

                        File.AppendAllText(ErrorFileAdd, "Date :" +
                        DateTime.Now.ToString() + "\t" + "Message:" + "Added this user to Ad:" + "\t" + result + "\t" + group + Environment.NewLine);
                    }
                }

            } 

    public void RemoveUserFromGroup(PrincipalContext context, GroupPrincipal group, string username)
            {
                var ErrorFileRmv = (string)Dts.Variables["$Package::ErrorLog"].Value;
                    var person = UserPrincipal.FindByIdentity(context, IdentityType.SamAccountName, username.Trim().ToLower());
                if (group.Members.Contains(person))
                {
                    group.Members.Remove(context, IdentityType.SamAccountName, username);

                    File.AppendAllText(ErrorFileRmv, "Date :" +
                    DateTime.Now.ToString() + "\t" + "Removed this user from AD:" + username + "\t" + group + Environment.NewLine);
                }
                
            }

    Wednesday, October 31, 2018 4:52 PM

Answers

  • I was thinking of just using a dictionary so you can process row by row. You could do a group by but ordering would need to be done to ensure the rows come back in the order you want. For that you'll need something you can group on such as an add date, sequentially incrementing ID or something. If you don't have any of that then you'll need to use ROWINDEX or equivalent.

    For this example I'll assume you have an ID column that is sequentially incrementing so you can group by it. This is untested code since I don't have your SSIS stuff set up.

    private static void Main ( string[] args )
    {
        try
        {
            var table = GetUsers();
    
            //Group by the AD groups and then order by the IDs which are sequentially incrementing
            var groups = from t in table.Rows.OfType<DataRow>()
                            group t by t.Field<string>("ADGroupName") into g                             
                            select new { Name = g.Key, Users = g.OrderBy(i => i.Field<int>("Id")).ToList() };
    
            using (var context = new PrincipalContext(ContextType.Domain, "domain", "user", "password"))
            {
                //For each group
                foreach (var group in groups)
                {
                    //Get the AD group, may not exist??
                    using (var groupPrincipal = GroupPrincipal.FindByIdentity(context, group.Name))
                    {
                        //Enumerate the users being worked on
                        foreach (var user in group.Users)
                        {
                            ApplyUserChange(groupPrincipal, user);                                
                        };
    
                        //Apply the changes - this could fail so wrap in try-catch if you want to keep trying with other users
                        groupPrincipal.Save();
                    };
    
                    //AD was updated so update your database
                    SetProcessed(group.Name);
                };
            };                
        } catch (Exception exp)
        {
            var ErrorFile = (string)Dts.Variables["$Package::ErrorLog"].Value;
            Dts.Events.FireError(0, "Standard Permissions App Requests", exp.Message + "\r" + exp.StackTrace, String.Empty, 0);
            File.AppendAllText(ErrorFile, "Date :" +
            DateTime.Now.ToString() + "\t" + "Message:" + exp.Message + "\t" + Environment.NewLine);
    
            //Dts.TaskResult = (int) ScriptResults.Failure;
        }
    }        
    
    private static DataTable GetUsers ()
    {
        //Your logic for populating a datatable goes here
        //I'm using a dummy table
        var dt = new DataTable();
        dt.Columns.AddRange(new[]
        {
            new DataColumn("Id", typeof(int)),
            new DataColumn("ADGroupName", typeof(string)),
            new DataColumn("sAMAccountName", typeof(string)),
            new DataColumn("RequestType", typeof(string))
        });
    
        dt.Rows.Add(1, "Group1", "User1", "Add");
        dt.Rows.Add(1, "Group2", "User2", "Add");
        dt.Rows.Add(1, "Group3", "User3", "Add");
        dt.Rows.Add(1, "Group1", "User4", "Add");
        dt.Rows.Add(1, "Group2", "User5", "Add");
        dt.Rows.Add(1, "Group3", "User6", "Add");
        dt.Rows.Add(1, "Group1", "User1", "Remove");
    
        return dt;
    }
    
    private static void ApplyUserChange ( GroupPrincipal group, DataRow user )
    {
        var userName = user.Field<string>("sAMAccountName").Trim();
    
        //If you expect more actions later then consider using an enum instead
        var isAdd = String.Compare(user.Field<string>("RequestType"), "Add", StringComparison.OrdinalIgnoreCase) == 0;
        var isRemove = !isAdd && String.Compare(user.Field<string>("RequestType"), "Remove", StringComparison.OrdinalIgnoreCase) == 0;
    
        var ErrorFileAdd = (string)Dts.Variables["$Package::ErrorLog"].Value;
    
        //Case shouldn't matter here
        var userPrincipal = UserPrincipal.FindByIdentity(group.Context, IdentityType.SamAccountName, userName);
        if (isAdd && !group.Members.Contains(userPrincipal))
            group.Members.Add(userPrincipal);
        else if (isRemove && group.Members.Contains(userPrincipal))
            group.Members.Remove(userPrincipal);
    
        File.AppendAllText(ErrorFileAdd, "Date :" +
                            DateTime.Now.ToString() + "\t" + "Message:" + (isAdd ? "Added" : "Removed") + " this user to AD:" + "\t" + userName + "\t" + group.Name + Environment.NewLine);            
    }
    
    private static void SetProcessed ( string groupName )
    {
        //This should come from ConnectionManager and be specified by in the package, then use SSIS's implementation
        //https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/connecting-to-data-sources-in-the-script-task?view=sql-server-2017
        //var conn = Dts.Connections["MyADUsersConnection].AcquireConnection(Dts.Transaction)
        var connectionString = @"Data Source=SQL1234;Provider=SQLOLEDB;Initial Catalog=ADUsers;Integrated Security=SSPI";
        using (var conn = new OleDbConnection(connectionString))
        {
            var cmd = conn.CreateCommand();
    
            //Set the parameters ideally based upon just the IDs associated with the rows, this helps with concurrency, if that isn't an option
            //then you'll need to expand your filter - the important part though is that you're updating all the users that were added to the group
            //rather than a separate call for each user - concurrency is an issue if this table is updated while your SSIS task is running
            //cmd.CommandText = "UPDATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE RequestType = ? AND ADGroupName LIKE ? AND Submitted_For = ? AND ApprovalStatus = ?;";
            cmd.CommandText = "UDPATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE ADGroupName = ?";                
            cmd.Parameters.AddWithValue("@adGroupName", groupName);
    
            //This could fail so wrap in try-catch if you don't want to fail SSIS task
            cmd.ExecuteNonQuery();
        };
    }

    In this code example I've broken up the functionality to make it easier to read. I've also put in some comments about using SqlConnection instead of OleDbConnection unless you really need OLEDB and using SSIS's ConnectionManager to manage the connections instead of doing this manually in code. There is a lot of assumptions going on here such as no concurrency issues, you won't have any more than 2 actions and any errors just fail the task.



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Thursday, November 1, 2018 8:54 PM
    Thursday, November 1, 2018 3:29 PM
    Moderator

All replies

  • Calling Add/Remove on Members just adds it to the collection. To actually save the changes to the group you have to call the Save method.

    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, October 31, 2018 5:24 PM
    Moderator
  • I have added the groupname.save() in my code.Now,it looks like it took sometime to add the users to the groups.Now i am seeing them in the AD groups.Wondering,why it took sometime.
    Wednesday, October 31, 2018 5:32 PM
  • Updating AD can take a while. If you want to have your existing script work like you had though (with error handling) then you'll want to make sure that you call Save after each Add/Remove. Otherwise it'll either all succeed or all fail. The more times you call Save the slower it'll run.

    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, October 31, 2018 5:41 PM
    Moderator
  • Is it possible to call save after for loop in any way?
    Thursday, November 1, 2018 1:47 AM
  • Yes, just move it outside the foreach loop. However you are now doing a batch update so if any of the add/remove calls fail then the state is undefined. Without trying it I cannot say whether it'll fail all the add/remove calls or just one and then skip the rest. Nevertheless you'd need to requery the AD group to see what members were added/removed.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, November 1, 2018 2:49 AM
    Moderator
  • I couldnt move groupname.Save() outside for loop because groupname var is local to foreach loop
    Thursday, November 1, 2018 2:52 AM
  • I see, so your rows represent a series of user/group mappings. Your foreach loop is enumerating through the rows, getting the AD group, adding/removing the user from that group and then you're updating a ProcesseToAD column stating that you pushed that change. 

    Personally I think you should leave the code as is other than perhaps adjusting the if statement to simply wrap the add/remove call. If you try to batch the changes, as I mentioned earlier, you won't know which changes were successful and therefore which rows to update vs not. Your code for add/remove already handles the case where your DB is out of sync so it probably doesn't matter but still it would be inefficient to run through a bunch of rows each time when only 1 row is failing.

    The most I think you could optimize this is to fetch each AD group only once. You could set up a dictionary that maps the group (as defined by your row) to the AD object. Inside your foreach loop you check to see if you have the group yet. If you don't then get it otherwise use the one you've already retrieved. Then add/remove  as normal but without the save. After you've enumerated through all the rows then enumerate through the groups you fetched (in the dictionary) and then call Save on each of them. For each group, if the save was successful then update the corresponding rows (by group) to indicate you've processed them. 


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, November 1, 2018 3:33 AM
    Moderator
  • Thanks a lot for clarification.For me the order of the rows are important because I want to process the add/remove based on first come first serve basis.So,the way rows are stored in the table,I want to process them one by one.How do i ensure the order by implementing the method you describe in last para(dictionary ).Also,it will be great if you can show me skeletal implementation of this in my code because I am kind of learning things now.

    I ma trying something in these lines,pls let me know if it will help me saving groups after loop ends:

    https://sensibledev.com/linq-group-by-multiple-columns/

    And trying out:

    var context = new PrincipalContext(ContextType.Domain, "abc123", "svcaccount", "xyz1234");
                        var table = new DataTable();
                        using (var adapter = new OleDbDataAdapter())
                        {
                            adapter.Fill(table, Dts.Variables["User::Users"].Value);
                            var results = (from r in table.AsEnumerable()
                                           group r.Field<string>("sAMAccountName") by new {ad = r.Field<string>("ADGroupName"), rt = r.Field<string>("RequestType"), rd = r.Field<DateTime>("RDate") } into g
                                           orderby g.Key.rd
                                           select new GroupInformation
                                           {
                                               Group = g.Key.ad,
                                               ReqType=g.Key.rt,
                                               ReqDate=g.Key.rd,
                                               Users = g.ToList(),Requests=g.ToList()
                                           }).ToList();

     foreach (var result in results)
                            {
                                var group = GroupPrincipal.FindByIdentity(context, result.Group);
                                foreach (var userName in result.Users)
                                {
                                    if(  )
                                    { 
                                       AddUserToGroup(context, group, userName);
                                    }
                                     
                                }
                                group.Save();
                                
                               
                            }

    public class GroupInformation
            {
                public string Group { get; set; }
                public string ReqType { get; set; }
                public DateTime ReqDate { get; set; }
                public IEnumerable<string> Users { get; set; }
                public IEnumerable<string> Requests { get; set; }
            }

    I am doing the above to get a list of user and request type(add/remove) and then may be call add/remove for every results.I dont know how do i check if the ReqType is Add/Remove.Pls guide me .




    Thursday, November 1, 2018 12:32 PM
  • I was thinking of just using a dictionary so you can process row by row. You could do a group by but ordering would need to be done to ensure the rows come back in the order you want. For that you'll need something you can group on such as an add date, sequentially incrementing ID or something. If you don't have any of that then you'll need to use ROWINDEX or equivalent.

    For this example I'll assume you have an ID column that is sequentially incrementing so you can group by it. This is untested code since I don't have your SSIS stuff set up.

    private static void Main ( string[] args )
    {
        try
        {
            var table = GetUsers();
    
            //Group by the AD groups and then order by the IDs which are sequentially incrementing
            var groups = from t in table.Rows.OfType<DataRow>()
                            group t by t.Field<string>("ADGroupName") into g                             
                            select new { Name = g.Key, Users = g.OrderBy(i => i.Field<int>("Id")).ToList() };
    
            using (var context = new PrincipalContext(ContextType.Domain, "domain", "user", "password"))
            {
                //For each group
                foreach (var group in groups)
                {
                    //Get the AD group, may not exist??
                    using (var groupPrincipal = GroupPrincipal.FindByIdentity(context, group.Name))
                    {
                        //Enumerate the users being worked on
                        foreach (var user in group.Users)
                        {
                            ApplyUserChange(groupPrincipal, user);                                
                        };
    
                        //Apply the changes - this could fail so wrap in try-catch if you want to keep trying with other users
                        groupPrincipal.Save();
                    };
    
                    //AD was updated so update your database
                    SetProcessed(group.Name);
                };
            };                
        } catch (Exception exp)
        {
            var ErrorFile = (string)Dts.Variables["$Package::ErrorLog"].Value;
            Dts.Events.FireError(0, "Standard Permissions App Requests", exp.Message + "\r" + exp.StackTrace, String.Empty, 0);
            File.AppendAllText(ErrorFile, "Date :" +
            DateTime.Now.ToString() + "\t" + "Message:" + exp.Message + "\t" + Environment.NewLine);
    
            //Dts.TaskResult = (int) ScriptResults.Failure;
        }
    }        
    
    private static DataTable GetUsers ()
    {
        //Your logic for populating a datatable goes here
        //I'm using a dummy table
        var dt = new DataTable();
        dt.Columns.AddRange(new[]
        {
            new DataColumn("Id", typeof(int)),
            new DataColumn("ADGroupName", typeof(string)),
            new DataColumn("sAMAccountName", typeof(string)),
            new DataColumn("RequestType", typeof(string))
        });
    
        dt.Rows.Add(1, "Group1", "User1", "Add");
        dt.Rows.Add(1, "Group2", "User2", "Add");
        dt.Rows.Add(1, "Group3", "User3", "Add");
        dt.Rows.Add(1, "Group1", "User4", "Add");
        dt.Rows.Add(1, "Group2", "User5", "Add");
        dt.Rows.Add(1, "Group3", "User6", "Add");
        dt.Rows.Add(1, "Group1", "User1", "Remove");
    
        return dt;
    }
    
    private static void ApplyUserChange ( GroupPrincipal group, DataRow user )
    {
        var userName = user.Field<string>("sAMAccountName").Trim();
    
        //If you expect more actions later then consider using an enum instead
        var isAdd = String.Compare(user.Field<string>("RequestType"), "Add", StringComparison.OrdinalIgnoreCase) == 0;
        var isRemove = !isAdd && String.Compare(user.Field<string>("RequestType"), "Remove", StringComparison.OrdinalIgnoreCase) == 0;
    
        var ErrorFileAdd = (string)Dts.Variables["$Package::ErrorLog"].Value;
    
        //Case shouldn't matter here
        var userPrincipal = UserPrincipal.FindByIdentity(group.Context, IdentityType.SamAccountName, userName);
        if (isAdd && !group.Members.Contains(userPrincipal))
            group.Members.Add(userPrincipal);
        else if (isRemove && group.Members.Contains(userPrincipal))
            group.Members.Remove(userPrincipal);
    
        File.AppendAllText(ErrorFileAdd, "Date :" +
                            DateTime.Now.ToString() + "\t" + "Message:" + (isAdd ? "Added" : "Removed") + " this user to AD:" + "\t" + userName + "\t" + group.Name + Environment.NewLine);            
    }
    
    private static void SetProcessed ( string groupName )
    {
        //This should come from ConnectionManager and be specified by in the package, then use SSIS's implementation
        //https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/connecting-to-data-sources-in-the-script-task?view=sql-server-2017
        //var conn = Dts.Connections["MyADUsersConnection].AcquireConnection(Dts.Transaction)
        var connectionString = @"Data Source=SQL1234;Provider=SQLOLEDB;Initial Catalog=ADUsers;Integrated Security=SSPI";
        using (var conn = new OleDbConnection(connectionString))
        {
            var cmd = conn.CreateCommand();
    
            //Set the parameters ideally based upon just the IDs associated with the rows, this helps with concurrency, if that isn't an option
            //then you'll need to expand your filter - the important part though is that you're updating all the users that were added to the group
            //rather than a separate call for each user - concurrency is an issue if this table is updated while your SSIS task is running
            //cmd.CommandText = "UPDATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE RequestType = ? AND ADGroupName LIKE ? AND Submitted_For = ? AND ApprovalStatus = ?;";
            cmd.CommandText = "UDPATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE ADGroupName = ?";                
            cmd.Parameters.AddWithValue("@adGroupName", groupName);
    
            //This could fail so wrap in try-catch if you don't want to fail SSIS task
            cmd.ExecuteNonQuery();
        };
    }

    In this code example I've broken up the functionality to make it easier to read. I've also put in some comments about using SqlConnection instead of OleDbConnection unless you really need OLEDB and using SSIS's ConnectionManager to manage the connections instead of doing this manually in code. There is a lot of assumptions going on here such as no concurrency issues, you won't have any more than 2 actions and any errors just fail the task.



    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by msdnpublic1234 Thursday, November 1, 2018 8:54 PM
    Thursday, November 1, 2018 3:29 PM
    Moderator
  • Thanks a lot,I dont trust the ID column and prefer ordering by ApprovedDate(Rdate) column.I am working on the code you suggest and will get back on it.
    Thursday, November 1, 2018 3:43 PM
  • Hi CoolDadTx,

    I tried the above ,fixed couple things and ran the code.It seems to go through  but it still takes ~15mins to populate 3 users into AD.Dont know if it can be further optimized:(

    Thanks a lot
    Thursday, November 1, 2018 8:54 PM
  • I cannot imagine it is taking 15 mins to update 3 users unless you're dealing with a very large AD structure. You might consider looking at the execution log for SSIS to see if it is this task that is taking that long. If so then put some Stopwatch calls in to time the various calls. It is possible your AD calls are just slow and maybe you need to involve your infrastructure team. If it is literally just the Save call then maybe use a test console app to make changes to eliminate SSIS and see what is going on.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, November 1, 2018 9:13 PM
    Moderator
  • Hi Michael,

    There is no lag when i ran the package second time yesterday ,i guess due to some glitch it took a while yesterday.But all good with respect to adding users to AD,but I face an "incorrect syntax error near "=" while executing command.Looking into that ,don't know what is causing this.

    Friday, November 2, 2018 1:29 PM
  • So,I actually modified the SetProcessed() statement to something like these and they worked.Thanks again for the help!!

    var connectionString = @"Data Source=SQL123;Provider=SQLOLEDB;Initial Catalog=ADUsers;Integrated Security=SSPI";
                string sql = "UPDATE BI_UserPermissionList SET ProcessedToAD='yes' WHERE ADGroupName LIKE ?";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@adGroupName", groupName);
                        conn.Open();
                  
                        cmd.ExecuteNonQuery();
                    }
                }


    Friday, November 2, 2018 4:42 PM