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
Select the appropriate SQL Server for your user accounts and logging.
Step 2: Create AD Accounts and Groups
User Setup
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
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
- 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
public SqlDataReader QueryInnerJoin(string table1, string table2,
string pkey1, string pkey2, ArrayList additionalFields,
SqlConnection sqlConn, LogFile log)
{
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
public void CreateUsersAccounts(string ouPath, SqlDataReader users,
string groupDn, string ldapDomain, UserSynch usersyn, LogFile log)
{
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");
newUser.Properties["samAccountName"].Value =
System.Web.HttpUtility.UrlEncode(
users[usersyn.User_sAMAccount].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();
for (i = 0; i < fieldcount; i++)
{
name = users.GetName(i);
if (name != "password" && name != "CN")
{
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];
if (first == last)
{
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
{
}
}
}
}
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;
newUser.Properties["userAccountControl"].Value =
val | (int)accountFlags.ADS_UF_NORMAL_ACCOUNT;
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");
(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");
}
}
}
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");
}
else
{
log.addTrn("issue creating users datareader is null " +
ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
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
public DataTable Create_Gmail_Users(AppsService service,
GmailUsers gusersyn, SqlDataReader users, LogFile log)
{
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
{
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("%", "_");
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 = users[gusersyn.User_password].ToString();
}
else
{
password = GetPassword(14);
}
if (gusersyn.User_password_short_fix_checkbox == true && password.Length < 8)
{
password = GetPassword(14);
}
UserEntry insertedEntry =
service.CreateUser(studentID, first_name, last_name, password);
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.
