How To
ForgeRock Identity Cloud

How do I set up a scripted SQL connector using the Remote Connector Server (RCS) with Identity Cloud?

Last updated Oct 7, 2021

The purpose of this article is to demonstrate how to set up a scripted SQL connector using the RCS with Identity Cloud.


Overview

This article provides instructions on how to set up a scripted SQL connector using the RCS with Identity Cloud. It is intended for demonstration purposes only, and uses a sample MySQL™ database in a Docker image and sample Groovy scripts. The scripts provided with this sample are specific to the sample. You must customize these scripts to address the requirements of your specific deployment. The sample scripts are a good starting point on which to base your customization.

Steps involved:

  1. Set up the RCS
  2. Create the Docker container with the sample MySQL database
  3. Copy the MySQL Connector to the RCS
  4. Create the scripted SQL connector in Identity Cloud
  5. Verify the connector in Identity Cloud
  6. Sync the MySQL database with Identity Cloud

Prerequisites

  • You have a working Identity Cloud tenant.
  • You have installed and configured a Docker engine. This must be on the same machine that you will install the RCS on.

Setting up the RCS

You'll need to install the Java® RCS which is required for Identity Cloud to connect remotely to any supported identity resource server via a connector. See About Identity Cloud Connectors for further information.

Install the RCS by following steps 1 through 3 in Sync Identities:

  1. Register a remote server
  2. Download a remote server
  3. Configure the remote server to connect to Identity Cloud

Once you've completed these steps, verify that the RCS server is connected: 

  • In the Identity Cloud Admin UI, navigate to Identities > Connect and check RCS server status is ‘Connected'.

Creating the Docker container with the sample MySQL database

Caution

Disclaimer for the following Docker image, please review before implementing this sample. This image is just a sample for demonstration purposes. Creating and using Docker images is outside the scope of ForgeRock support; if you want more tailored advice, consider engaging Deployment Support Services.  

Follow these steps to create a Docker container with an external MySQL database (named “hrdb”). This database is pre-populated with sample data and a user (called forgerock) that the connector will authenticate as.

  1. Create the Docker container with the external MySQL database: $ docker run --name mysql-for-rcs -p3306:3306 -e MYSQL_ROOT_PASSWORD=Pa$$w0rd -d sandeepc0/mysql-hrdb
  2. Verify that a MySQL instance is running in the Docker container: $ docker exec -it mysql-for-rcs mysql -u root -pPa$$w0rdThe mysql > prompt is displayed if the MySQL instance is running in the Docker container as expected.
  3. Verify that the database is initialized:
    1. Specify the database to use:  mysql> use hrdb; Database changed
    2. Return a list of users:mysql> select uid from users; +--------+ | uid | +--------+ | bob | | rowley | | louis | | john | | jdoe | +--------+ 5 rows in set (0.00 sec)If a list of users is returned, this indicates that the database has initialized successfully.
    3. Exit MySQL:mysql> exit

Copying the MySQL Connector to the RCS

  1. Download MySQL Connector/J version 5.1 or later.
  2. Unzip the downloaded file and copy the mysql-connector-java-x.x.x.jar to /path/to/openicf/lib/framework on the machine where you installed the RCS.
  3. Restart the RCS:$ /path/to/openicf/bin/ConnectorServer.sh /run

Creating the scripted SQL connector in Identity Cloud

  1. Download the latest IDM distribution from here and unzip it.
  2. Modify the contents of provisioner.openicf-hrdb.json (located in /path/to/idm/samples/scripted-sql-with-mysql/conf) as shown in the following example. You'll need to:
    1. Add connectorHostRef, systemType and displayName under connectorRef as shown, and change the connectorHostRef to the name of your RCS client.
    2. Change the username and password under configurationProperties to forgerock and Pa$$w0rd respectively.
    3. Change the scriptRoots path under configurationProperties to replace /path/to/openicf with the path to your RCS client.
    4. Remove the systemActions section completely.{    "connectorRef" : {         "connectorHostRef": "<RCSClientName>",         "systemType": "provisioner.openicf",         "displayName": "Scripted SQL Connector",         "bundleName" : "org.forgerock.openicf.connectors.scriptedsql-connector",         "bundleVersion" : "[1.5.0.0,1.6.0.0)",         "connectorName" : "org.forgerock.openicf.connectors.scriptedsql.ScriptedSQLConnector"     },     "producerBufferSize" : 100,     "connectorPoolingSupported" : true,     "poolConfigOption" : {         "maxObjects" : 10,         "maxIdle" : 10,         "maxWait" : 150000,         "minEvictableIdleTimeMillis" : 120000,         "minIdle" : 1     },     "operationTimeout" : {         "CREATE" : -1,         "TEST" : -1,         "AUTHENTICATE" : -1,         "SEARCH" : -1,         "VALIDATE" : -1,         "GET" : -1,         "UPDATE" : -1,         "DELETE" : -1,         "SCRIPT_ON_CONNECTOR" : -1,         "SCRIPT_ON_RESOURCE" : -1,         "SYNC" : -1,         "SCHEMA" : -1     },     "configurationProperties" : {         "username" : "forgerock",         "password" : "Pa$$w0rd",         "driverClassName" : "com.mysql.jdbc.Driver",         "url" : "jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC",         "autoCommit" : true,         "validationQuery" : "SELECT 1 FROM DUAL",         "validationInterval" : "2000",         "testOnBorrow" : true,         "authenticateScriptFileName" : "AuthenticateScript.groovy",         "createScriptFileName" : "CreateScript.groovy",         "testScriptFileName" : "TestScript.groovy",         "searchScriptFileName" : "SearchScript.groovy",         "deleteScriptFileName" : "DeleteScript.groovy",         "updateScriptFileName" : "UpdateScript.groovy",         "syncScriptFileName" : "SyncScript.groovy",         "schemaScriptFileName" : "SchemaScript.groovy",         "scriptRoots" : [             "/path/to/openicf/connector_scripts/scripted_sql/"         ]     },     "resultsHandlerConfig": {         "enableAttributesToGetSearchResultsHandler": true     },     "syncFailureHandler" : {         "maxRetries" : 5,         "postRetryAction" : "logged-ignore"     },     "objectTypes" : {         "group" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "__GROUP__",             "type" : "object",             "nativeType" : "__GROUP__",             "properties" : {                 "name" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "__NAME__",                     "nativeType" : "string"                 },                 "gid" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "gid",                     "nativeType" : "string"                 },                 "description" : {                     "type" : "string",                     "required" : false,                     "nativeName" : "description",                     "nativeType" : "string"                 },                 "users" : {                     "type" : "array",                     "nativeName" : "users",                     "nativeType" : "object",                     "items" : {                         "type" : "object",                         "properties" : {                             "uid" : {                                 "type" : "string"                             }                         }                     }                 }             }         },         "organization" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "organization",             "type" : "object",             "nativeType" : "organization",             "properties" : {                 "name" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "__NAME__",                     "nativeType" : "string"                 },                 "description" : {                     "type" : "string",                     "required" : false,                     "nativeName" : "description",                     "nativeType" : "string"                 }             }         },         "account" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "__ACCOUNT__",             "type" : "object",             "nativeType" : "__ACCOUNT__",             "properties" : {                 "firstName" : {                     "type" : "string",                     "nativeName" : "firstname",                     "nativeType" : "string",                     "required" : true                 },                 "email" : {                     "type" : "string",                     "nativeName" : "email",                     "nativeType" : "string"                 },                 "cars" : {                     "type" : "array",                     "nativeName" : "cars",                     "nativeType" : "object",                     "items" : {                         "type" : "object",                         "properties" : {                             "year" : {                                 "type" : "string"                             },                             "make" : {                                 "type" : "string"                             },                             "model" : {                                 "type" : "string"                             }                         }                     }                 },                 "password" : {                     "type" : "string",                     "nativeName" : "password",                     "nativeType" : "string",                     "flags" : [ "NOT_READABLE", "NOT_RETURNED_BY_DEFAULT" ]                 },                 "uid" : {                     "type" : "string",                     "nativeName" : "__NAME__",                     "required" : true,                     "nativeType" : "string"                 },                 "fullName" : {                     "type" : "string",                     "nativeName" : "fullname",                     "nativeType" : "string"                 },                 "lastName" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "lastname",                     "nativeType" : "string"                 },                 "organization" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "organization",                     "nativeType" : "string"                 },                 "timestamp" : {                     "type" : "string",                     "nativeName" : "timestamp",                     "nativeType" : "string",                     "flags" : [                         "NOT_CREATEABLE",                         "NOT_UPDATEABLE"                     ]                 }             }         }     },     "operationOptions" : {     } }
  3. Create a connector_scripts/scripted_sql directory in /path/to/openicf on the machine where you installed the RCS:$ cd path/to/openicf $ mkdir -p connector_scripts/scripted_sql
  4. Copy all the Groovy scripts from /path/to/idm/samples/scripted-sql-with-mysql/tools to /path/to/openicf/connector_scripts/scripted_sql.
  5. Create the connector configuration in Identity Cloud by making a PUT request with the JSON content in provisioner.openicf-hrdb.json (modified in Step 2), replacing <YourTenantName> with your Identity Cloud tenant name:$ curl 'https://<YourTenantName>.forgerock.io/openidm/config/provisioner.openicf/hrdb' \  -X 'PUT' \   -H 'authorization: Bearer <bearer token>' \   -H 'content-type: application/json' \   --data-raw '{     "connectorRef" : {         "connectorHostRef": "<RCSClientName>",         "systemType": "provisioner.openicf",         "displayName": "Scripted SQL Connector",         "bundleName" : "org.forgerock.openicf.connectors.scriptedsql-connector",         "bundleVersion" : "[1.5.0.0,1.6.0.0)",         "connectorName" : "org.forgerock.openicf.connectors.scriptedsql.ScriptedSQLConnector"     },     "producerBufferSize" : 100,     "connectorPoolingSupported" : true,     "poolConfigOption" : {         "maxObjects" : 10,         "maxIdle" : 10,         "maxWait" : 150000,         "minEvictableIdleTimeMillis" : 120000,         "minIdle" : 1     },     "operationTimeout" : {         "CREATE" : -1,         "TEST" : -1,         "AUTHENTICATE" : -1,         "SEARCH" : -1,         "VALIDATE" : -1,         "GET" : -1,         "UPDATE" : -1,         "DELETE" : -1,         "SCRIPT_ON_CONNECTOR" : -1,         "SCRIPT_ON_RESOURCE" : -1,         "SYNC" : -1,         "SCHEMA" : -1     },     "configurationProperties" : {         "username" : "forgerock",         "password" : "Pa$$w0rd",         "driverClassName" : "com.mysql.jdbc.Driver",         "url" : "jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC",         "autoCommit" : true,         "validationQuery" : "SELECT 1 FROM DUAL",         "validationInterval" : "2000",         "testOnBorrow" : true,         "authenticateScriptFileName" : "AuthenticateScript.groovy",         "createScriptFileName" : "CreateScript.groovy",         "testScriptFileName" : "TestScript.groovy",         "searchScriptFileName" : "SearchScript.groovy",         "deleteScriptFileName" : "DeleteScript.groovy",         "updateScriptFileName" : "UpdateScript.groovy",         "syncScriptFileName" : "SyncScript.groovy",         "schemaScriptFileName" : "SchemaScript.groovy",         "scriptRoots" : [             "/path/to/openicf/connector_scripts/scripted_sql/"         ]     },     "resultsHandlerConfig": {         "enableAttributesToGetSearchResultsHandler": true     },     "syncFailureHandler" : {         "maxRetries" : 5,         "postRetryAction" : "logged-ignore"     },     "objectTypes" : {         "group" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "__GROUP__",             "type" : "object",             "nativeType" : "__GROUP__",             "properties" : {                 "name" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "__NAME__",                     "nativeType" : "string"                 },                 "gid" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "gid",                     "nativeType" : "string"                 },                 "description" : {                     "type" : "string",                     "required" : false,                     "nativeName" : "description",                     "nativeType" : "string"                 },                 "users" : {                     "type" : "array",                     "nativeName" : "users",                     "nativeType" : "object",                     "items" : {                         "type" : "object",                         "properties" : {                             "uid" : {                                 "type" : "string"                             }                         }                     }                 }             }         },         "organization" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "organization",             "type" : "object",             "nativeType" : "organization",             "properties" : {                 "name" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "__NAME__",                     "nativeType" : "string"                 },                 "description" : {                     "type" : "string",                     "required" : false,                     "nativeName" : "description",                     "nativeType" : "string"                 }             }         },         "account" : {             "$schema" : "http://json-schema.org/draft-03/schema",             "id" : "__ACCOUNT__",             "type" : "object",             "nativeType" : "__ACCOUNT__",             "properties" : {                 "firstName" : {                     "type" : "string",                     "nativeName" : "firstname",                     "nativeType" : "string",                     "required" : true                 },                 "email" : {                     "type" : "string",                     "nativeName" : "email",                     "nativeType" : "string"                 },                 "cars" : {                     "type" : "array",                     "nativeName" : "cars",                     "nativeType" : "object",                     "items" : {                         "type" : "object",                         "properties" : {                             "year" : {                                 "type" : "string"                             },                             "make" : {                                 "type" : "string"                             },                             "model" : {                                 "type" : "string"                             }                         }                     }                 },                 "password" : {                     "type" : "string",                     "nativeName" : "password",                     "nativeType" : "string",                     "flags" : [ "NOT_READABLE", "NOT_RETURNED_BY_DEFAULT" ]                 },                 "uid" : {                     "type" : "string",                     "nativeName" : "__NAME__",                     "required" : true,                     "nativeType" : "string"                 },                 "fullName" : {                     "type" : "string",                     "nativeName" : "fullname",                     "nativeType" : "string"                 },                 "lastName" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "lastname",                     "nativeType" : "string"                 },                 "organization" : {                     "type" : "string",                     "required" : true,                     "nativeName" : "organization",                     "nativeType" : "string"                 },                 "timestamp" : {                     "type" : "string",                     "nativeName" : "timestamp",                     "nativeType" : "string",                     "flags" : [                         "NOT_CREATEABLE",                         "NOT_UPDATEABLE"                     ]                 }             }         }     },     "operationOptions" : {     } }

Verifying the connector in Identity Cloud

Follow these steps to verify that the connector is working and can return data from the database.

  1. In the Identity Cloud Admin UI, navigate to Native Consoles > Identity Management > Configure > Connectors.

The Hrdb connector is displayed and shown as “Active”. 

  1. Select the Hrdb connector and navigate to Data tab > Account.

Account data from the hrdb database is displayed:

Synchronizing the MySQL database with Identity Cloud

  1. In the Identity Cloud Admin UI, navigate to Native Consoles > Identity Management > Create Mapping.
  2. Select the source (Hrdb) and target resource (for example, Managed Alpha_user) and click Create mapping > Create.
  3. Click Properties > Attributes Grid > Add Missing Required Properties.
  4. Click the Edit icon next to each target property to configure the following corresponding source properties:
    • givenName - firstName
    • mail - email
    • sn - lastName
    • userName - uid
  1. Click the Behaviors tab and select Default Actions from the Current Policy drop-down and click Save.
  2. Click Reconcile to sync the identities.

Once the reconciliation is complete, check that the identities in the hrdb database have been sync'd to Identity Cloud: 

  • In the Identity Cloud Admin UI, navigate to Identities > Manage > Alpha/Bravo Realm Users and search for the identities.

See Also

How do I implement a Remote Connector Server (RCS) for Identity Cloud?

Create a connector configuration over REST

Connect to a MySQL Database With ScriptedSQL


Copyright and Trademarks Copyright © 2021 ForgeRock, all rights reserved.