The Microsoft SQL Stored Procedure Connector performs password management functions for a Microsoft SQL Server database by running existing stored procedures and supports both Provisioning and Identity.

The Provisioning functionalities of this connector enable exporting and importing user accounts on a Microsoft SQL Server system.
The Identity functionalities of this connector enable you as an Identity administrator to configure Microsoft SQL Stored Procedure as a connected system and then make Identity users part of the Microsoft SQL Stored Procedure system. This enables the user or Identity administrator to reset Microsoft SQL Stored Procedure account passwords. This also enables you to enable and disable user accounts.

Functionalities

Identity Integration

Product Feature	Supported
Authenticate (Test Connection)	Yes
Validate User	Yes
Enable/Disable User	Yes
Reset Password	Yes
Expire Password Immediately	Yes
Expire Password by Date	Yes

Provisioning Integration

Data Format	Export	Create	Modify	Delete	Trigger
Tables	Yes	Yes	Yes	Yes	No
Stored Procedure	No	Yes	Yes	Yes	No

Prerequisites

Ensure that these prerequisites are satisfied:

Microsoft SQL Server Version 2000 or 2005 is installed, configured, and running.
An administrator account that can be used to establish a connection and has authority to manage accounts on the connected system.

Creating the Connected System in the Admin UI

Log in to Identity Administration and click the Systems tab.
On the Connected System View page, click the Add button and select the Microsoft SQL Server connected system from the Type drop-down list. The Connected System Details page displays the default values::

Enter the desired information:

Definition
Supported Connectors	Displays whether the connected system is Identity only, Provisioning only, or both.
Password Policy	Displays the name of the password policy associated with the connected system.
Connected System Group	Displays the name of the system group that includes this connected system. Note: If a password policy is associated with a connected system and then the connected system is placed in a group, the group’s password policy will override the connected system’s password policy. The password policy will be removed from the connected system.
Type	Select the connected system type.
Locale	Select the preferred language (default: English). Locale specific information such as Display Name and Description can be added only while modifying the connected system.
Name	The name for this connected system. Note: The name cannot be modified later.
Display Name	The display name of the new connected system.
Description	The description of the connected system.
Associated With	Select how the connector associated with this system will run: Server (default) - Runs locally on the Provisioning/Identity Server. Global Identity Gateway - Runs remotely on a Global Identity Gateway cluster member. Note: Only GIG clusters that have at least one registered and enabled member will display in this list. See Using the Global Identity Gateway with Connected Systems for additional information.
Password Reset By	Enables administrators to configure password management functions normally available to Users and OBO (On Behalf Of) Users: OBO User Only - Connected system and account association information is displayed only in Self-Service user management (for OBO Users). OBO Users can reset passwords for accounts on this connected system. Administrators can perform all user management functions for this connected system (e.g., enable/disable, validate, associate user, and password reset). End users will not see their accounts on this connected system in Self-Service and Kiosk; therefore, they cannot reset passwords for accounts on this connected system. Users and OBO User - Connected system and account association information is displayed in Self-Service password reset, Self-Service - Kiosk, and Self-Service user management. Self-Service users, Kiosk users, and OBO Users can reset passwords for accounts on this connected system. Administrators can perform all user management functions for this connected system (e.g., enable/disable, validate, associate user, and password reset). External - Connected system and account association information is not displayed in Self-Service password reset, Self-Service - Kiosk, and Self-Service user management. Self-Service users, Kiosk users, and OBO Users cannot reset passwords for accounts on this connected system. Note: When user management configuration enables OBO Users to perform password resets, this definition must be set to OBO User Only or Users and OBO User. For connectors that support Provisioning only, there is no password reset capability.
Provisioning Option	Select the provisioning option: Automated (default) - The connected system functions as a normal connected system; there are no restrictions. Administrative - The connected system cannot be used as an object in a workflow.
Enable HPAM Support	Select to make the connected system HPAM enabled (default: cleared). Note: This can only be set for systems that support Identity.
Connection Information
Host	The IP address or host name of the server (e.g., 10.102.200.20 or localhost).
Port	The database port number.
Service Account Name	The name of the administrative user account used to connect to the server.
Service Account Password	The administrative user password.
Initial DB	The SID or Service Name to connect.
Maximum Connection Pool Size	Select the maximum number of connections that can be created in the connection pool by the connector. As needed, the connection pool will grow only to this maximum limit.
Stored procedure to reset password with/without expiry
Name	Specify the stored procedure to set the password with these supporting parameters (separated by %): LOGIN_ID PASSWORD SERVICE_ACCOUNT_PASSWORD PASSWORD_EXP_DATETIME ERROR_MESSAGE EXPIRE_IMMEDIATE Notes: While executing the reset password, LOGIN_ID, PASSWORD, SERVICE_ACCOUNT_PASSWORD, PASSWORD_EXP_DATETIME, and EXPIRE_IMMEDIATE parameters are substituted by Account ID, Password, Service Account Password, Expiration Date, and Expire Password respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to enable a user
Name	Specify the stored procedure used to enable a user with these supporting parameters (separated by %): LOGIN_ID ENABLE_DISABLE ERROR_MESSAGE Notes: While executing the enable user, LOGIN_ID and ENABLE_DISABLE parameters are substituted by Account ID and True respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to disable a user
Name	Specify the stored procedure used to disable a user with these supporting parameters (separated by %): LOGIN_ID ENABLE_DISABLE ERROR_MESSAGE Notes: While executing the disable user, LOGIN_ID and ENABLE_DISABLE parameters are substituted by Account ID and False respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to check validity of a user
Name	Specify the stored procedure used to validate a user with these supporting parameters (separated by %): · LOGIN_ID · ERROR_MESSAGE · RESULT Notes: While executing the validate user, the LOGIN_ID parameter is substituted by Account ID. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure. RESULT is an output parameter used to return the validation status of a user. It must be set within the stored procedure as follows: If the user was valid (found), set it to the value specified in Valid user return code. If the user was invalid (not found), set it to the value specified in Invalid user return code.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Valid user return code	The return code of the stored procedure when it has validated a user.
Invalid user return code	The return code of the stored procedure when it has invalidated a user.
Stored procedure to authenticate a user
Name	Specify the stored procedure used to authenticate a user with these supporting parameters (separated by %): LOGIN_ID PASSWORD ERROR_MESSAGE Notes: While executing the authenticate user, LOGIN_ID and PASSWORD parameters are substituted by Account ID and Password respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Password Expiration Support
Expiration Options For Admin/OBO User Password Reset	Specify the password expiration: None, Immediate, or Immediate with Date. Note: If Immediate with Date is selected, Immediate is also available.
System Owner	Add or Remove users assigned as the owners of the system. Displays the Connected System Owner Search page for selecting users. The HPAM column indicates whether the system owner is authorized to use the HPAM feature. The Approvers column indicates whether the system owner is an approver in the approval process.
Add PswdPolicy / Remove PswdPolicy	Adds/removes a password policy to/from this connected system. If the connected system is associated with a Connected System Group, the buttons will be unavailable - all password policy assignments are defined at the group level (refer to Admin UI _ Systems _ Groups option).

Click the Test Connection button to test the Connection Information:
- If successful, one or both of these messages may display:
Message: Connection from Identity to the connected system was established successfully.
- If unsuccessful, one or both of these messages may display:
Error: Failed to establish connection from Identity to the connected system.

Note: If the connection fails, additional messages may display providing more information regarding the failure, and additional information may be posted to the Provisioning and Identity logs.
(Optional) To select owners of the system, click the System Owner Add button. The Connected System Owner Search page displays:
1. Select the owners and then click the Select button. The system owner displays under the System Owner section:
  
  Note: More than one user can be assigned as an owner.
2. To add additional system owners, click the Add button.
On the Connected System Details page, click the Add button to save the configured connected system. The Object Category Association page displays a list of categories that are already associated and/or can be selected to add additional associations to this connected system:
1. Select one or more available object categories or provide search criteria and click the Search button to find specific categories to select. If there are no available categories to select, proceed to Step 7.
2. Click the Add Association button to associate the selected object categories to the connected system.
Click the Back button to return to the Connected System View page. The new connected system displays in the list.

See Copying, Modifying, and Deleting Connected Systems for additional information.

Creating the Connected System in the Studio

Log in to the Workflow and Connectivity Studio and click Connectivity ► Add Systems on the menu bar. The Add Connected Systems window displays.
Select the Microsoft SQL Stored Procedure connected system from the Type drop-down list. The default values display:

.Enter the desired information:

Definition
Type	Select the connected system type.
Name	The name for this connected system. Note: The name cannot be modified later.
Display Name	The display name of the new connected system.
Description	The description of the connected system.
Supported Connectors	Displays whether the connected system is Identity only, Provisioning only, or both. Only connectors that support Provisioning are available here.
Associated With	Select how the connector associated with this system will run: Server (default) - Runs locally on the Provisioning/Identity Server. Global Identity Gateway - Runs remotely on a Global Identity Gateway cluster member. Note: Only GIG clusters that have at least one registered and enabled member will display in this list. See Using the Global Identity Gateway with Connected Systems for additional information.
Password Reset By	Enables administrators to configure password management functions normally available to Users and OBO (On Behalf Of) Users: OBO User Only - Connected system and account association information is displayed only in Self-Service user management (for OBO Users). OBO Users can reset passwords for accounts on this connected system. Administrators can perform all user management functions for this connected system (e.g., enable/disable, validate, associate user, and password reset). End users will not see their accounts on this connected system in Self-Service and Kiosk; therefore, they cannot reset passwords for accounts on this connected system. Users and OBO User - Connected system and account association information is displayed in Self-Service password reset, Self-Service - Kiosk, and Self-Service user management. Self-Service users, Kiosk users, and OBO Users can reset passwords for accounts on this connected system. Administrators can perform all user management functions for this connected system (e.g., enable/disable, validate, associate user, and password reset). External - Connected system and account association information is not displayed in Self-Service password reset, Self-Service - Kiosk, and Self-Service user management. Self-Service users, Kiosk users, and OBO Users cannot reset passwords for accounts on this connected system. Note: When user management configuration enables OBO Users to perform password resets, this definition must be set to OBO User Only or Users and OBO User. For connectors that support Provisioning only, there is no password reset capability.
Provisioning Option	Select the provisioning option: Automated (default) - The connected system functions as a normal connected system; there are no restrictions. Administrative - The connected system cannot be used as an object in a workflow.
Enable HPAM Support	Select to make the connected system HPAM enabled (default: cleared). Note: This can only be set for systems that support Identity.
Connection Information
Host	The IP address or host name of the server (e.g., 10.102.200.20 or localhost).
Port	The database port number.
Service Account Name	The name of the administrative user account used to connect to the server.
Service Account Password	The administrative user password.
Initial DB	The SID or Database Name to connect.
Maximum Connection Pool Size	Select the maximum number of connections that can be created in the connection pool by the connector. As needed, the connection pool will grow only to this maximum limit.
Stored procedure to reset password with/without expiry
Name	Specify the stored procedure to set the password with these supporting parameters (separated by %): LOGIN_ID PASSWORD SERVICE_ACCOUNT_PASSWORD PASSWORD_EXP_DATETIME ERROR_MESSAGE EXPIRE_IMMEDIATE Notes: While executing the reset password, LOGIN_ID, PASSWORD, SERVICE_ACCOUNT_PASSWORD, PASSWORD_EXP_DATETIME, and EXPIRE_IMMEDIATE parameters are substituted by Account ID, Password, Service Account Password, Expiration Date, and Expire Password respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to enable a user
Name	Specify the stored procedure used to enable a user with these supporting parameters (separated by %): LOGIN_ID ENABLE_DISABLE ERROR_MESSAGE Notes: While executing the enable user, LOGIN_ID and ENABLE_DISABLE parameters are substituted by Account ID and True respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to disable a user
Name	Specify the stored procedure used to disable a user with these supporting parameters (separated by %): LOGIN_ID ENABLE_DISABLE ERROR_MESSAGE Notes: While executing the disable user, LOGIN_ID and ENABLE_DISABLE parameters are substituted by Account ID and False respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Stored procedure to check validity of a user
Name	Specify the stored procedure used to validate a user with these supporting parameters (separated by %): · LOGIN_ID · ERROR_MESSAGE · RESULT Notes: While executing the validate user, the LOGIN_ID parameter is substituted by Account ID. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure. RESULT is an output parameter used to return the validation status of a user. It must be set within the stored procedure as follows: If the user was valid (found), set it to the value specified in Valid user return code. If the user was invalid (not found), set it to the value specified in Invalid user return code.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Valid user return code	The return code of the stored procedure when it has validated a user.
Invalid user return code	The return code of the stored procedure when it has invalidated a user.
Stored procedure to authenticate a user
Name	Specify the stored procedure used to authenticate a user with these supporting parameters (separated by %): LOGIN_ID PASSWORD ERROR_MESSAGE Notes: While executing the authenticate user, LOGIN_ID and PASSWORD parameters are substituted by Account ID and Password respectively. ERROR_MESSAGE is an output parameter used to return a specific error message to the connector when the stored procedure encounters a failure during execution. It must be set within the stored procedure and is used only when the value specified in Failure return code is returned by the stored procedure.
Success return code	The return code of the stored procedure when it has run successfully.
Failure return code	The return code of the stored procedure when it failed to run successfully.
Password Expiration Support
Expiration Options For Admin/OBO User Password Reset	Specify the password expiration: None, Immediate, or Immediate with Date. Note: If Immediate with Date is selected, Immediate is also available.

Click the Connect button to test the Connection Information:
- If successful, this message displays:
Connection from Studio to the connected system was established successfully.
- If unsuccessful, this message displays:
Failed to establish connection from Studio to the connected system.

Note: If the connection fails, additional messages may display providing more information regarding the failure, and additional information may be posted to the Provisioning and Identity logs.
Click the Apply button to apply changes. The Category Association window displays.
1. Select one or more object categories from the Available Categories list or enter a category name and click the Search button to find a specific category to select. If there are no available categories to select, proceed to Step 6.
2. Click the Add button to associate the selected object categories to the connected system.
Click OK to accept selected categories.

See Copying, Modifying, and Deleting Connected Systems for additional information.

Using the Connected System for Identity

Perform these procedures to configure the connector:

Connector Details for Identity
Identity Password Management

Connector Details for Identity

This table lists values to enter when associating the Identity user with an existing user in the connected system:

Field	System Attribute	Example Value
Login ID	username	BLANE
Account ID	username	BLANE

Variables

This connector performs password management functions for a Microsoft SQL Server database using existing stored procedures to perform the different features. When creating the connected system, specify the stored procedure and its parameters for the various features, such as setting expiring/ nonexpiring passwords, enabling a user, checking the validity of a user, and checking whether a user is enabled/disabled. The return codes for successful and failed execution of the stored procedure also need to be specified. In the case of operations such as checking whether a user is enabled/disabled or valid/invalid, also provide an OUT parameter to the stored procedure, which will be set to the return code that indicates the status of the user. This table defines some System variables:

Variable	Description
LOGIN_ID	This functions as an IN parameter to the stored procedure of type varchar. The connector substitutes the ID of the user on whom the operation is performed in place of this variable at run time. For example, If a stored procedure called idmsp_isValidUser is used to check the validity of the user, it will be configured as: idmsp_IsValidUser %LOGIN_ID %RESULT.
PASSWORD	This functions as an IN parameter to the stored procedure of type varchar. The value of the new password to be set for the user will be substituted by the connector in place of this variable, for example, idmsp_ResetPassword %LOGIN_ID %PASSWORD.
PASSWORD_EXP_DATETIME	This functions as an IN parameter to the stored procedure of type datetime. The datetime at which the password has to expire specified using the Identity Admin UI in the format MM/dd/yyyy HH:mm:ss will be substituted, for example, idmsp_ResetPassword %LOGIN_ID %PASSWORD %PASSWORD_EXP_DATETIME.
RESULT	This functions as an OUT parameter to the stored procedure of type int, tinyint, or smallint. This is used in the case of stored procedures that fill an out parameter with a value to indicate whether a user is enabled/disabled or valid/invalid, for example, idmsp_IsValidUser %LOGIN_ID %RESULT.
ERROR_MESSAGE	This functions as an OUT parameter to the stored procedure of type varchar. This can be specified as a parameter to a stored procedure that returns the error message of a failed execution in an out variable, for example, idmsp_Reset_Password %LOGIN_ID %PASSWORD %ERROR_MESSAGE. Note: The value of the ERROR_MESSAGE variable will only be displayed in the Admin UI if execution of the stored procedure returns the Failure return code specified when configuring this connector.
ENABLE_DISABLE	This functions as an IN parameter to the stored procedure of type bit and will be substituted with a value of bit, or Boolean value of 1 when the operation is to enable the user, and 0 when the operation is to disable the user.

Parameters

The connector does not expect these parameters in any particular order but it must match the order expected by the stored procedure. This configuration information may be provided for the different features when creating a connected system:

Check the validity of a user in the connected system.
If there is a stored procedure to check the validity of the user and it is called idmsp_IsValidUser, then the connector supports these parameters for the stored procedure:
idmsp_IsValidUser %LOGIN_ID %RESULT %ERROR_MESSAGE

In addition to the stored procedure and its parameters, also provide the return codes (integer for MSSQL Server) for the stored procedures for success and failure. The RESULT variable contains the return code that indicates whether a user is valid or invalid. LOGIN_ID and RESULT are man- datory and ERROR_MESSAGE is optional depending on the parameters accepted by the stored procedure.

Check whether a user is enabled or disabled.
If there is a stored procedure to check whether the user is enabled/disabled and it is called idmsp_IsUserEnabled, then the connector supports these parameters for the stored procedure:
idmsp_IsUserEnabled %LOGIN_ID %ENABLE_DISABLE %ERROR_MESSAGE

The return codes for successful and failed executions of the stored procedure also need to be provided. The RESULT variable contains the return code that indicates whether a user is enabled or disabled. LOGIN_ID and RESULT are mandatory and ERROR_MESSAGE is optional depending on the parameters accepted by the stored procedure.

Enable/disable the users account in the system.
There may be a single procedure to handle both enabling and disabling of a user or there may be two different procedures. If there is only a single procedure and it is called idmsp_EnableUser, then the connector supports these parameters for the stored procedure:
idmsp_EnableUser %LOGIN_ID %LOGIN_ID %ENABLE_DISABLE %ERROR_MESSAGE
LOGIN_ID and ENABLE_DISABLE are mandatory and ERROR_MESSAGE is optional depending on the parameters accepted by the stored procedure.

If there are two different procedures, these will be specified: idmsp_EnableUser %LOGIN_ID %ENABLE_DISABLE %ERROR_MESSAGE idmsp_DisableUser %LOGIN_ID %ENABLE_DISABLE %ERROR_MESSAGE LOGIN_ID is mandatory and ENABLE_DISABLE and ERROR_MESSAGE are optional depending on the parameters accepted by the stored procedure.

Reset regular password, or password that will expire at a particular date or immediately.
If the procedure to reset the password is called idmsp_ResetPassword, then the connector sup- ports these parameters for the stored procedure:
idmsp_ResetPassword %LOGIN_ID %PASSWORD %PASSWORD_EXP_DATETIME %ERROR_MESSAGE

LOGIN_ID and PASSWORD are mandatory and PASSWORD_EXP_DATETIME and ERROR_MESSAGE are optional depending on the parameters accepted by the stored procedure.

This connector returns a password expiry support type of EXPIRE_AT_DATETIME, which means that in the User Password Management page, a check box indicates whether expiry of the password is supported:

Identity Password Management

See User Management for details on password management.

Using the Connected System for Provisioning

Perform these procedures to configure the connector:

Note: If the number of records to be processed exceeds one thousand, we recommend configuring the workflow to use bulk mode, which lowers the memory consumption of the system by streaming data to files. Because data is streamed for every task, performance of the workflow execution will be decreased due to increased read-write operations. See the Workflow and Connectivity Studio document for details on how to configure bulk mode.

Configuring for Export

Perform these procedures to configure the connector for data export:

From the Workflow and Connectivity Studio, select the Microsoft SQL Stored Procedure UserExport workflow listed under the projects folder.

If a workflow does not already exist, create an export workflow. See Workflow and Connectivity Studio documentation for details on creating export workflows.

Configuring the Export Connector

In the Design pane, double-click the export object (the first workflow object after the Start object). The Configure Data Source window displays:

From the Configure Plug-in tab, set these properties as required:

Associated Connected System	Select the connected system from the list. The export operation will be done from this connected system.
Data Formats	Select the type of data format to use: Worker (default), WorkerFromTransactionLogs, Reference or Report
DeltaExportMode	Select the type of attribute to export if a change takes place (this works in conjunction with ExportMode when DeltaExport is selected): OnlyChangedAttributes - Performs a partial export of only the changed attributes from the last time the query was run. ChangedAndMandatoryAttributes (default) - Performs a partial export of both changed and mandatory attributes from the last time the query was run. Mandatory attributes are exported whether they have been changed or not. AllAttributes - Performs a full export of all attributes that contain a value. This property is not available for WorkerFromTransactionLogs data format.
DynamicConnectedSystem	Select the global variable to use as the dynamic connected system name. This works in conjunction with DynamicCon- nectedSystemOption when GlobalVariable is selected.
DynamicConnectedSystemOption	Select how to control Dynamic System Support: None - There will not be any Dynamic System Support. Transaction-SystemName - The value of the Transaction- SystemName attribute in data will be used as the dynamic connected system. The connected system name must be passed as the value of the attribute Transaction- SystemName; if it is missing in data, the operation will fail. GlobalVariable - Select a global variable to use as the dynamic connected system name from the property DynamicConnectedSystem.
ExcludeEmptyFields	Select how to process null fields: FALSE - Returns null fields with empty values. TRUE - Ignores null fields.
ExportMode	Select the type of data to export: FullExport - Exports all DeltaExport - Exports changed, mandatory, or all attributes, depending on the DeltaExportMode property setting. This property is not available for WorkerFromTransactionLogs data format since this data format always uses Full Export mode.
MaximumRows	The maximum number of records to return on the export.
Note: Hover the pointer over a property to view its description.

Optional) Select the Appearance tab to change how the Connected System object displays in the Design pane.
Click OK to save any changes and return to the Workflow and Connectivity Studio window.

Configuring the Export Link

In the Design pane, double-click the export link between the export object (the first workflow object after the Start object) and the Data Mapper object. The Configure Link window displays:

Element	Description
Database	Select the database name: Static - Select from the Database Name drop-down list. Dynamic - Select from the Global Variable drop-down list. Select the database source for the selected fields: Tables or Views.
Tables	Lists the schema tables available for export.
Fields	Lists the fields available for export.
Check attributes for delta export.
Selected Fields	Lists the selected schema table fields for export. Note: The check boxes are used only for delta export operations. These checked attributes will always be exported whether they were changed or not. The Format button specifies a desired date/time format to be applied to a selected date type field. Only selected date fields will be able to apply a date/ time format to their value.
Advanced Settings	Displays the Configure Attributes window for selecting any attributes that need to be encrypted.
SQL Query	Displays the SQL query run against the database. The Edit button edits the query.
Preferred Key(s)	Select the field(s) in the Selected Fields list: Set Key - Sets the primary key. Get Key - Gets the Preferred Key(s) of the selected table/view.

From the Datasource tab, perform these steps.
1. Select a database from the Schema field. Select whether the source is Tables or Views. Select the table or view from the Tables/Views Select the fields from the Fields list to export and add them to the Selected Fields list. Select the field(s) from the Selected Fields list that require a date and/or time format and click the Format button. The Format Date window displays.
2. Select the Include Time check box to add the timestamp with the date. Select the 24 Hour or 12 Hour option button and then select the required date/time format. Click OK to save the selected format. The Configure Link window displays.
3. Perform one of the steps.
  - Select the field(s) in the Selected Fields list and click the Set Key button to set the primary key.
    Or
  - Click the Get Key button to get the Preferred Key(s) of the selected table/view.
4. Check the boxes in the Selected Fields list to set mandatory attributes.
Click OK to save any changes and return to the Workflow and Connectivity Studio window.
Deploy the workflow by selecting Deploy _ New Deployment.
See the Workflow and Connectivity Studio document for details of deployment options.
Manage and run the deployed workflow from the Admin UI ► Server tab.
See the Identity Suite Administration Guide for details.

Configuring for Import

Perform these procedures to configure the connector for data import:

From the Workflow and Connectivity Studio, select the Microsoft SQL Server UserAdd, UserModify, or UserDelete workflow listed under the projects folder.

If a workflow does not already exist, create an import workflow. See the Workflow and Connectivity Studio documentation for details on creating import workflows.

Configuring the Import Connector

In the Design pane, double-click the import object (the last workflow object). The Configure Data Source window displays:

From the Configure Plug-in tab, set these properties as required:

Associated Connected System	Select the connected system from the list. The export operation will be done from this connected system.
Data Formats	Select the type of data format to use: Worker (default), WorkerFromTransactionLogs, Reference or Report
DynamicConnectedSystem	Select the global variable to use as the dynamic connected system name. This works in conjunction with DynamicCon- nectedSystemOption when GlobalVariable is selected.
DynamicConnectedSystemOption	Select how to control Dynamic System Support: None - There will not be any Dynamic System Support. Transaction-SystemName - The value of the Transaction- SystemName attribute in data will be used as the dynamic connected system. The connected system name must be passed as the value of the attribute Transaction- SystemName; if it is missing in data, the operation will fail. GlobalVariable - Select a global variable to use as the dynamic connected system name from the property DynamicConnectedSystem.
Id *	Enter the attribute that contains the value used to uniquely identify the user account user ID on the connected system.
loginId *	Enter the attribute that contains the value used to uniquely identify the user account login ID on the connected system.
Notes: * Id and loginId are used by the Provisioning Policy and IdentityHub features to populate the ACCOUNT_ID and ACCOUNT_USERNAME columns of the FISC_USER_ACCOUNT table of the Product database. See the ‘Provisioning Policy’ and ‘Provisioning Using the IdentityHub’ chapters of the Identity Suite Administration Guide for details. Hover the pointer over a property to view its description.

(Optional) Select the Appearance tab to change how the Connected System object displays in the Design pane.
Click OK to save any changes and return to the Workflow and Connectivity Studio window.
Configuring the Import Link

In the Design pane, double-click the import link between the Data Mapper object and the import object (the last workflow object). If you selected the Stored Procedure data format in Step , this Configure Link window displays:

Element	Description
Select API variables to be imported.
API	Lists the APIs available for import.
API Variables	Lists the variables available for import.
Check variables for audit.
Format	Sets the format for date variables.
Selected Variables	Lists the selected schema table fields for import. If auditing is enabled and these variables below are checked, Provisioning will log all events for auditing purposes.
Modify APIs	Adds or modifies APIs. Displays the Modify API window.
Advanced Settings	Displays the Configure Attributes window for selecting any attributes that need to be encrypted.

Perform these steps.

Select the API name in the API field. Select the API variable in the API Variables field. Add the selected API variables to the Selected Variables field.

To add or modify an API, click the Modify APIs button. The Modify API window displays:

Element	Description
API Names	Displays configured APIs. Select the API to modify or delete.
API Variables	Displays the API variables corresponding to the selected API. Select the API variable to modify or delete.
Use API Format	Select the method to build the API at runtime with this check box: If not selected (default), each variable will be listed in the API Variable Order list box. The variables will be used in the API Variable Order while generating the API. The Up and Down buttons can be used to change the order. If selected, the format can be specified in the API Format text area that becomes visible. Specify the variables inside ## (e.g., ##sp_adduser.loginame##). The API will be generated by substituting the values of the variables in the API format. The Edit button that becomes visible, displays the Api Format dialog. Use the Get Template button to generate a default format for the variables configured in the API; this has to be modified based on the API requirement.
API Variable Order	Change the variable order with the Up and Down buttons.
Primary Key	Select the value to use to uniquely identify an object (e.g., user or group).
Failure Message Attribute	The output attribute that returns the failure reason. This is an optional setting and if specified, the return value is used to set the status description on API execution failure.
Role Attribute	Sets the Role attribute.

Custom APIs and API variables can be created to invoke any stored procedure in the Microsoft SQL Database Server. See the appendix Working with Database APIs for additional information.

Click OK when done adding/modifying APIs to return to the Configure Link window.

Click OK to save any changes and return to the Workflow and Connectivity Studio window. If this workflow was downloaded from another studio, there is a possibility that it might be using APIs and API variables which are not available in the current studio. In such scenario a confirmation message as shown below will display.

If the user selects Yes, the APIs will be copied to the studio API list with the information available with the workflow and continue with the processing. If the user selects No, processing will stop. The user has to either manually add the API or has to cancel the operation.
Deploy the workflow by selecting Deploy _ New Deployment.
See the Workflow and Connectivity Studio document for details of deployment options.
Manage and run the deployed workflow from the Admin UI _ Server tab.
See the Identity Suite Administration Guide for details.

Connector Details for Provisioning

Configuration import properties Id and loginId are used by the Provisioning Policy and IdentityHub features to populate the ACCOUNT_ID and ACCOUNT_USERNAME columns of the FISC_USER_ACCOUNT table of the Product database. See the ‘Provisioning Policy’ and ‘Provisioning Using the IdentityHub’ chapters of the Identity Suite Administration Guide for details.

This table shows the default attributes specified for these properties for the connected system:

Import Property	System Attribute
Id	sp_addlogin.loginame
loginId	sp_addlogin.loginame

Connector Attributes

Connector attributes are controlled by the APIs configured for the connector. These are the default APIs and their attributes:

Name	Type	Corresponding Native Attribute	Description
Add User
Group Name	Varchar	grpname	Role name
Login Name	Varchar	loginame	Login name
Name for the New User	Varchar	name_in_db	User name in database
Drop Login
Login Name	Varchar	loginame	Login name