parameters - sorengranfeldt/sqlma GitHub Wiki

The MA primarily reads it's schema from the specified single- and multivalued tables. However, you can to define a few things for the MA to operate to your specific needs.

IMPORTANT - Your tables must contain at least one record for the schema detection to work properly.

On the Connectivity tab of the MA, you need to define the different parameters and the value of the different settings determine the function and capabilities of the MA -

• Authentication Type - Specifies whether authentication for SQL server should use SQL authentication or Windows authentication.
• Connection String - The connection string contains the information that the provider need to know to be able to establish a connection to the database or the data file.
  • The MA will replace {username} and {password} with the values from the parameters with the same name (see below). For now only SQL authentication is supported, however you can use Windows authentication by specifying that in the connection string. If you so choose, you should allow the service account running the Synchronization Service access to your SQL tables. Later version will support impersonating a different user for Windows authentication. You can find sample connection string at https://www.connectionstrings.com/
  • If the password contains special characters, you can encapsulate your password in single quotes. e.g. given the password iloveachallenge; your connection string should contain Password='iloveachallenge;';, i.e. use '{password}' to have the replaced password be encapsulated in single quotes.
• Username - this is the username for connecting to the database server
• Password - this is the password for the username that connects to the database server
• Domain - this is the Windows NetBIOS Domain Name for connecting to the database server (only used for Windows authentication)
• Tablename - this is the name of the single-value tablename.
• Tablename (multivalue) - Optional. This is the name of the multi-value tablename.
• Object class - this is a dropdown where you specify whether the object class value is fixed or column-based (a value from a specific column in the single-value table).
• Object class (name or column) - this is either the name of the fixed object class or the name of the column in the single-value table that has the object class value.
• Anchor column - this is the column name of the ID or anchor column. It's recommend to index this column and to use a column of type of 'string', 'uniqueidentifier' or 'number' for the anchor column.
• DN column - if you want a different DN that the anchor value for your objects, then specify the column name here. If you want to use the anchor value, specify the same columnd name as for the anchor.
• IsDeleted column - Optional. if you want to support delta deletes with this MA, you must have a column in your single-value table of type 'bit' and specify the name of that column here. If you leave this blank, the MA will not and cannot handle delta deletes and instead do physical deletes from your tables. If you have a multivalue table and want to support delta deletes, a column with the same name must exist in your multivalue table as well.
• Multivalue anchor reference column - Optional. This is the name column that holds the backreference ID/anchor to the single-value object table. This must be of the same type as ID/anchor column in the single-value table. You only need to specify this value if you are using a multivalue table.
• Delta column type - Optional. This is a dropdown where you specify the type of delta value that your data tables use. You can select between 'Rowversion' or 'DateTime'. This value is only used if you specify a value in the 'Delta column name' (see below)
• Delta column name - Optional. This is the name of the column that has the value indicating whether a specific row has changed. This column must be of either type 'timestamp' (recommended) or 'datetime'. This is only required if you want to support delta imports.
• Date format - Required. This date format is used to format values from any date or datetime columns in the tables. You can use any date format specifications found in the .NET Framework.
• XML configuration - using a specific XML format, you can override schema values (see more below)

Below there are two samples of connection string that can be used for either SQL authentication or Windows authentication -

• Windows authentication - Server=sql01.contoso.com;Database=test;Trusted_Connection=True;
• SQL authentication - data source=sql01.contoso.com;initial catalog=test;persist security info=true;user id={username};password={password};multipleactiveresultsets=true

For more samples for connection string, please see https://www.connectionstrings.com/

With the schema XML, you can override the defaults that the MA sets for the different columns and you can exclude (remove) some columns from selected objectclasses.

Allowed override type for the schematype value are -

• reference
• string
• binary
• boolean
• integer

NOTE - It is your responsibility to make sure that the values in the column that you choose to override, supports the type conversion, i.e. from a string type column to a number value. Also, if you have multiple object class sharing the same attribute, you need to specify the type override for each object class in the XML.

Below is a sample XML with configuration for two different object classes, person and group. As you can see below the column (attribute) managerid is changed to be of type reference and the column (attribute) organizationalid is overridden to be of type string. Also, for the person object class a few columns are excluded as those columns from the single-value table only relates to the group objectclass. Below there is a similar setup for the group object class using a similar pattern. Please note that if two or more objecttypes share an attribute with a type override the override must be specified for both type - otherwise schema detection will fail.

<configuration>
 <objectclasses>
  <objectclass name='person'>
   <overrides>
    <attribute name='managerid' schematype='reference' />
    <attribute name='organizationalid' schematype='string' />
   </overrides>
   <excludes>
    <attribute name='export_password'/>
    <attribute name='grouptype'/>
    <attribute name='member'/>
    <attribute name='managedby'/>
   </excludes>
  </objectclass>

  <objectclass name='group'>
   <overrides>
    <attribute name='managedby' schematype='reference' />
   </overrides>
   <excludes>
    <attribute name='managerid'/>
    <attribute name='export_password'/>
    <attribute name='firstname'/>
    <attribute name='lastname'/>
    <attribute name='proxyaddresses'/>
    </excludes>
  </objectclass>
 </objectclasses>
</configuration>