PSD1182 – Use PIM to search in SQL

Prerequisites

PhenixID Identity Manager (PIM) 5.6.2 or later.

Overview

Use Case

Use a Predefined search to search data in SQL.
PIM searches against SQL and gets a result back which PIM maps to the corresponding LDAP object. Objects that cannot be mapped are removed, only objects that have a corresponding LDAP object are retained.

Other PSDs of interest

PSD1183 – Create result grid in PIM from a SQL query
PSD1182 will fetch users from SQL but not the data of the users. PSD1183 will instruct you how to create a grid of data from SQL.

Configuration

With a number of policies you specify e.g. the SQL server and SQL query. (see the different policies below)

Add filter to PIM file system

1. Open file system
2. Add file in https://files.phenixid.se/s/K29GMgRL5gzFYfS to drive:\..\PhenixID\IM\customer\extension\class\psd
3. Restart PIM.

Add filter as a Tab External Filter

1. Open the Predefined Search in question
2. Click Tools – Tab External Filter
3. Click add and add psd.SearchInSqlPDSearchFilter

Policies / Tab Parameters

The polices below could either be used as policies in DSEditor.properties or as Tab Parameters in a form.

Below is the policies/tab parameters available. All parameters are available to use either as policies or parameters. Below is all the policies/parameters, an explanation and also where you probably would like to add the policy/parameter.

# What SQL driver to use for PIM
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchFilter.jdbc=net.sourceforge.jtds.jdbc.Driver

# Path to the SQL server including port and database name. The correct path is 
# decided by the driver used. Below is an example of the example driver above.
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchFilter.url=jdbc:jtds:sqlserver://127.0.0.1:1433/your_database

# Login name to database
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchFilter.username=sa

# Password for username
# Probably added as a policy in DSEditor.properties
psd.SearchInSqlPDSearchFilter.pwd=password

# The SQL stored procedure
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchFilter.query=[dbo].[example_spGetUsers]

# The OU in AD to map users in the SQL query. Users not mapped will be removed. 
# If value exist in form (Search Base:) then it will be used. If no value exist you 
# can add the value using this parameter.
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchFilter.searchBase=OU=UsersOU=CoS,DC=ad,DC=local

# Should PIM look in sub OU´s or not. Default the settings in form will be used. 
# If value exist in form (Scope:) then it will be used. If no value exist you can 
# add the value using this parameter.
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchFilter.searchScope=SUB

# Attribute from the SQL result including the mapping value
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchFilter.sqlKey=UserID

# Attribute in LDAP including the mapping value
# Probably added as a Tab Parameter in the search form
psd.SearchInSqlPDSearchFilter.ldapKey=sAMAccountName