User provisioning from Microsoft SQL Server

Overview

Before the Import SQL Data | Create AD Users rule can read data from a SQL Table or View, a connection between Cayosoft Administrator and the Microsoft SQL Server must be established. For details on how to connect to Administrator to Microsoft SQL Server see the Connecting to Microsoft SQL Server data source article.

Rule: Import SQL Data | Create AD Users

This rule will query the specified Microsoft SQL Table or View data source and provision new user accounts according to the Action Section settings. Before the rule will provision users, it must first be configured. The configuration of this rule is almost identical to Text File | Create AD Users.

If the users in the Table or View already exist in Active Directory, on of the following will occur:

1. The user creation will fail with the error that the user account already exists.

2. If the system is not configured to avoid duplicate account create AND Alternative Name Generation rules have been configured – a new user with a unique name will be created.

3. RECOMMENDED: If the system is configured to avoid duplicates, then creation for this user is skipped and the next record in the SQL Table or View is processed. Avoiding duplicate account creation is done by configuring the Skip user if anchor attribute already exists attribute in the More Options section of the rule.

Add the Import SQL Data | Create AD Users Rule to a runbook

1. Select or create a new Runbook.

2. In the Sequence section, click New rule. The New Rule dialog appears.

3. Complete the wizard as follows:

New Rule > Active Directory > Import SQL Data > Import SQL Data | Create AD Users > Next > Next > Finish.

Configure Query section

Specify Data Table or View

• SQL Instance - The name of the SQL Instance as defined in the Utils Extension SQL Server configuration. Using the Default SQL Instance setting will retrieve the current setting from the SQL Connection settings of the UTILS extension.

• SQL Database Name - Allows for the selection of a specific database from selected SQL Instance. Using the Default SQL Database setting will retrieve the current setting from the SQL Connection settings of the UTILS extension.

• SQL Table - Allows for the selection of a specific Table or View from the selected database. Click the Selector button to display a list of tables from which to choose.

• SQL Credentials - Allows for the entry of a specific database from the data source SQL Instance. Click the Selector button to enter SQL Credentials.

NOTE: Cayosoft Administrator does not support Windows Authentication for connecting to a Microsoft SQL Server database. The database connection must use SQL Server Authentication, where the credentials (username and password) are created and managed within SQL Server itself. Mixed Mode refers to the SQL Server configuration that allows both SQL Server Authentication and Windows Authentication, but only SQL Server Authentication is supported by Cayosoft.

More Options

• Where Clause - Defines a where statement in the SQL query sent to the data source to limit the rows returned by SQL Server.

• Filter Data - Allows for the simple creation of a post-query filter to remove additional rows of data from the data returned by the data source.

• Return These SQL Columns - Defines the columns returned by the data source.

  NOTE: For performance reasons, it is recommended that only the essential columns be returned from the data source.

• Execution History Name - Naming reference used to identify the record from the data source in the Cayosoft Administrator Execution history log.

• Skip user if anchor attribute already exists - Because names are likely to have duplicates, a unique value from the data source should be used to determine if records read from the data source have already been processed. The anchor attribute must be unique in the data source. Using the defined Anchor attribute, checks to see if the anchor is already present in Active Directory. If the anchor is present, the user was previously created and the row in the data source is skipped.

• Data Source Anchor attribute - Defines the column in the data source that will be used to determine if the user account already exists. This value is compared to the Active Directory Anchor Attribute.

• Active Directory Anchor attribute - Defines the attribute in AD to which the data source anchor attribute is to be compared. When a new user is created this value also specifies the AD attribute into which the data source anchor is written for comparison the next time the rule is executed.

  NOTE: If the Active Directory attribute you wish to use as the Active Directory Anchor attribute is not displayed, you can enter the ldap name of the attribute in the field. The attribute must be flagged as searchable (https://msdn.microsoft.com/en-us/library/ms679765(v=vs.85).aspx) within Active Directory. To determine if the attribute is flagged as searchable you can use ADSI Edit to view the Schema Objects container and examine the attribute’s searchFlags property.

• Selection button – This button will open a dialog that will allow you to select a table column.

• Preview Button – This button will display a preview of the data retrieved b the Query section.

  NOTE: For efficiency, only the first 300 objects are returned by the preview.

Configure the Action section

• 
  Create in - Determines the initial location within Active Directory, where the new user accounts are to be created.

TIP: For simplicity, Cayosoft recommends that new objects be created in a separate OU then moved to the final location after provisioning is completed. The AD Users | DynamicAttributes™ Relocate AD Users rule can be used to automatically move new user objects to the desired OU after the accounts are fully provisioned.

Account

• Logon Name (SamAccountName) - By default, the SamAccountName is automatically generated from the Data Source assuming the Data Source contains the correctly named fields. If field names are not the same as shown for the selected format, contact Cayosoft for an override format. If the SamAccountName resides in the data source then use the Selector button to pick the desired field. The SamAccountName must be a unique value.

• UPNSuffix - This is the domain name component of the new user’s UserPrincipalName. If you are using Office 365, this value should be set to an a domain that has been registered in Office 365/Azure AD.

• UserPrincipalName - By default, the UserPrincipalName (UPN) is automatically generated from the Data Source assuming the Data Source contains the correctly named fields. If field names are not the same as shown for the selected UPN format, contact Cayosoft for an override format. If the UPN resides in the data source then use the Selector button to pick the desired field. The UPN must be a unique value.

  NOTE: Microsoft best practices assume the Active Directory and Office 365/Azure AD UPNs will match.

• FirstName (GivenName) - If the data source contains a field named FirstName, ignore this setting. Otherwise use the Selector button to choose a field from the data source.

• Last/Sur Name - If the data source contains a field named LastName, ignore this setting. Otherwise use the Selector button to choose a field from the data source.

• Name (cn) - If the data source contains fields named FirstName and LastName, choose the desired format or ignore this setting. Otherwise use the Selector button to choose a field from the data source or contact Cayosoft for an override format.

• Display Name - If the data source contains fields named FirstName and LastName, choose the desired format ignore this setting. Otherwise use the Selector button to choose a field from the data source or contact Cayosoft for an override format.

• Description - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

Settings

• Default Password - Sets the password for the new account. This value can be static text, a field from the data source chosen using the Selection button, set to Generate Random Password. Randomly Generated Passwords will match both the Active Directory Password Complexity Policy, and additional complexity requirements defined in the Cayosoft Administrator Password Complexity Policy.

  NOTE: Static passwords or passwords from data source must meet the Active Directory Password Complexity Policy of the target container or the account will be created in a disabled state.

• Must change password at next logon - Enables/Disables the standard Active Directory user object setting.

• Account enabled - Enables/Disables the standard Active Directory user object setting.

• User cannot change password - Enables/Disables the standard Active Directory user object setting.

• Password never expires - Enables/Disables the standard Active Directory user object setting.

• Account Expiration Date - Sets the Account Expiration attribute in Active Directory.

Organization

• Office - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Job Title (Title) - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Department - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Company - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Employee Number - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• EmployeeID - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Division - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Manager Identifier - Use the Selector button to choose a field from the data source that is a unique identifier for the user’s manager. Typically this will be the Managers EmployeeNumber or EmployeeID.

• AD Attribute for Manager Lookup - Select an Active Directory attribute that is to be used searched for the value of the Manager Identifier specified in the field above.

Contact Info

• Country - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Email Address - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

  NOTE: If alternative SMTP addresses are needed, the rule AD Users | Set Proxy Addresses can be run after the Import SQL Data | Create AD Users rule

• Office Phone (TelephoneNumber) - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Mobile Phone (mobile) - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Street Address - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• City - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• State - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

• Postal Code - If the data source contains a field name as shown, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the data source.

Name generation conflict resolution

• Name conflict resolution - This option determines how the system should be react when the name of a user being created already exists.

• Logon Name (SamAccountName)(Alternative) - Behavior of this attribute is the same as in the Account section.

• UserPrincipalName (Alternative) - Behavior of this attribute is the same as in the Account section.

• Name (cn)(Alternative) - Behavior of this attribute is the same as in the Account section.

• Email Address (Alternative) - Behavior of this attribute is the same as in the Contact Info section.

• Counter Format - This specifies the number of fixed places the counter should have.

• Add counter when - Specifies if a counter should always be added to the user name, or only when name conflicts occur. The counter option is not available when the Use Alternative Generation Rules option is set to “On failure record error in Rule Execution History and go on’.

Other Properties

Additional information in the data source that must be written to Active Directory can be mapped from the data source to AD by entering in a customer mapping in the Other Properties field.