Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

AD, SQL Server, GMail synchronizer tool

Rate me:
Please Sign up or sign in to vote.
4.86/5 (5 votes)
29 Jun 2011CPOL6 min read 29.5K   526   51   3
Multi account synchronizer for GMail, AD, and SQL Server.

Introduction

This program is written with the intention of being able to create GMail accounts using the .NET API supplied from Google. It is written with Visual Studio 2008. The whole project is included, and a working copy should be available in the Release directory.

How it Runs

Step 1: Setup the SQL Database Configuration

config.JPG

Select the appropriate SQL Server for your user accounts and logging.

Step 2: Create AD Accounts and Groups

User Setup

Image 2

Users can be pulled from a table or a view and put anywhere in the Active Directory the execution account has access to. A custom column to add field mappings can be made by using the Custom tab. Nearly every writable field in AD is available. Every user is put in a default group after being created, to identify the user synch that created them.

Groups Setup

Image 3

Groups are sort of not necessary for this tool, but seemed like a good inclusion, so here they are. It requires a table or view with the list of groups. The distinct set of groups from that table are created in a sub OU with the group append name. To get the users assigned to the groups, a cross reference table or view is required.

  • Conversion of any SQL Server 2000 data into Microsoft Active Directory Users, or Security Groups.
  • Accounts/Groups mapped from SQL Server to AD by field assignment.
  • Synchronize data based on user unique ID from the database source to AD.
  • Account data is not actively synchronized.

Step 3: Create GMail Accounts

Image 4

  • Conversion from AD to GMail.
  • Users created as ID99999999@domain.gmail.apps.edu.
  • User email accounts aliased as first.last@domain.gmail.apps.edu; if there are duplicates, combination of the middle and first name are abbreviated to get a unique name.

Execution

  • Task Scheduler can call the LDAP magic command line, giving the parameters for a saved mapping and the type of operation.
  • LDAP magic executes the selected operation and creates a log.
  • The log file generates a history of successful transactions, warnings, and errors. Logging is currently not saved.

Using the Code

There is so much code I don't know what qualifies to end up here. A decent portion of it is commented, and simplistic debug code is also hidden in the comments. On average, each function has a small description and a list of expected parameters if they didn't seem self evident. Most of the code is designed to be robust and easily repurposed. Most of everything that is likely to create an error is setup with error catching code and logging. Now, onto some of the good stuff.

First, an example of some of the SQL query logic

C#
//utils/toolset.cs

public SqlDataReader QueryInnerJoin(string table1, string table2, 
       string pkey1, string pkey2, ArrayList additionalFields, 
       SqlConnection sqlConn, LogFile log)
{
    // additionalFields takes the field names " table.field,"
    // Returns data from table1 where the row is in both table 1 and table2
    // additional fields table2.data2
    //**************************************************
    //| Table1   | Table2            | Returned result
    //**************************************************
    //| ID  Data | ID    Data  Data2 |              | Table1. Table1. Table2.
    //                                                 ID      DATA    data2
    //| 1   a    | 1     a     e     | RETURNED     | 1         a        e
    //| 2   b    | null  null  f     | NOT RETURNED |            
    //| 3   c    | 3     null  g     | RETURNED     | 3         c        g
    //| 4   d    | 4     e     h     | RETURNED     | 4         d        h

    SqlDataReader r;
    SqlCommand sqlComm;
    string additionalfields = "";
    foreach (string key in additionalFields)
    {
        additionalfields += key;
    }
    additionalfields = additionalfields.Remove(additionalfields.Length - 2);
    if (additionalFields.Count > 0)
    {
        sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".*, " + additionalfields + 
                  " FROM " + table1 + " INNER JOIN " + table2 + " ON " + 
                  table1 + "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
    }
    else
    {
        sqlComm = new SqlCommand("SELECT DISTINCT " + table1 + ".* FROM " + 
                  table1 + " INNER JOIN " + table2 + " ON " + table1 + 
                  "." + pkey1 + " = " + table2 + "." + pkey2, sqlConn);
    }

    try
    {
        sqlComm.CommandTimeout = 360;
        r = sqlComm.ExecuteReader();
        log.addTrn(sqlComm.CommandText.ToString(), "Query");
        return r;
    }
    catch (Exception ex)
    {
        log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + 
                   " error " + ex.Message.ToString() + "\n" + 
                   ex.StackTrace.ToString(), "Error");
    }
    return null;
}

This is a sample of one of the queries that was designed primarily to merge data. Most queries are duplicated; one returns a SQLDataReader; the complementary query uses SELECT INTO and creates a SQL table for use in other methods.

Next is the mass Active Directory user creation snippet

C#
//utils/toolset.cs

public void CreateUsersAccounts(string ouPath, SqlDataReader users, 
       string groupDn, string ldapDomain, UserSynch usersyn, LogFile log)
{
    // oupath holds the path for the AD OU to hold the Users 
    // users is a sqldatareader witht the required fields
    // in it ("CN") other Datastructures would be easy to substitute 
    // groupDN is a base group which all new users get automatically inserted into

    int i;
    int fieldcount;
    int val;
    string name = "";
    string last = "";
    string first = "";
    fieldcount = users.FieldCount;
    try
    {
        while (users.Read())
        {
            try
            {
                if (users[usersyn.User_password].ToString() != "")
                {
                    if (!DirectoryEntry.Exists("LDAP://CN=" + 
                        System.Web.HttpUtility.UrlEncode(
                        users[usersyn.User_sAMAccount].ToString()
                        ).Replace("+", " ").Replace("*", "%2A") + "," + ouPath))
                    {

                        DirectoryEntry entry = new DirectoryEntry("LDAP://" + ouPath);
                        DirectoryEntry newUser = entry.Children.Add("CN=" + 
                          System.Web.HttpUtility.UrlEncode(
                          users[usersyn.User_CN].ToString()).Replace(
                          "+", " ").Replace("*", "%2A"), "user");
                        // generated
                        newUser.Properties["samAccountName"].Value = 
                          System.Web.HttpUtility.UrlEncode(
                          users[usersyn.User_sAMAccount].ToString()).Replace(
                          "+", " ").Replace("*", "%2A");
                        //newUser.Properties["mail"].Value = System.Web.HttpUtility.UrlEncode(
                        //  users[usersyn.User_mail].ToString()).Replace("+", " ").Replace(
                        //  "*", "%2A") + "@" + System.Web.HttpUtility.UrlEncode(
                        //  users[usersyn.UserEmailDomain].ToString()).Replace(
                        //  "+", " ").Replace("*", "%2A");
                        newUser.Properties["UserPrincipalName"].Value = 
                          System.Web.HttpUtility.UrlEncode(
                          users[usersyn.User_sAMAccount].ToString()).Replace(
                          "+", " ").Replace("*", "%2A");
                        newUser.Properties["displayName"].Value = 
                          System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_Lname]).Replace(
                          "+", " ").Replace("*", "%2A") + ", " + 
                          System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_Fname]).Replace(
                          "+", " ").Replace("*", "%2A");
                        newUser.Properties["description"].Value = 
                          System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_Lname]).Replace("+", " ").Replace(
                          "*", "%2A") + ", " + System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_Fname]).Replace(
                           "+", " ").Replace("*", "%2A");

                        newUser.CommitChanges();

                        // SQL query generated ensures matching field
                        // names between the SQL form fields and AD
                        for (i = 0; i < fieldcount; i++)
                        {
                            name = users.GetName(i);
                            // eliminiate non updatable fields
                            if (name != "password" && name != "CN")
                            {
                                // mail needs some special handling
                                if (name != "mail")
                                {
                                    if ((string)users[name] != "")
                                    {
                                        newUser.Properties[name].Value = 
                                          System.Web.HttpUtility.UrlEncode(
                                          (string)users[name]).Replace(
                                          "+", " ").Replace("*", "%2A");
                                    }
                                }
                                else
                                {
                                    first = System.Web.HttpUtility.UrlEncode(
                                      (string)users[name]).Replace("+", " ").Replace(
                                      "*", "%2A").Replace("%40", "@");
                                    last = (string)users[name];
                                    // check to see if mail field has illegal characters
                                    if (first == last)
                                    {
                                        // no illegal characters input the value into AD
                                        newUser.Properties[name].Value = 
                                          System.Web.HttpUtility.UrlEncode(
                                          (string)users[name]).Replace("+", " ").Replace(
                                          "*", "%2A").Replace("!", "%21").Replace(
                                          "(", "%28").Replace(")", "%29").Replace(
                                          "'", "%27").Replace("_", "%5f").Replace(
                                          " ", "%20").Replace("%40", "@");
                                    }
                                    else
                                    {
                                        // newUser.Properties[name].Value = "";
                                    }
                                }
                            }
                        }

                        AddUserToGroup("CN=" + System.Web.HttpUtility.UrlEncode(
                          users[usersyn.User_sAMAccount].ToString()).Replace(
                          "+", " ").Replace("*", "%2A") + "," + usersyn.UserHoldingTank, 
                          groupDn, false, ldapDomain, log);
                        newUser.Invoke("SetPassword", new object[] { 
                          System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_password]).Replace(
                           "+", " ").Replace("*", "%2A") });
                        newUser.CommitChanges();

                        val = (int)newUser.Properties["userAccountControl"].Value;
                        // set to normal user
                        newUser.Properties["userAccountControl"].Value = 
                                val | (int)accountFlags.ADS_UF_NORMAL_ACCOUNT;
                        // set to enabled account val & ~0c0002 creates
                        // a bitmask which reverses the disabled bit
                        newUser.Properties["userAccountControl"].Value = 
                                           val & ~(int)accountFlags.ADS_UF_ACCOUNTDISABLE;
                        newUser.CommitChanges();
                        newUser.Close();
                        newUser.Dispose();
                        entry.Close();
                        entry.Dispose();
                        log.addTrn("User added |" + (string)users[usersyn.User_sAMAccount] + 
                                   " " + usersyn.UserHoldingTank, "Transaction");
                    }
                    else
                    {
                        log.addTrn("CN=" + System.Web.HttpUtility.UrlEncode(
                          (string)users[usersyn.User_sAMAccount]).Replace("+", " ").Replace(
                          "*", "%2A") + "," + ouPath + 
                          " user already exists from adding", "Error");
                        //MessageBox.Show("CN=" + System.Web.HttpUtility.UrlEncode(
                          (string)users["CN"]).Replace("+", " ").Replace("*", "%2A") + 
                          "," + ouPath + " user already exists from adding");
                    }
                }

            }
            catch (Exception ex)
            {
                string debugdata = "";
                for (i = 0; i < fieldcount; i++)
                {

                    debugdata += users.GetName(i) + "=" + 
                      System.Web.HttpUtility.UrlEncode((string)users[i]).Replace(
                      "+", " ").Replace("*", "%2A") + ", ";

                }
                log.addTrn("issue create user LDAP://CN=" + System.Web.HttpUtility.UrlEncode(
                  (string)users["CN"]).Replace("+", " ").Replace("*", "%2A") + "," + ouPath + 
                  "\n" + debugdata + " User create failed, commit error" + name + " | " + 
                  ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                // MessageBox.Show(e.Message.ToString() + "issue create user LDAP://CN=" + 
                //  System.Web.HttpUtility.UrlEncode((string)users["CN"]).Replace("+", 
                //  " ").Replace("*", "%2A") + "," + ouPath + "\n" + debugdata);
            }
        }

    }
    catch (Exception ex)
    {
        if (users != null)
        {
            string debugdata = "";
            for (i = 0; i < fieldcount; i++)
            {
                debugdata += users.GetName(i) + "=" + System.Web.HttpUtility.UrlEncode(
                  (string)users[i]).Replace("+", " ").Replace("*", "%2A") + ", ";
            }
            log.addTrn("issue create user LDAP://CN=" + System.Web.HttpUtility.UrlEncode(
              (string)users["CN"]).Replace("+", " ").Replace("*", "%2A") + "," + ouPath + 
              "\n" + debugdata + " failed field maybe " + name + " | " + 
              ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
            // MessageBox.Show(e.Message.ToString() + "issue create user LDAP://CN=" + 
            // System.Web.HttpUtility.UrlEncode((string)users["CN"]).Replace(
            // "+", " ").Replace("*", "%2A") + "," + ouPath + "\n" + debugdata);
        }
        else
        {
            log.addTrn("issue creating users datareader is null " + 
              ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
        }
    }
    /*
    //Add this to the create account method
    int val = (int)newUser.Properties["userAccountControl"].Value; 
         //newUser is DirectoryEntry object
    newUser.Properties["userAccountControl"].Value = val | 0x80000; 
}

Take note, the UserSynch contains most of the field names gathered from the GUI interface. This code is responsible for taking a SqlDataReader and creating Active Directory accounts. There are many more general examples of doing the same thing. However, this is a good starting point if you are trying to create some AD account from a SQL table.

Last, the code used to create the GMail accounts from a SQLDataReader

C#
public DataTable Create_Gmail_Users(AppsService service, 
       GmailUsers gusersyn, SqlDataReader users, LogFile log)
{
    // user alising not created yet
    // Takes the SQLDataReader and creates all users in the reader

    // create the table for holding the users final
    // gmail account infomation for email writeback
    DataTable returnvalue = new DataTable();
    DataRow row;

    returnvalue.TableName = "users";

    returnvalue.Columns.Add(gusersyn.Writeback_primary_key);
    returnvalue.Columns.Add(gusersyn.Writeback_email_field);

    row = returnvalue.NewRow();
    string studentID = "";
    string first_name = "";
    string last_name = "";
    string middle_name = "";
    string password = "";
    string userNickName = "Aliasing off";
    try
    {
        while (users.Read())
        {
            try
            {
                // using _ as escape character allows illegal characters in username
                studentID = System.Web.HttpUtility.UrlEncode(
                  users[gusersyn.User_StuID].ToString()).Replace(
                  "+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", 
                  "%28").Replace(")", "%29").Replace("'", "%27").Replace(
                  "_", "%5f").Replace(" ", "%20").Replace("%", "_");
                // names are less restricitve the only illegal characters are < > =
                first_name = users[gusersyn.User_Fname].ToString().Replace(
                  "<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
                middle_name = users[gusersyn.User_Mname].ToString().Replace("<", 
                              "%3c").Replace(">", "%3e").Replace(
                              "=", "%3d").Replace("%", "%25");
                last_name = users[gusersyn.User_Lname].ToString().Replace("<", 
                            "%3c").Replace(">", "%3e").Replace(
                            "=", "%3d").Replace("%", "%25");
                if (gusersyn.User_password_generate_checkbox == false)
                {
                    // password needs to bea able to handle special characters
                    password = users[gusersyn.User_password].ToString();
                }
                else
                {
                    password = GetPassword(14);
                }
                if (gusersyn.User_password_short_fix_checkbox == true && password.Length < 8)
                {
                    password = GetPassword(14);
                }

                //Create a new user.
                UserEntry insertedEntry = 
                   service.CreateUser(studentID, first_name, last_name, password);

                //if (gusersyn.Levenshtein == true)
                //{
                // create user ailas here
                userNickName = GetNewUserNickname(service, studentID, 
                                     first_name, middle_name, last_name, 0, false);

                row[0] = studentID;
                if (userNickName != "failure")
                {
                    row[1] = userNickName + "@" + gusersyn.Admin_domain;
                }
                else
                {
                    row[1] = studentID + "@" + gusersyn.Admin_domain;
                }

                returnvalue.Rows.Add(row);
                row = returnvalue.NewRow();

                log.addTrn("Added Gmail user " + studentID + "@" + 
                      gusersyn.Admin_domain + 
                      " Aliased as " + userNickName + "@" + 
                      gusersyn.Admin_domain, "Transaction");
                // }
            }
            catch (AppsException e)
            {
                log.addTrn("Failed adding Gmail user " + studentID + "@" + 
                  gusersyn.Admin_domain + 
                  " Aliased as " + userNickName + "@" + gusersyn.Admin_domain + 
                  " failed " + e.Message.ToString() + " reason " + 
                  e.Reason.ToString(), "Error");
            }
            catch (Exception ex)
            {
                log.addTrn("Failed adding Gmail user " + studentID + "@" + 
                  gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + 
                  gusersyn.Admin_domain + " failed " + ex.Message.ToString() + 
                  "\n" + ex.StackTrace.ToString(), "Error");
            }
        }
    }
    catch (Exception ex)
    {
        log.addTrn("Issue adding gmail users datareader is null " + 
          ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
    }
    return returnvalue;
}

This portion of code is responsible for taking a SQLDataReader and creating the GMail accounts. It does some simple checks on illegal characters for GMail accounts before creating them. It deals with illegal password lengths by overriding them with a longer 14 char password. Finally, the method builds a datatable with the users which were successfully created, and returns it.

Compiling the Code

The code makes use of the Google Data API 1.7.0.1, and will not compile without it. Install it and ensure that the reference to Google.Gdata points to your installation.

Points of Interest

The most interesting point of this project was taking the time to redesign a lot of it. Each time, I threw away 3/5 of the code I had already written. I spent a lot of time looking at data structures and chasing down speed improvements. The program takes advantage of SQL Server to do most of the processing, and most of the functionality comes from queries.

Code Design

The LDAP driver is split in four main files:

  • Program.cs holds the command line logic.
  • Form1.cs holds the GUI code.
  • utils/toolset.cs holds the entire logic.
  • utils/arguments.cs holds the command line parser.

LDAP Magic Warnings

LDAP Magic Limitations

  • This program depends on the existence of the columns picked in the save data; if column names change, the mappings will have to be remapped.
  • This program depends on the use of (#) temporary tables, and therefore needs read and write capabilities to the selected database.
  • This program attempts to check for empty lists, and handles them robustly. However, there is a high chance if a problem occurs, it will be from a mishandled empty list passed between functions.
  • A custom SQL connection timeout of 360s is created so timeouts will not match the default. Indefinitely held connections could be a problem while the program remains open.
  • There is a large set of created objects, and memory leaks may abound. Most objects are closed; however, some rely on the garbage collector.
  • Many functions in tools are overloaded, and when updating a tool, be careful to make sure each overloaded method will match the new standards.
  • This program has a log file which will tell which function failed when an exception is thrown, and will attempt to add useful data (timestamp, function failed, passed variables).
  • This program must be run by a user with sufficient rights to read and write to AD users and groups.

Issues

  • Unique table naming for multiple instances running at once.
  • Very rarely fails to enable an Active Directory account after creation.

Do NOT

  • Use the (User Mapping) or (Execution Order) tabs, they are broken.
  • Run more than one copy simultaneously unless using temporary tables in the configuration. Each copy will try to access the same tables in the database.

Wish List

  • Preview area for users/groups/accounts
  • AD click to choose OU buttons
  • Fix all outstanding issues

Thanks to

History

  • Code updated on June 28, 2011.
    • Logging completed
    • Improved stability
    • GUI design updated
    • Fixed updating queries
    • Fixed deletion problem in AD
    • Base code for Levenshtein in place but not active - should be easy
    • Included user synch code that allows only updates to pass to AD
    • Updated field handling for Active Directory fields which have special conditions
    • Updated to match new password requirements for the Gdata API
  • Code first released on April 22, 2009.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Systems / Hardware Administrator
United States United States
I am work in lovely Orlando Florida and enjoy multitasking in many different areas of computer work. I have been working with computers since the turn of the century and recently got the chance to do some coding.

Comments and Discussions

 
QuestionLooking for a Co-maintainer Pin
michael.neubrander30-Jun-11 8:43
michael.neubrander30-Jun-11 8:43 
GeneralMy vote of 5 Pin
Filip D'haene30-Jun-11 5:12
Filip D'haene30-Jun-11 5:12 
GeneralRe: My vote of 5 Pin
michael.neubrander30-Jun-11 8:30
michael.neubrander30-Jun-11 8:30 
Thank you, this code has been a long journey and its good to hear someone else can appreciate it!
I'm glad to bring it to the community.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.