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.
![](https://support.phenixid.se/content/uploads/2021/05/PIM_SQL_2.jpg)
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
- Open file system
- Add file in https://files.phenixid.se/s/K29GMgRL5gzFYfS to drive:\..\PhenixID\IM\customer\extension\class\psd
- Restart PIM.
Add filter as a Tab External Filter
- Open the Predefined Search in question
- Click Tools – Tab External Filter
- 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
DISCLAIMER
Information provided in this document is for your information only. PhenixID makes no explicit or implied claims to the validity of this information. Any trademarks referenced in this document are the property of their respective owners.The origin of this information may be internal or external to PhenixID. PhenixID makes all reasonable efforts to verify this information.
PhenixID - support.phenixid.se