Step 7: Create the SQL_Azure DLL and Connector
Creating and configuring the SQL_ECMA2 Extensible Connectivity 2.0 Management Agent for the test lab consists of the following:
Create SQL_Azure Connector DLL
Create the SQL_Azure Connector
Configure the SQL_Azure Run Profiles
Create SQL_Azure Connector DLL
Now we will create our SQL_Azure DLL for our SQL_Azure management agent. This will be done using the code provided.
To create the connector DLL
Log on to FIM1 as CORP\Administrator.
Click Start, select All Programs, select Microsoft Forefront Identity Manager and double-click Synchronization Service.
In the Synchronization Service, at the top, select Management Agents.
At the top, select Actions, select Create Extension Projects, and choose Extensible Connectivity 2.0 Extension. This will open a Create Extension Project dialog box.
In the Create Extension Project dialog box, next to Programming Language: select Visual C#
In the Create Extension Project dialog box, next to Visual Studio Version: select Visual Studio 2010.
In the Create Extension Project dialog box, next to Project Name: enter SQL_Azure.
Click OK. This will launch Visual Studio 2010.
Note
If this is the first time running Visual Studio you will receive a Choose Default Environmental Settings Dialog box. Select General Development Settings and click Start Visual Studio.
On the right, at the top, under the Solutions Explorer, double-click SQL_Azure.cs. This will open the file with code that has already been written.
Remove all of the code in SQL_Azure.cs and copy the code below into SQL_Azure.cs. The code should look similar to below:
//******************************************************** //* * //* Copyright (C) Microsoft. All rights reserved. * //* * //******************************************************** using System; using System.IO; using System; using System.IO; using System.Xml; using System.Text; using System.Collections.Specialized; using System.Collections.Generic; using System.Collections.ObjectModel; using Microsoft.MetadirectoryServices; using System.Data.SqlClient; using System.Data; namespace FimSync_Ezma { public class EzmaExtension : IMAExtensible2CallExport, IMAExtensible2CallImport, //IMAExtensible2FileImport, //IMAExtensible2FileExport, //IMAExtensible2GetHierarchy, IMAExtensible2GetSchema, IMAExtensible2GetCapabilities, IMAExtensible2GetParameters //IMAExtensible2GetPartitions { // // constansts // private int m_importDefaultPageSize = 12; private int m_importMaxPageSize = 50; private int m_exportDefaultPageSize = 10; private int m_exportMaxPageSize = 20; public string myConnection; public string myDB; public string myServer; public string myTable; public string myUsername; System.Security.SecureString myPassword; SqlConnection conn; SqlCommand cmd; SqlDataAdapter adapter; DataSet da; public string myFirst; public string myLast; public string myEmail; public string myEmpID; public string myFull; public string myAccount; // // Constructor // public EzmaExtension() { // // TODO: Add constructor logic here // } // // The connector capabilities. // public MACapabilities Capabilities { get { MACapabilities myCapabilities = new MACapabilities(); myCapabilities.ConcurrentOperation = true; myCapabilities.ObjectRename = false; myCapabilities.DeleteAddAsReplace = true; myCapabilities.DeltaImport = false; myCapabilities.DistinguishedNameStyle = MADistinguishedNameStyle.None; myCapabilities.ExportType = MAExportType.AttributeUpdate; myCapabilities.NoReferenceValuesInFirstExport = false; myCapabilities.Normalizations = MANormalizations.None; return myCapabilities; } } // //The parameters of our connector. This will allow us to take input for the SQL database at the time the management agent is installed. // public IList<ConfigParameterDefinition> GetConfigParameters(KeyedCollection<string, ConfigParameter> configParameters, ConfigParameterPage page) { List<ConfigParameterDefinition> configParametersDefinitions = new List<ConfigParameterDefinition>(); switch (page) { case ConfigParameterPage.Connectivity: configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Server", "")); configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Database", "")); configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Table", "")); configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("UserName", "")); configParametersDefinitions.Add(ConfigParameterDefinition.CreateEncryptedStringParameter("Password", "")); break; case ConfigParameterPage.Global: break; case ConfigParameterPage.Partition: break; case ConfigParameterPage.RunStep: break; } return configParametersDefinitions; } // //This is the method where we would configure code to validate the configuration parameters. //For purposes of this test lab we don’t validate the parameters but we include this method because it is required to implement IMAExtensible2GetParameters. // public ParameterValidationResult ValidateConfigParameters(KeyedCollection<string, ConfigParameter> configParameters, ConfigParameterPage page) { ParameterValidationResult myResults = new ParameterValidationResult(); return myResults; } // //This will be used to discover our schema while the management agent is being setup. //The code below will connect to our HR database and query for the column names. //It will set the anchor attribute to EmployeeID and then the other column names will be used as single-valued attributes. // public Schema GetSchema(KeyedCollection<string, ConfigParameter> configParameters) { Microsoft.MetadirectoryServices.SchemaType personType = Microsoft.MetadirectoryServices.SchemaType.Create("Person", false); myServer = configParameters["Server"].Value; myServer = "tcp:" + myServer + ".database.windows.net"; myDB = configParameters["Database"].Value; myTable = configParameters["Table"].Value; myUsername = configParameters["UserName"].Value; myPassword = configParameters["Password"].SecureValue; DataSet myData = this.SQLSchema(myServer, myDB, myTable, myUsername, myPassword); string[] SQLSchema = new string[myData.Tables["Columns"].Rows.Count]; for (int i = 0; i <= myData.Tables["Columns"].Rows.Count - 1; i++) { SQLSchema[i] = myData.Tables["Columns"].Rows[i].ItemArray.GetValue(0).ToString().Trim(); string myattrib = SQLSchema[i]; if (myattrib == "EmployeeID") { personType.Attributes.Add(SchemaAttribute.CreateAnchorAttribute(myattrib, AttributeType.String)); } else { personType.Attributes.Add(SchemaAttribute.CreateSingleValuedAttribute(myattrib, AttributeType.String)); } } Schema schema = Schema.Create(); schema.Types.Add(personType); return schema; } // //This dataset is used in our schema discovery. //It basically reads our SQL table columns and returns these columns as a dataset to the GetSchema method. //The GetSchema method then loops through the dataset and creates the attributes for the schema based on the contents of the dataset. // public DataSet SQLSchema(string server, string database, string table, string user, System.Security.SecureString pass) { SqlConnectionStringBuilder myConString = new SqlConnectionStringBuilder(); myConString.DataSource = server; myConString.InitialCatalog = database; myConString.Encrypt = true; myConString.TrustServerCertificate = false; myConString.UserID = user; IntPtr ptr = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(pass); myConString.Password = System.Runtime.InteropServices.Marshal.PtrToStringUni(ptr); string conn2 = myConString.ToString(); conn = new SqlConnection(myConString.ToString()); cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; string cmdText = "Select COLUMN_NAME from Information_Schema.Columns where TABLE_Name = '" + table + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; adapter = new SqlDataAdapter(cmd); da = new DataSet(); adapter.Fill(da, "Columns"); return da; } // //This will initialize our connection information with the SQL database that will be used during imports. // public OpenImportConnectionResults OpenImportConnection( KeyedCollection<string, ConfigParameter> configParameters, Schema types, OpenImportConnectionRunStep importRunStep) { myServer = configParameters["Server"].Value; myServer = "tcp:" + myServer + ".database.windows.net"; myDB = configParameters["Database"].Value; myTable = configParameters["Table"].Value; myUsername = configParameters["UserName"].Value; myPassword = configParameters["Password"].SecureValue; SqlConnectionStringBuilder myConString = new SqlConnectionStringBuilder(); myConString.DataSource = myServer; myConString.InitialCatalog = myDB; myConString.Encrypt = true; myConString.TrustServerCertificate = false; myConString.UserID = myUsername; IntPtr ptr = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(myPassword); myConString.Password = System.Runtime.InteropServices.Marshal.PtrToStringUni(ptr); conn = new SqlConnection(myConString.ToString()); cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; string cmdText = "Select * from " + myTable; cmd.CommandText = cmdText; cmd.Connection = conn; return new OpenImportConnectionResults(); } // //This is the workhorse of a call-based MA with regard to importing. //This method creates a dataset and populates it by selecting all of the data from the HR table. //Then the code loops through the dataset and sets string values for the information in the dataset. //These strings are used to set our attribute values. //Once we have these values, we create a new Person object and set the attributes of the Person object to what is contained in our strings. //Then the person added to our list of csentries. //Once all of the data from SQL is created and added to the list, the list is returned to the Synchronization Service. // public GetImportEntriesResults GetImportEntries(GetImportEntriesRunStep importRunStep) { adapter = new SqlDataAdapter(cmd); da = new DataSet(); adapter.Fill(da, "Employees"); GetImportEntriesResults importReturnInfo; List<CSEntryChange> csentries = new List<CSEntryChange>(); for (int i = 0; i <= da.Tables["Employees"].Rows.Count - 1; i++) { myFirst = da.Tables["Employees"].Rows[i].ItemArray.GetValue(1).ToString().Trim(); myLast = da.Tables["Employees"].Rows[i].ItemArray.GetValue(2).ToString().Trim(); myEmail = da.Tables["Employees"].Rows[i].ItemArray.GetValue(3).ToString().Trim(); myEmpID = da.Tables["Employees"].Rows[i].ItemArray.GetValue(4).ToString().Trim(); myFull = da.Tables["Employees"].Rows[i].ItemArray.GetValue(5).ToString().Trim(); myAccount = da.Tables["Employees"].Rows[i].ItemArray.GetValue(6).ToString().Trim(); CSEntryChange csentry1 = CSEntryChange.Create(); csentry1.ObjectModificationType = ObjectModificationType.Add; csentry1.ObjectType = "Person"; csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("FirstName", myFirst)); csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("LastName", myLast)); csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("EMail", myEmail)); csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("EmployeeID", myEmpID)); csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("FullName", myFull)); csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("AccountName", myAccount)); csentries.Add(csentry1); } importReturnInfo = new GetImportEntriesResults(); importReturnInfo.MoreToImport = false; importReturnInfo.CSEntries = csentries; return importReturnInfo; } // //This will close our import connection. // public CloseImportConnectionResults CloseImportConnection(CloseImportConnectionRunStep importRunStepInfo) { conn.Close(); return new CloseImportConnectionResults(); } // //Returns our maximum import page size. // public int ImportMaxPageSize { get { return m_importMaxPageSize; } } // //Returns our default import page size. // public int ImportDefaultPageSize { get { return m_importDefaultPageSize; } } // //Opens our export connection. // public void OpenExportConnection(KeyedCollection<string, ConfigParameter> configParameters, Schema types, OpenExportConnectionRunStep exportRunStep) { myServer = configParameters["Server"].Value; myServer = "tcp:" + myServer + ".database.windows.net"; myDB = configParameters["Database"].Value; myTable = configParameters["Table"].Value; myUsername = configParameters["UserName"].Value; myPassword = configParameters["Password"].SecureValue; SqlConnectionStringBuilder myConString = new SqlConnectionStringBuilder(); myConString.DataSource = myServer; myConString.InitialCatalog = myDB; myConString.Encrypt = true; myConString.TrustServerCertificate = false; myConString.UserID = myUsername; IntPtr ptr = System.Runtime.InteropServices.Marshal.SecureStringToBSTR(myPassword); myConString.Password = System.Runtime.InteropServices.Marshal.PtrToStringUni(ptr); conn = new SqlConnection(myConString.ToString()); cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; conn.Open(); } // //This is the workhorse of a call-based MA with regard to exporting. //This method is responsible for writing our information to SQL. //This is done by taking the csentries that are passed in by the Synchronization Service and depending on the type of modification, whether it is object or attribute, //a corresponding T-SQL statement is built and then executed on the database. // public PutExportEntriesResults PutExportEntries(IList<CSEntryChange> csentries) { int i = 0; foreach (CSEntryChange csentryChange in csentries) { myEmpID = csentries[i].DN.ToString(); if (csentryChange.ObjectType == "Person") { #region Add if (csentryChange.ObjectModificationType == ObjectModificationType.Add) { #region a foreach (string attrib in csentryChange.ChangedAttributeNames) { switch (attrib) { case "FirstName": // myFirst = csentryChange.ChangedAttributeNames[0].ToString(); myFirst = csentryChange.AttributeChanges["FirstName"].ValueChanges[0].Value.ToString(); break; case "LastName": // myLast = csentryChange.ChangedAttributeNames[0].ToString(); myLast = csentryChange.AttributeChanges["LastName"].ValueChanges[0].Value.ToString(); break; case "EMail": // myEmail = csentryChange.ChangedAttributeNames[0].ToString(); myEmail = csentryChange.AttributeChanges["EMail"].ValueChanges[0].Value.ToString(); break; case "EmployeeID": //myEmpID = csentryChange.AttributeChanges["EmpoyeeID"].ValueChanges[0].Value.ToString(); break; case "AccountName": myAccount = csentryChange.AttributeChanges["AccountName"].ValueChanges[0].Value.ToString(); break; case "FullName": myFull = csentryChange.AttributeChanges["FullName"].ValueChanges[0].Value.ToString(); break; } } #endregion string cmdText = "Insert into " + myTable + "(FirstName, LastName, EMail, EmployeeID, FullName, AccountName) VALUES ('" + myFirst + "','" + myLast + "','" + myEmail + "','" + myEmpID + "','" + myFull + "','" + myAccount + "')"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } #endregion #region Delete if (csentryChange.ObjectModificationType == ObjectModificationType.Delete) { myEmpID = csentries[i].DN.ToString(); string cmdText = "Delete from " + myTable + "Where EmployeeID = '" + myEmpID + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } #endregion #region Update if (csentryChange.ObjectModificationType == ObjectModificationType.Update) { foreach (string attribName in csentryChange.ChangedAttributeNames) { if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Add) { myEmpID = csentryChange.AnchorAttributes[0].Value.ToString(); string attribValue = csentryChange.AttributeChanges[attribName].ValueChanges[0].Value.ToString(); string cmdText = "Update" + myTable + "SET" + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Delete) { myEmpID = csentryChange.AnchorAttributes[0].Value.ToString(); string cmdText = "Update " + myTable + " SET " + attribName + " = 'NULL' Where EmployeeID = '" + myEmpID + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Replace) { myEmpID = csentryChange.AnchorAttributes[0].Value.ToString(); string attribValue = csentryChange.AttributeChanges[attribName].ValueChanges[0].Value.ToString(); string cmdText = "Update " + myTable + " SET " + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Update) { string attribValue; foreach (ValueChange valueChange in csentryChange.AttributeChanges[attribName].ValueChanges) { if (valueChange.ModificationType == ValueModificationType.Add) { attribValue = valueChange.Value.ToString(); break; } } myEmpID = csentryChange.AnchorAttributes[0].Value.ToString(); string cmdText = "Update " + myTable + " SET " + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'"; cmd.CommandText = cmdText; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } } #endregion } i++; } PutExportEntriesResults exportEntriesResults = new PutExportEntriesResults(); return exportEntriesResults; } // //Closes our export connection. // public void CloseExportConnection(CloseExportConnectionRunStep exportRunStep) { conn.Close(); } // //Returns our default export page size. // public int ExportDefaultPageSize { get { return m_exportDefaultPageSize; } set { m_exportDefaultPageSize = value; } } // //Returns our maximum export page size. // public int ExportMaxPageSize { get { return m_exportMaxPageSize; } set { m_exportMaxPageSize = value; } } }; }At the top, select Build and choose Build Solution from the drop-down. At the bottom, in the Output window you should see Build: 1 succeeded.
Close Visual Studio.
Create the SQL_Azure Connector
Now we will create the SQL_Azure connector in the synchronization service.
To create the SQL_Azure Connector
Back in the Synchronization Service, click Management Agents and
In the Synchronization Service, at the top, select Management Agents and over on the right, under Actions, select Create. This will open a Create Management Agent wizard.
On the Create Management Agent screen, next to Management Agent for: select Extensible Connectivity 2
On the Create Management Agent screen, next to Name: enter SQL_Azure
.png "SQL Azure MA 1")
Remove the check from Run this management agent in a separate process. This will allow for debugging should the need arise.
Click Next.
On the Select Extension DLL screen, click Browse and select SQL_Azure.dll. Click OK.
On the Select Extension DLL screen, click Refresh interfaces. This will populate the box below. It should support Import, Full Import, and Export operations.
.png "SQL Azure MA 2")
Click Next.
On the Connectivity screen, next to Server enter: <the name of the SQL Azure database server>.
On the Connectivity screen, next to Database enter: CONTOSO.
On the Connectivity screen, next to Table enter: HR.
On the Connectivity screen, next to UserName enter: sa_fim.
On the Connectivity screen, next to Password enter: Pass1word$
.png "SQL Azure MA 3")
Click Next.
On the Configure Partitions and Hierarchies screen, leave the defaults.
.png "SQL Azure MA 4")
Click Next.
On the Select Object Types screen, select Person.
.png "SQL Azure MA 5")
Click Next.
On the Select Attributes screen, select all seven.
.png "SQL Azure MA 6")
Click Next.
On the Configure Anchors screen, click Specify Anchor. This will open a Set Anchor dialog box.
Under Selected attributes, make sure EmployeeID is selected. Click OK.
.png "SQL Azure MA 7")
Click Next.
On the Configure Connector Filter screen, click Next.
.png "SQL Azure MA 8")
On the Configure Join and Projection Rules screen, click New Projection Rule. This will open a Projection dialog box.
On the Projection dialog box, verify Declared is selected.
On the Projection dialog box, verify Person is in the box next to Metaverse object type.
Click OK.
.png "SQL Azure MA 9")
Click Next.
On the Configure Attribute Flow screen, from the drop-down list under Data source object type, select Person.
On the Configure Attribute Flow screen, from the drop-down list under Metaverse object type list, select person.
On the Configure Attribute Flow screen, for Mapping Type, select Direct.
On the Configure Attribute Flow screen, from the list below Data source attribute, select FirstName.
On the Configure Attribute Flow screen, from the list below Metaverse attribute, select givenName.
On the Configure Attribute Flow screen, for Flow Direction, select Import. Ensure that Allow Nulls is not selected. Click New.
Repeat the above steps for each of the attribute entries in the following table.
Important
Be sure to change the Flow Direction where applicable. Also ensure that Allow Nulls is not checked.
Table 1 – Attribute Flow
Data Source Attribute Flow Direction Metaverse attribute AccountName
Import
accountName
EMail
Import
mail
FirstName
Import
firstName
FullName
Import
displayName
EmployeeID
Import
employeeID
LastName
Import
lastName
AccountName
Export
accountName
EMail
Export
mail
FirstName
Export
firstName
FullName
Export
displayName
LastName
Export
lastName
.png "SQL Azure MA 10")
Click Next.
On the Configure Deprovisioning screen, select Make them disconnectors and click Next.
.png "SQL Azure MA 11")
On the Configure Extensions screen click Finish.
.png "SQL_ECMA_12")
Configure the SQL_Azure Run Profiles
Now that the SQL_Azure has been created, you will need to create run profiles for the management agent.
To configure the SQL_Azure Run Profiles
In the Synchronization Service, on the right, under Actions menu, click Configure Run Profiles. This opens the Configure run Profiles window.
Click New Profile. This will begin the Configure Run Profile wizard.
On the Profile Name page, in the text box under Name, type the following, and then click Next:
Full ImportOn the Configure Step page, from the drop-down list under Type, select Full Import (Stage Only), and then click Next.
On the Management Agent Configuration. Click Finish.
Click New Profile.
On the Profile Name page, in the text box under Name, type the following, and then click Next:
Full SynchronizationOn the Configure Step page, from the drop-down list under Type, select Full Synchronization, and then click Next.
On the Management Agent Configuration page click Finish.
Click Finish.
Click New Profile.
On the Profile Name page, in the text box under Name, type the following, and then click Next:
Delta SynchronizationOn the Configure Step page, from the drop-down list under Type, select Delta Synchronization, and then click Next.
On the Management Agent Configuration page click Finish.
Click Finish.
Click New Profile.
On the Profile Name page, in the text box under Name, type the following, and then click Next:
ExportOn the Configure Step page, from the drop-down list under Type, select Export, and then click Next.
On the Management Agent Configuration page click Finish.
Click Finish.