SQL to Active Directory

Scenario

This use case imports users from a SQL table and provisions them in Active Directory.

The downloadable ZIP package for this use case includes two schedulers with corresponding pipes: one for user creation and one for user updates.

During the import process, a validation step ensures that users do not already exist in Active Directory. If a user already exists, the creation step is skipped. Once a user is successfully created, the use case writes back the generated user identifier to the SQL database as part of the pipeline execution.

In addition, a separate pipe handles updates of existing users. If the department column is modified in the SQL table, the corresponding user object in Active Directory will be updated accordingly.

Prerequisite

• A server with the latest version of FortifiedID Control installed
• An Active Directory environment configured and accessible
• A SQL server configured and accessible
• SQL Server Management Studio, or another SQL client that can create the example database and run the included scripts
• Access to Management Center with permission to update Control configuration and deploy changes

Configuration

Download and add the two schedules

1. Download the ZIP package for the use case.
   1. Click https://share.fortifiedid.se/index.php/s/pSZDzZTJ2nLpkRb to download the use case package.
   2. Unzip the file.
      1. The package contains two schedules that will be added to Management Center.
      2. The package also contains scripts for creating the SQL database and adding example users.
2. Add the two schedules to the schedule folder.
   1. Open a file explorer.
   2. Go to drive:\path\FortifiedID\mgmt-center\data\customer\control\config\modules\flows.
   3. Drop the two schedules into this folder.
3. Refresh Management Center Control and verify that the schedules are visible.
   1. In Configuration and Schedules, there should be a category called HR -> Active Directory.

Create and configure SQL database with some example data

1. Open SQL Server Management Studio.
2. Manually create a database called state.
3. First run the script in the zip-file called, create_temp_hr_users.sql
   A table called dbo.temp_hr_users has now been created.
4. Then run the script in the zip-file called, add_test_users_to_sql.sql
   A number of test users have now been added to the table.

Update globals to match your Active Directory and SQL database

All environment references in this use case use globals. Change the values below to match your environment.

1. Open Management Center Control.

2. Click the Configuration tab.

3. Expand Misc settings.

4. Click Globals.

5. Add the following keys.
   The paths below are examples. You can either create the same structure in your environment or change the values to match your own structure.

     "ad_edition_name" : "ad_edition",
     "ad_edition_host" : "127.0.0.1",
     "ad_edition_port" : 636,
     "ad_edition_use_ssl" : true,
     "ad_edition_ssl_trust_all" : true,
     "ad_edition_base_dn" : "OU=FortifiedID_Control,DC=company,DC=local",
     "ad_edition_bind_dn" : "your_admin_account",
     "ad_edition_bind_password" : "your_admin_password",
     "ad_edition_userprincipalname_suffix" : "@company.local",
     "ad_edition_add_users_dn" : "OU=users,OU=FortifiedID_Control,DC=company,DC=local",
     "ad_edition_add_groups_dn" : "OU=groups,OU=FortifiedID_Control,DC=company,DC=local"
     "db_sql_1_namespace": "sqlexpress",
     "db_sql_1_driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
     "db_sql_1_url": "jdbc:sqlserver://127.0.0.1:1433;database=state;encrypt=false",
     "db_sql_1_username": "sa",
     "db_sql_1_password": "your_sa_password",
     "db_sql_1_encryption_key": "your_encryption_key",

6. Change the values above to match your environment.

7. Click Update.

Add an LDAPClient module

The LDAPClient module uses the globals configured in the previous step.

1. Open Management Center Control.

2. Click the Configuration tab.

3. Expand Modules and click + to add a new module.

4. Add an LDAPClient.

5. Click JSON mode and paste the data below. It uses the values added to globals.

   {
     "name": "LdapClient",
     "enabled": true,
     "instances": 1,
     "config": {
       "name": "${globals.ad_edition_name}",
       "connection": {
         "host": "${globals.ad_edition_host}",
         "port": "${globals.ad_edition_port}",
         "bind_dn": "${globals.ad_edition_bind_dn}",
         "bind_password": "${globals.ad_edition_bind_password}",
         "use_ssl": "${globals.ad_edition_use_ssl}",
         "ssl_trust_all": "${globals.ad_edition_ssl_trust_all}"
       }
     },
     "display_name": "AD Edition"
   }

6. Click Update.

Add a JDBCClient module

The JDBCClient module uses the globals configured earlier for the SQL database connection.

1. Open Management Center Control.

2. Click the Configuration tab.

3. Expand Modules and click + to add a new module.

4. Add a JDBCClient.

5. Click JSON mode and paste the data below. It uses the values added to globals.

   {
     "id": "jdbcclient_1",
     "name": "JdbcClient",
     "config": {
       "name": "${globals.db_sql_1_namespace}",
       "db_driver": "${globals.db_sql_1_driver}",
       "jdbc": {
         "url": "${globals.db_sql_1_url}",
         "user": "${globals.db_sql_1_username}",
         "password": "${globals.db_sql_1_password}"
       }
     },
     "display_name": "Local SQL Express DB",
     "description": "Create and update users from SQL to AD (Scheduled)"
   }
   

6. Click Update.

Deploy settings to Control

1. Click the settings button in the top-right corner of Management Center.
2. Click Deploy.
3. Changes will be saved and service restarted.

Test the configuration

Test the create schedule

1. In Management Center, click Configuration -> Schedule.

2. Under the HR -> Active Directory category, click SQL -> AD (Create).

3. Click the associated pipe called SQL_AD_Create_pipe.

4. In the upper-right corner, click the Test button.

   

5. Click Run Pipe.

6. Check the Test UI and the server.log file for errors.

7. If the pipe completes without errors, verify that the users have been created in the target Active Directory OU.

8. Verify that the generated user identifiers have been written back to the SQL table.

Test the update schedule

1. Open the SQL table and change the department for one or more of the users created by the first schedule.

2. In Management Center, click Configuration -> Schedule.

3. Under the HR -> Active Directory category, click SQL -> AD (Update).

4. Click the associated pipe called SQL_AD_Update_pipe.

5. In the upper-right corner, click the Test button.

   

6. Click Run Pipe.

7. Check the Test UI and the server.log file for errors.

8. If the pipe completes without errors, verify that the corresponding users in Active Directory have updated department values.

Adjust configuration for production

Before using this configuration in production, review the example values and replace them with values that match your security requirements.

• Use proper certificate validation for LDAPS instead of trusting all certificates.
• Use service accounts with only the permissions required for the SQL and Active Directory operations in this use case.
• Store and protect database and directory credentials according to your operational security policy.