One Identity Manager 8.1.

Native Database Connector User

Guide for Connecting SQL Server
Databases
Copyright 2020 One Identity LLC.
ALL RIGHTS RESERVED.
This guide contains proprietary information protected by copyright. The software described in this guide
is furnished under a software license or nondisclosure agreement. This software may be used or copied
only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced
or transmitted in any form or by any means, electronic or mechanical, including photocopying and
recording for any purpose other than the purchaser’s personal use without the written permission of
One Identity LLC .
The information in this document is provided in connection with One Identity products. No license,
express or implied, by estoppel or otherwise, to any intellectual property right is granted by this
document or in connection with the sale of One Identity LLC products. EXCEPT AS SET FORTH IN THE
TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT,
ONE IDENTITY ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR
STATUTORY WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-
INFRINGEMENT. IN NO EVENT SHALL ONE IDENTITY BE LIABLE FOR ANY DIRECT, INDIRECT,
CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT
LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF
INFORMATION) ARISING OUT OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF
ONE IDENTITY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. One Identity makes no
representations or warranties with respect to the accuracy or completeness of the contents of this
document and reserves the right to make changes to specifications and product descriptions at any
time without notice. One Identity does not make any commitment to update the information
contained in this document.
If you have any questions regarding your potential use of this material, contact:
One Identity LLC.
Attn: LEGAL Dept
4 Polaris Way
Aliso Viejo, CA 92656
Refer to our Web site ([Link] for regional and international office information.
Patents
One Identity is proud of our advanced technology. Patents and pending patents may apply to this
product. For the most current information about applicable patents for this product, please visit our
website at [Link]
Trademarks
One Identity and the One Identity logo are trademarks and registered trademarks of One Identity
LLC. in the U.S.A. and other countries. For a complete list of One Identity trademarks, please visit
our website at [Link]/legal. All other trademarks are the property of their
respective owners.
Legend

WARNING: A WARNING icon highlights a potential risk of bodily injury or property

damage, for which industry-standard safety precautions are advised. This icon is
often associated with electrical hazards related to hardware.

CAUTION: A CAUTION icon indicates potential damage to hardware or loss of data if

instructions are not followed.

One Identity Manager Native Database Connector User Guide for Connecting SQL Server Databases
Updated - January 2020
Version - 8.1.2
Contents

Native database connector for SQL Server databases 4

Users and permissions for synchronizing 5
Setting up a custom application role for synchronization 9
Setting up the synchronization server 10
Prerequisites and notes for connecting a One Identity Manager database as a target
system 13
Creating a synchronization project 14
How to set up a synchronization project 16
Connecting a system to an SQL Server database 18
Updating schemas 26
Starting synchronization 27
Analyzing synchronization 28
Post-processing outstanding objects 28
Configuring target system synchronization 29
How to post-process outstanding objects 30
Configuring the provisioning of memberships 31

Error handling 34

About us 35
Contacting us 35
Technical support resources 35

Index 36

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases
3
 1

Native database connector for SQL

Server databases

Using this native database connector, you can synchronize external databases with the One
Identity Manager database. One Identity Manager supports connecting to SQL Server
databases, amongst others. The native database connector can therefore also be used to
synchronize One Identity Manager databases with different product versions or modules.
The native database connector cannot load any random external database system data
configuration. For example, custom data types and columns containing value list are not
currently supported.
The native database connection does not provide a project template for setting up
synchronization. You must create synchronization configuration components
(mappings, workflows, start up configurations ...) manually after the synchronization
project has been saved.
In the Synchronization Editor, external database tables and columns are referenced as
schema types and schema properties.

To set up synchronization with a database

1. Install and configure a synchronization server and declare the server as Job server in
One Identity Manager.
2. Provide One Identity Manager users with the required permissions for setting up
synchronization and post-processing of synchronization objects.
3. Create a synchronization project with the Synchronization Editor.

Detailed information about this topic

l Setting up the synchronization server on page 10

l Users and permissions for synchronizing on page 5
l Prerequisites and notes for connecting a One Identity Manager database as a target
system on page 13
l Creating a synchronization project on page 14

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 4
Native database connector for SQL Server databases
Users and permissions for
synchronizing
In the synchronization with the database connectors, there are three use cases for mapping
synchronization objects in the One Identity Manager data model.

1. Mapping custom target systems

2. Mapping default tables (for example Person, Department)
3. Mapping custom tables

In the case of non-role-based login to One Identity Manager tools, it is sufficient to add one
system user in the DPR_EditRights_Methods permissions group. For detailed
information about system users and permissions groups, see the One Identity Manager
Authorization and Authentication Guide.

Table 1: Users and permissions groups for non role-based login

User Tasks

One Identity Manager l Create customized permissions groups for application

administrators roles for role-based login to administration tools in the
Designer as required.
l Create system users and permissions groups for non role-
based login to administration tools in the Designer as
required.
l Enable or disable additional configuration parameters in
the Designer as required.
l Create custom processes in the Designer as required.
l Create and configure schedules as required.
l Create and configure password policies as required.

System users in the l Configure and start synchronization in the

DPR_EditRights_ Synchronization Editor.
Methods permissions
l Edit the synchronization's target system types as well as
group
outstanding objects in the Manager.

There are different steps required for role-based login, in order to equip One Identity
Manager users with the required permissions for setting up synchronization and post-
processing of synchronization objects.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 5
Native database connector for SQL Server databases
Table 2: User and permissions groups for role-based login: Mapped as custom
target system

User Tasks

One Identity Manager l Create customized permissions groups for application

administrators roles for role-based login to administration tools in the
Designer as required.
l Create system users and permissions groups for non role-
based login to administration tools in the Designer as
required.
l Enable or disable additional configuration parameters in
the Designer as required.
l Create custom processes in the Designer as required.
l Create and configure schedules as required.
l Create and configure password policies as required.

Target system Target system administrators must be assigned to the Target

administrators systems | Administrators application role.
Users with this application role:

l Administrate application roles for individual target

systems types.
l Specify the target system manager.
l Set up other application roles for target system managers
if required.
l Specify which application roles for target system
managers are mutually exclusive.
l Authorize other employee to be target system
administrators.
l Do not assume any administrative tasks within the target
system.

Target system Target system managers must be assigned to the Target

managers systems | Custom target systems application role or a
child application role.
Users with this application role:

l Assume administrative tasks for the target system.

l Create, change, or delete target system objects, like user
accounts or groups.
l Edit password policies for the target system.
l Prepare groups for adding to the IT Shop.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 6
Native database connector for SQL Server databases
User Tasks

l Can add employees, who have an other identity than the

Primary identity.
l Configure synchronization in the Synchronization Editor
and defines the mapping for comparing target systems
and One Identity Manager.
l Edit the synchronization's target system types and
outstanding objects.
l Authorize other employees within their area of
responsibility as target system managers and create child
application roles if required.

Table 3: User and permissions groups for role-based login: Mapped as default
tables

User Tasks

One Identity Manager l Create customized permissions groups for application

administrators roles for role-based login to administration tools in the
Designer as required.
l Create system users and permissions groups for non role-
based login to administration tools in the Designer as
required.
l Enable or disable additional configuration parameters in
the Designer as required.
l Create custom processes in the Designer as required.
l Create and configure schedules as required.
l Create and configure password policies as required.

Custom application role Users with this application role:

l Configure and start synchronization in the

Synchronization Editor.
l Edit the synchronization's target system types as well as
outstanding objects in the Manager.

This application role gets its write access through a custom

permissions group and the vi_4_SYNCPROJECT_ADMIN
permissions group.

Table 4: Users and permissions groups for role-based login: Mapped in custom
tables

User Tasks

One Identity Manager l Create customized permissions groups for application

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 7
Native database connector for SQL Server databases
User Tasks

administrators roles for role-based login to administration tools in the

Designer as required.
l Create system users and permissions groups for non role-
based login to administration tools in the Designer as
required.
l Enable or disable additional configuration parameters in
the Designer as required.
l Create custom processes in the Designer as required.
l Create and configure schedules as required.
l Create and configure password policies as required.

Application roles for Administrators must be assigned to the Custom |

custom tasks Administrators application role.
Users with this application role:

l Administrate custom application roles.

l Set up other application roles for managers if required.

Manager for custom Managers must be assigned to the Custom | Managers

tasks application role or a child role.
Users with this application role:

l Add custom task in One Identity Manager.

l Configure and start synchronization in the

Synchronization Editor.
l Edit the synchronization's target system types as well as
outstanding objects in the Manager.

You can use these application roles, for example, to guarantee

One Identity Manager users write permissions on custom tables
or columns. All application roles that you define here must
obtain their write permissions through custom permissions
groups.
This application role gets its write access through a custom
permissions group and the vi_4_SYNCPROJECT_ADMIN
permissions group.

To configure synchronization projects and target system synchronization (in the

use cases 2 and 3)

1. Set up a custom permissions group with all permissions for configuring

synchronization and editing synchronization objects.
2. Assign a custom application role to this permissions group.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 8
Native database connector for SQL Server databases
Detailed information about this topic

l Setting up a custom application role for synchronization on page 9

Setting up a custom application role for

synchronization
For role-based login, create a custom application role to guarantee One Identity Manager
users the necessary permissions for configuring synchronization and handling outstanding
objects. This application role obtains the required permissions by using a custom
permissions group.

To set up an application role for synchronization (use case 2):

1. In the Manager, select the default application role to use to edit the objects you want
to synchronization.
l Establish the application role's default permissions group.
If you want to import employee data, for example, select the Identity
Management | Employees | Administrators application role. The default
permissions group of this application role is vi_4_PERSONADMIN.
2. In the Designer, create a new permissions group.
l Set the Only use for role based authentication option.
3. Make the new permissions group dependent on the vi_4_SYNCPROJECT_ADMIN
permissions group.
The vi_4_SYNCPROJECT_ADMIN permissions groups must be assigned as the parent
permissions group. This means that the new permissions group inherits the
properties.
4. Make the new permissions group dependent on the default permissions group of the
selected default application role.
The default permissions group must be assigned as a subgroup. This means that the
new permissions group inherits the properties.
5. Save the changes.
6. In the Manager, create a new application role.
a. Assign the selected application role to be the parent application role.
b. Assign the new permissions group.
7. Assign employees to this application role.
8. Save the changes.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 9
Native database connector for SQL Server databases
To set up an application role for synchronization (use case 3):

1. In the Designer, create a new permissions group for custom tables, which are
populated though synchronization.
l Set the Only use for role based authentication option.
2. Guarantee this permissions group all the required permissions to the custom tables.
3. Create another permissions group for synchronization.
l Set the Only use for role based authentication option.
4. Make the permissions group for synchronization dependent on the permissions group
for custom tables.
The permissions group for custom tables must be assigned as parent permissions
group. This means the permissions groups for synchronization inherits its properties.
5. Make the permissions group for synchronization dependent on the vi_4_
SYNCPROJECT_ADMIN permissions group.
The vi_4_SYNCPROJECT_ADMIN permissions groups must be assigned as the parent
permissions group. This means the permissions groups for synchronization inherits
its properties.
6. Save the changes.
7. In the Manager, create a new application role.
a. Assign the Custom | Managers application role as the parent
application role.
b. Assign the permissions group for the synchronization.
8. Assign employees to this application role.
9. Save the changes.

For detailed information about setting up application roles and permissions groups, see the
One Identity Manager Authorization and Authentication Guide.

Setting up the synchronization server

A server with the following software must be available for setting up synchronization:

l One Identity Manager Service

l Install One Identity Manager components with the installation wizard.
1. Select Select installation modules with existing database.
2. Select the Server | Job server machine role.
For more detailed information about system requirements for installing the One
Identity Manager Service, see the One Identity Manager Installation Guide.

The synchronization server must be declared as a Job server in One Identity Manager.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 10
Native database connector for SQL Server databases
Use the One Identity Manager Service to install the Server Installer. The program executes
the following steps:

l Sets up a Job server.

l Specifies machine roles and server function for the Job server.
l Remotely installs One Identity Manager Service components corresponding to the
machine roles.
l Configures the One Identity Manager Service.
l Starts the One Identity Manager Service.

NOTE: To generate processes for the Job server, you need the provider, connection
parameters, and the authentication data. In the default case, this information is determ-
ined from the database connection data. If the Job server runs through an application
server, you must configure extra connection data in the Designer. For detailed inform-
ation about setting up Job servers, see the One Identity Manager Configuration Guide.
NOTE: The program executes remote installation of the One Identity Manager Service.
Local installation of the service is not possible with this program. Remote installation is
only supported within a domain or a trusted domain.
To remotely install the One Identity Manager Service, you must have an
administrative workstation on which the One Identity Manager components are
installed. For detailed information about installing a workstation, see the One Identity
Manager Installation Guide.

To remotely install and configure One Identity Manager Service on a server

1. Start the program Server Installer on your administrative workstation.

2. Enter the valid connection credentials for the One Identity Manager database on the
Database connection page.
3. Specify the server on which you want to install One Identity Manager Service on the
Server properties page.
a. Select a Job server from the Server menu.
- OR -
To create a new Job server, click Add.
b. Enter the following data for the Job server.
l Server: Name of the Job server.
l Queue: Name of the queue to handle the process steps. Each One
Identity Manager Service within the network must have a unique queue
identifier. The process steps are requested by the Job queue using
exactly this queue name. The queue identifier is entered in the One
Identity Manager Service configuration file.
l Full server name: Full server name in accordance with DNS syntax.
Syntax:
<Name of servers>.<Fully qualified domain name>

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 11
Native database connector for SQL Server databases
 NOTE: You can use the Extended option to make changes to other properties
for the Job server. You can also edit the properties later with the Designer.
4. On the Machine roles page, select Job server.
5. On the Server functions page, select Native database connector.
6. On the Service Settings page, enter the connection data and check the One Identity
Manager Service configuration.
NOTE: The initial service configuration is predefined already. If further changes
need to be made to the configuration, you can do this later with the Designer. For
detailed information about configuring the service, see the One Identity Manager
Configuration Guide.
l For a direct connection to the database:
a. Select Process collection | sqlprovider.
b. Click the Connection parameter entry, then click the Edit button.
c. Enter the connection data for the One Identity Manager database.
l For a connection to the application server:
a. Select Process collection, click the Insert button and select
AppServerJobProvider.
b. Click the Connection parameter entry, then click the Edit button.
c. Enter the connection data for the application server.
d. Click the Authentication data entry and click the Edit button.
e. Select the authentication module. Depending on the authentication
module, other data may be required, for example, user and password.
For detailed information about the One Identity Manager authentication
modules, see the One Identity Manager Authorization and
Authentication Guide.
7. To configure remote installations, click Next.
8. Confirm the security prompt with Yes.
9. On the Select installation source page, select the directory with the install files.
10. On the Select private key file page, select the file with the private key.
NOTE: This page is only displayed when the database is encrypted.
11. On the Service access page, enter the service's installation data.
l Computer: Name or IP address of the server that the service is installed and
started on.
l Service account: User account data for the One Identity Manager Service.
l To start the service under the NT AUTHORITY\SYSTEM account, set
the Local system account option.
l To start the service under another account, disable the Local system

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 12
Native database connector for SQL Server databases
 account option and enter the user account, password and password
confirmation.
l Installation account: Data for the administrative user account to install
the service.
l To use the current user’s account, set the Current user option.
l To user another user account, disable the Current user option and enter
the user account, password and password confirmation.
l To change the install directory, names, display names or description of the One
Identity Manager Service, use the other options.
12. Click Next to start installing the service.
Installation of the service occurs automatically and may take some time.
13. Click Finish on the last page of the Server Installer.
NOTE: In a default installation, the service is entered in the server’s service
management with the name One Identity Manager Service.

Prerequisites and notes for connecting

a One Identity Manager database as a
target system
The native database connector can also be used to synchronize One Identity Manager
databases with different product versions or modules. The following prerequisites apply for
connecting this type of database:

l If the two databases have different One Identity Manager versions, the database with
the earlier version must be connected as the target system. This means that
synchronization is configured on the database with the newer version.
l To have write access to the target system database, this database must
l Be connected through an application server
l Have at least Version 7.0.
l For data changes in the target system database, the REST API of the application
server is used. The HTTP request methods POST, GET, PUT and DELETE must be
permitted by the application server’s web server.
l The following applies for encrypted databases:
l Both databases to be synchronized use the same private key.
l The encrypted data is transmitted in encrypted form during synchronization.
The data is not decrypted in this process.
l The following applies to synchronizing in the Target system direction:

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 13
Native database connector for SQL Server databases
 Objects that are only in the target system database cannot be marked as outstanding
in the target system. The MarkAsOutstanding processing method is not available for
the synchronization steps.

Creating a synchronization project

A synchronization project collects all the information required for synchronizing the One
Identity Manager database with a target system. Connection data for target systems,
schema types and properties, mapping, and synchronization workflows all belong to this.
Make the following information available for setting up a synchronization project for
synchronizing with the native database connector.

Table 5: Information Required for Setting up a Synchronization Project

Data Explanation

Synchronization All One Identity Manager Service actions are executed against
server the target system environment on the synchronization server.
Data entries required for synchronization and administration
with the One Identity Manager database are processed by the
synchronization server.
Installed components:

l One Identity Manager Service (started)

The synchronization server must be declared as a Job server in

One Identity Manager. The Job server name is required.
For more information, see Setting up the synchronization server
on page 10.

Remote connection To configure synchronization with a target system, One Identity

server Manager must load the data from the target system. One
Identity Manager communicates directly with the target system
to do this. Sometimes direct access from the workstation on
which the Synchronization Editor is installed is not possible,
because of the firewall configuration, for example, or because
the workstation does not fulfill the necessary hardware and
software requirements. If direct access is not possible from the
workstation, you can set up a remote connection.
The remote connection server and the workstation must be in
the same Active Directory domain.
Remote connection server configuration:

l One Identity Manager Service is started

l RemoteConnectPlugin is installed

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 14
Native database connector for SQL Server databases
Data Explanation

The remote connection server must be declared as a Job server

in One Identity Manager. The Job server name is required.
TIP: The remote connection server requires the same config-
uration as the synchronization server (with regard to the
installed software and entitlements). Use the synchronization
as remote connection server at the same time, by simply
installing the RemoteConnectPlugin as well.
For more detailed information about setting up a remote
connection, see the One Identity Manager Target System
Synchronization Reference Guide.

Synchronization Set the option Data import in the synchronization step if

workflow synchronization data is imported from a secondary system. If a
One Identity Manager database is connected as a target system,
this option works in both directions, that is, also including
synchronization with the target system.
For more detailed information about synchronizing user data
with different systems, see the One Identity Manager Target
System Synchronization Reference Guide.

Base object You cannot normally specify a base object for synchronizing with
database connectors. In this case, assignment of one base table
and the synchronization server is sufficient.

l Select the Base table from the menu in which to load the
objects. The base table can be used to defined
downstream processes for synchronization. For more
information about downstream processes, see the One
Identity Manager Target System Synchronization
Reference Guide.
l The Synchronization servers menu displays all Job
servers for which the Native database connector
server function is activated.

Variable set If you implement specialized variable sets, ensure that the start
up configuration and the base object use the same variable set.

To configure synchronization with the native database connector

1. Create a new synchronization project.

2. Add mappings. Define property mapping rules and object matching rules.
3. Create synchronization workflows.
4. Create a start up configuration.
5. Define the synchronization scope.
6. Specify the base object of the synchronization.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 15
Native database connector for SQL Server databases
 7. Specify the extent of the synchronization log.
8. Run a consistency check.
9. Activate the synchronization project.
10. Save the new synchronization project in the database.

Detailed information about this topic

l How to set up a synchronization project on page 16

How to set up a synchronization project

There is an wizard to assist you with setting up a synchronization project. This wizard takes
you all the steps you need to set up initial synchronization with a target system. Click Next
once you have entered all the data for a step.
NOTE: The following sequence describes how you configure a synchronization project if
Synchronization Editor is both:

l Executed in default mode

l Started from the launchpad

If you execute the project wizard in expert mode or directly from Synchronization Editor,
additional configuration settings can be made. Follow the project wizard instructions
through these steps.

To set up a synchronization project

1. Start the Launchpad and log on to the One Identity Manager database.
NOTE: If synchronization is executed by an application server, connect the
database through the application server.

2. Select Native Database Connector and click on Run.

This starts the Synchronization Editor's project wizard.

3. On the System access page, specify how One Identity Manager can access the
target system.
l If access is possible from the workstation on which you started
Synchronization Editor, you do not need to make any settings.
l If access is not possible from the workstation on which you started
Synchronization Editor, you can set up a remote connection.
Enable the Connect using remote connection server option and select the
server to be used for the connection under Job server.

l Click Next to start the system connection wizard to create a connection to an

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 16
Native database connector for SQL Server databases
 external database.

4. Select the database system to which you want to connect on the Select database
system page.
l Select SQL Server.
5. Configure the system connection.
For more information, see Connecting a system to an SQL Server database on
page 18.
6. You can save the current configuration as a template on the Save configuration
page. When you reconnect to a database system of the same type, you can use this
configuration as a template.
l Click and enter the name and repository of the configuration file.
7. You can save the connection data on the last page of the system connection wizard.
l Set the Save connection locally option to save the connection data. This can
be reused when you set up other synchronization projects.
l Click Finish, to end the system connection wizard and return to the
project wizard.

8. On the One Identity Manager Connection tab, test the data for connecting to the
One Identity Manager database. The data is loaded from the connected database.
Reenter the password.
NOTE: If you use an unencrypted One Identity Manager database and have not
yet saved any synchronization projects to the database, you need to enter all
connection data again. This page is not shown if a synchronization project
already exists.
9. The wizard loads the target system schema. This may take a few minutes depending
on the type of target system access and the size of the target system.

10. Select a project template on the Select project template page to use for setting up
the synchronization configuration.
NOTE: The native database connector does not provide a default project template
for setting up synchronization. If you have created your own project template, you
can select it to configure the synchronization project. Otherwise, select Create
blank project.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 17
Native database connector for SQL Server databases
 11. Enter the general setting for the synchronization project under General.

Table 6: General properties of the synchronization project

Property Description

Display Display name for the synchronization project.

name

Script Language in which the scripts for this synchronization project are
language written.
Scripts are implemented at various points in the synchronization
configuration. Specify the script language when you set up an empty
project.
IMPORTANT: You cannot change the script language once the
synchronization project has been saved.
If you use a project template, the template's script language is
used.

Description Spare field for additional explanation.

12. To close the project wizard, click Finish.

13. Save the synchronization project in the database.

Connecting a system to an SQL Server

database
Table 7: Required information for connecting the system

Data Explanation

Server Name of the server on which the database server is installed.

The fully qualified server name or the IP address may be given.

User and password User account and password used by the native database
connector to log in to the external database. Make a user
account available with sufficient permissions.

Database Name of the external database to be synchronized.

Windows Specifies whether integrated Windows authentication is used.
authentication This type of authentication is not recommended. If you decide to
use it anyway, ensure that your environment supports Windows
authentication.

URL Web address for the application server if a One Identity Manager
database is to be connected as the target system

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 18
Native database connector for SQL Server databases
Data Explanation

Synchronization user's Password of the Synchronization default system user if a One

password Identity Manager database is to be connected as the target
system

To configure the connection to an SQL Server database

1. Enter the connection parameters on the Database connection page. Enter all the
parameters required by the database connector to create a connection with the
selected database system.
l To enter additional system-specific information about the system connection,
click Advanced.
The database system connection is tested the moment you click Next.

2. Enter a display name and a unique identifier for the database connection on the
Describe the database page.

Table 8: Name of the database

Property Description

Display name of Display name of the database for display in the One Identity
database Manager tools.

System identifier Unique identifier of the database.

IMPORTANT: The system identifier of the database must
be unique. These identifiers help to differentiate between
the databases. To prevent incorrect behavior and loss of
data ensure that the system identifiers are unique within
the One Identity Manager environment.
l Identifiers may not be defined more than once.
l Identifiers must not be changed after the connection
is saved.

3. You can enter a file on the Load configuration page from which the connection
configuration can be loaded. This data is used in subsequent steps in the connection
wizard and can be modified there.
4. Select the time zone for the time zone data in the database on the page, Time zone
selection. The time zone is required to convert the time saved in the database into
the local time. The local time is displayed in the One Identity Manager tools.
5. You can specify additional connection settings on the Initializing page. Write a
script in the database syntax to specify number and date formats, language, and
data sort order, for example. This script is then executed every time you
connect the system.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 19
Native database connector for SQL Server databases
6. On the Select partial schemas page, you can reduce the database schema by
selecting partial schemas. If the database contains several schema, specify here,
which schemas are loaded into the synchronization project.
l Enable all the schemas to process in the Partial schemas/owner list.

7. The database schema is loaded on the Schema detection page. during which One
Identity Manager tries to identify a known schema.
l If a One Identity Manager schema is detected, the Fill in system
description completely option is displayed. If you only want allow read-only
access to the database, you can deactivate this option.
If the schema is loaded successfully, the next step in the sequence can be
carried out.

8. The Configure system access page opens when you enable the Fill in system
description completely option on the Load schema page. Enter the connection
data for the application server of the target system database.

Table 9: Connection data for the application server

Property Description

URL Web address for the application server

Synchronization user's Password of the Synchronization default system

password user

l Click Test connection to test the connection data.

9. The following pages are displayed if no One Identity Manager schema was
detected. This information is determined automatically if a One Identity Manager
scheme was detected.
a. On the Extend key information page, specify columns for each table to be
used as unique keys for identifying objects.
NOTE:
l This page is only displayed if the schema of the external database
there are tables with no identifiable unique keys.
l Tables without unique keys are not used in the synchronization
configuration.

Table 10: Defining unique keys

Property Description

Hide Specifies whether table are hidden if no settings have been

unconfigured changed.
tables

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 20
Native database connector for SQL Server databases
 Property Description

Schema Tables without a unique key.

Column is Specifies whether the column contains a unique key.

key

Column Button for editing column groups. Create a column group, if a

group unique key can only be made of a combination of more than
one column.
l To create a column group, click Add
l To edit or remove an existing column group, click Edit
or remove

Table 11: Column group properties

Property Description

Key name Column group identifier. Permitted characters are letters and
underscore. A virtual schema property is formed from the column
group with the name vrtColumnGroup<column group>.

Columns Columns included in the column group. Mark all the columns that
together make up the unique key.

b. You can enter information about object relations in the Define data
relations page.

Table 12: Defining column relations

Property Description

Hide Specifies whether table are hidden if no settings have been

unconfigured changed.
tables

Schema Database schema tables.

Target(s) Columns to which the reference refers. Enter table and column
names in the following syntax: [<schema>].<table
name>.<column name>. If a reference points to several column,
enter the targets in a comma delimited list. The target
columns must be labeled as key columns.
TIP: You can copy the column name of a referenced column
using the Copy fully qualified column names item in the
context menu and add this as a target.

Referential Specifies whether the referential integrity of the data in the

integrity target table has been tested.
enabled

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 21
Native database connector for SQL Server databases
c. You can enter additional schema information on the Complete schema page.

Table 13: Additional schema information

Property Description

Hide Specifies whether table are hidden if no settings have been

unconfigured changed.
tables

Schema Tables and schemas of the database schema.

Display Column used in the display pattern.

value
l To use the column in the display pattern, click Add.

Preferred Specifies whether the column is to be primarily used for object

key identification. A preferred key can defined, if a table has more
than one unique key. Only columns with the String data type
can be selected.

Contains Specifies whether the column contains sensitive data.

sensitive
data

Revision Specifies whether the column contains the revision counter.

counter The data in this column form the comparison value for revision
filtering.

Sort criteria Specifies whether the value in this column represents the path
for in an object hierarchy. If this table’s objects are sorted by this
hierarchies column, it results in a list sorted in hierarchical order. This
makes it possible to resolve object dependencies. Only one
column per table can be marked as a sort criterion. An
example is the CanonicalName column.

Scope Specifies whether the column can be used to form the

reference reference scope. Only one column per schema type can be
labeled as the reference scope.

Table 14: Table properties

Property Description

Display Display pattern with which the objects in Synchronization

template Editor are displayed. The display pattern is, for example,
used in error messages or test result from object matching
rules. The display pattern is, for example, used in error
messages or in the test results from object matching rules.
Enter a display table for each display pattern.
l To use a column in the display pattern, select a
column and click Add.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 22
Native database connector for SQL Server databases
10. You can specify special operations for changing data in the external database on the
Define data operations page. This is only required, if the default operations
INSERT, UPDATE and DELETE cannot be used in the external database system.

WARNING: A good knowledge of programming is required to

implement data operations. Errors in this implementation can lead to
loss of data.

To define a data operation

a. Select a table and mark the operation you want to define.
b. Select a strategy.
c. Enter the data operation you want to run in the Settings input field.

Table 15: Defining data operations

Property Description

Hide unconfigured tables Specifies whether table are hidden if no settings

have been changed.

Table/operation Tables for which the data operations are to be

defined.

Strategy Strategy with which the data operation is created

and run. A simple procedure can be called for a data
operation or a script can be executed. Select the
strategy you want use to define the data operation.

Table 16: Strategies for running data opera-

tions

Strategy Description

Pattern- Simple procedure call that runs the

based operation.

Script- Script that runs a complex data

based operation.
You can use custom code snippets in
the script. The code snippets must
contain a keyword element with the
DML keyword. For more detailed
information about support for writing
scripts, see the One Identity Manager
Target System Synchronization
Reference Guide.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 23
Native database connector for SQL Server databases
 Property Description

l Click to delete a data operation.

Required columns List of required key columns in a script-based data

operation. The columns must be entered if they are
not part of the display name.

Settings Define the data operation that is to be run when

objects are added, updated, or deleted. Enter the
procedure call or create a script depending on the
selected strategy.
Example of a pattern-based data operation:
exec CreateUser
('%Uid%','%FirstName%','%LastName%')
It has an advanced edit mode which provides
additional actions. For detailed information about
support for creating scripts, see the One Identity
Manager Target System Synchronization Reference
Guide.

11. The Extend target system schema page opens if you enable the Fill in system
description completely option on the Load schema page or make settings on the
Define data operations page. You can add virtual schema properties to the target
system schema here. Use the virtual schema properties to provide additional data
for your own DML handling.

Table 17: Virtual schema properties

Property Description

Hide Specifies whether table are hidden if no settings have been changed.
unconfigured
tables

Schema Tables in the target system schema for which virtual schema proper-
ties can be added or exist already.

Virtual Buttons for editing virtual schema properties

schema
l Click Add to add a virtual schema property.
properties
l Click Edit or remove to edit or delete a virtual schema
property.

Data type Data type of the schema property

Multivalue Specifies whether the schema property has multiple values

Required Specifies whether the schema property is a mandatory property

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 24
Native database connector for SQL Server databases
 Property Description

field

Secret Specifies whether the schema property value may be displayed in

the Synchronization Editor or in logs, reports, and messages. If this
option is set, the value is not displayed.

To edit or delete a virtual schema property

a. In the Schema column, open the node of the table with the schema properties
that you want to edit or delete.
b. Click Edit or remove.
c. Edit the properties of the virtual schema property.
- OR -
Click Delete.

12. The Extend target system schema page opens if you enable the Fill in system
description completely option on the Load schema page or make settings on the
Define data operations page. You can add virtual schema properties to the target
system schema here. Use the virtual schema properties to provide additional data
for your own DML handling.

Table 18: Virtual schema properties

Property Description

Hide Specifies whether table are hidden if no settings have been changed.
unconfigured
tables

Schema Tables in the target system schema for which virtual schema proper-
ties can be or are created

Virtual Buttons for editing virtual schema properties

schema
l Click Add to add a virtual schema property.
properties
l Click Edit or remove to edit or delete a virtual schema
property.

Data type Data type of the schema property

Multivalue Specifies whether the schema property has multiple values

Required Specifies whether the schema property is a mandatory property

field

Secret Specifies whether the schema property value may be displayed in

the Synchronization Editor or in logs, reports and messages. If this
option is set, the value is not displayed.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 25
Native database connector for SQL Server databases
 To edit or delete a virtual schema property
a. In the Schema column, open the node of the table with the schema properties
that you want to edit or delete.
b. Click Edit or remove.
c. Edit the properties of the virtual schema property.
- OR -
Click Delete.

Related topics

l How to set up a synchronization project on page 16

Updating schemas
All the schema data (schema types and schema properties) of the target system schema
and the One Identity Manager schema are available when you are editing a
synchronization project. Only a part of this data is really needed for configuring
synchronization. If a synchronization project is finished, the schema is compressed to
remove unnecessary data from the synchronization project. This can speed up loading the
synchronization project. Deleted schema data can be added to the synchronization
configuration again at a later point.
If the target system schema or the One Identity Manager schema has changed, these
changes must also be added to the synchronization configuration. Then the changes can be
added to the schema property mapping.
To include schema data that have been deleted through compressing and schema
modifications in the synchronization project, update each schema in the synchronization
project. This may be necessary if:

l A schema was changed by:

l Changes to a target system schema
l Customizations to the One Identity Manager schema
l A One Identity Manager update migration
l A schema in the synchronization project was shrunk by:
l Enabling the synchronization project
l Saving the synchronization project for the first time
l Compressing a schema

To update a system connection schema

1. Select the Configuration | Target system category.

- OR -

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 26
Native database connector for SQL Server databases
 Select the Configuration | One Identity Manager connection category.
2. Select the General view and click Update schema.
3. Confirm the security prompt with Yes.
This reloads the schema data.

To edit a mapping

1. Select the Mappings category.

2. Select a mapping in the navigation view.
Opens the Mapping Editor. For more detailed information about mappings, see the
One Identity Manager Target System Synchronization Reference Guide.

NOTE: The synchronization is deactivated if the schema of an activated synchronization

project is updated. Reactivate the synchronization project to synchronize.

Starting synchronization
Synchronization is started using scheduled process plans. A scheduled process plan is
added once a start up configuration is assigned to a schedule. Use schedules to define
executing times for synchronization.
NOTE: Synchronization can only be started if the synchronization project is enabled.
To execute synchronization regularly, configure, and activate the a schedule. You can also
start synchronization manually if there is no active schedule.
IMPORTANT: As long as synchronization is running, you must not start another
synchronization for the same target system. This applies especially, if the same
synchronization objects would be processed.

l If another synchronization is started with the same start up configuration, this

process is stop and is assigned the Frozen execution status. An error message is
written to the One Identity Manager Service log file.
l Ensure that start up configurations that are used in start up sequences are
not started individually at the same time. Assign start up sequences and start
up configurations different schedules.
l If another synchronization is started with another start up configuration, that
addresses same target system, it may lead to synchronization error or loss of data.
Specify One Identity Manager behavior in this case, in the start up configuration.
l Use the schedule to ensure that the start up configurations are executed
in sequence.
l Group start up configurations with the same start up behavior.

If you want to specify the order in which target systems are synchronized, use the start up
sequence to run synchronization. In a start up sequence, you can combine start up
configurations from different synchronization projects and specify the order of execution.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 27
Native database connector for SQL Server databases
For detailed information about start up configurations, see the One Identity Manager Target
System Synchronization Reference Guide.

Analyzing synchronization
Synchronization results are summarized in the synchronization log. You can specify the
extent of the synchronization log for each system connection individually. One Identity
Manager provides several reports in which the synchronization results are organized under
different criteria.

To display a synchronization log

1. Open the synchronization project in the Synchronization Editor.

2. Select the Logs category.
3. Click in the navigation view toolbar.
Logs for all completed synchronization runs are displayed in the navigation view.
4. Select a log by double-clicking it.
An analysis of the synchronization is shown as a report. You can save the report.

Synchronization logs are stored for a fixed length of time.

To modify the retention period for synchronization logs

l In the Designer, enable the DPR | Journal | LifeTime configuration parameter and
enter the maximum retention period.

Post-processing outstanding objects

Objects, which do not exist in the target system, can be marked as outstanding in One
Identity Manager by synchronizing. This prevents objects being deleted because of an
incorrect data situation or an incorrect synchronization configuration.
Outstanding objects

l Cannot be edited in One Identity Manager.

l Are ignored by subsequent synchronization.
l Are ignored by inheritance calculations.

This means, all memberships and assignments remain intact until the outstanding objects
have been processed.
Start target system synchronization to do this.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 28
Native database connector for SQL Server databases
To allow post-processing of outstanding objects

l Configure target system synchronization.

For more information, see Configuring target system synchronization on page 29.

Related topics

l How to post-process outstanding objects on page 30

l Users and permissions for synchronizing on page 5

Configuring target system synchronization

Create a target system for post-processing outstanding objects. Assign tables you want to
be populated by synchronization, to this target system type. Specify the tables for which
outstanding objects can be published in the target system during post-processing. Define a
process for publishing the objects.

To create a target system type

1. In the Manager, select the Data Synchronization | Basic configuration data |

Target system types category.
2. Click in the result list.
3. Edit the target system type master data.
4. Save the changes.

Enter the following data for a target system type.

Table 19: Master data for a target system type

Property Description

Target system Target system type description.

type

Description Spare field for additional explanation.

Display name Name of the target system type as displayed in One Identity Manager
tools.

Cross-boundary Specifies whether user accounts can be assigned to groups if they

inheritance belong to different custom target systems.
NOTE: If this option is not set, the target system type is used to
group the target systems.

Show in compli- Specifies whether the target system type for compliance rule wizard
ance rule wizard can be selected when rule conditions are being set up.

Text snippet Text snippets used for linking text in the compliance rule wizard.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 29
Native database connector for SQL Server databases
To add tables to the target system synchronization

1. In the Manager, select the Data Synchronization | Basic configuration data |

Target system types category.
2. In the result list, select the target system type custom-defined target system.
3. Select the Assign synchronization tables task.
4. Assign custom tables whose outstanding objects you want to handle in .
5. Save the changes.
6. Select the Configure tables for publishing task.
7. Select tables whose outstanding objects can be published in the target system and
set the Publishable option.
8. Save the changes.

NOTE: The connector must have write access to the target system in order to publish
outstanding objects that are being post-processed. That means, the Connection is
read-only option must no be set for the target system connection.

To publish outstanding objects

l For each table for which you want to publish outstanding objects, create a process,
which is triggered by the event HandleOutstanding and which executes the
provisioning of the objects. Use the AdHocProjection process function of the
ProjectorComponent process component. For detailed information about defining
processes, see the One Identity Manager Configuration Guide.

How to post-process outstanding objects

To post-process outstanding objects

1. In the Manager, select Data synchronization | Target system synchronization:

<target system type>.
All tables assigned to the target system type are displayed in the navigation view.
2. Select the table whose outstanding objects you want to edit in the navigation view.
All objects marked as outstanding are shown on the form.
TIP:

To display object properties of an outstanding object

a. Select the object on the target system synchronization form.
b. Open the context menu and click Show object.

3. Select the objects you want to rework. Multi-select is possible.

4. Click one of the following icons in the form toolbar to execute the respective method.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 30
Native database connector for SQL Server databases
 Table 20: Methods for handling outstanding objects

Icon Method Description

Delete The object is immediately deleted in the One Identity Manager

database. Deferred deletion is not taken into account. The
Outstanding label is removed for the object.
Indirect memberships cannot be deleted.

Publish The object is added in the target system. The Outstanding label
is removed for the object.
The method triggers the HandleOutstanding event. This runs a
target system specific process that triggers the provisioning
process for the object.
Prerequisites:
l The table containing the object can be published.
l The target system connector has write access to the target
system.
l A custom process is set up for provisioning the object.

Reset The Outstanding label is removed for the object.

5. Confirm the security prompt with Yes.

NOTE: By default, the selected objects are processed in parallel, which speeds up
execution of the selected method. If an error occurs during processing, the action is
stopped and all changes are discarded.
Bulk processing of objects must be disabled if errors are to be localized, which means the
objects are processed sequentially. Failed objects are named in the error message. All
changes that were made up until the error occurred are saved.

To disable bulk processing

l Deactivate in the form toolbar.

Related topics

l Configuring target system synchronization on page 29

l Users and permissions for synchronizing on page 5

Configuring the provisioning of

memberships
Memberships, for example, user accounts in groups, are saved in assignment tables in the
One Identity Manager database. During provisioning of modified memberships, changes

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 31
Native database connector for SQL Server databases
made in the target system will probably be overwritten. This behavior can occur under the
following conditions:

l Memberships are saved in the target system as an object property in list form
(Example: List of user accounts in the Members property of an Active Directory group).
l Memberships can be modified in either of the connected systems.
l A provisioning workflow and provisioning processes are set up.

If a membership in One Identity Manager changes, the complete list of members is

transferred to the target system by default. Memberships, previously added to the target
system are removed by this; previously deleted memberships are added again.
To prevent this, provisioning can be configured such that only the modified membership is
provisioned in the target system. The corresponding behavior is configured separately for
each assignment table.

To allow separate provisioning of memberships

1. In the Manager, select the Data Synchronization | Basic configuration data |

Target system types category.
2. Select the Configure tables for publishing task.
3. Select the assignment tables for which you want to allow separate provisioning.
Multi-select is possible.
l This option can only be enabled for assignment tables that have a base table
with XDateSubItem or CCC_XDateSubItem column.
l Assignment tables that are grouped together in a virtual schema property in
the mapping must be marked identically (for example, ADSAccountInADSGroup,
ADSGroupInADSGroup and ADSMachineInADSGroup).
4. Click Enable merging.
5. Save the changes.

For each assignment table labeled like this, the changes made in One Identity Manager are
saved in a separate table. During modification provisioning, the members list in the target
system is compared to the entries in this table. This means that only modified
memberships are provisioned and the members list does not get entirely overwritten.
NOTE: The complete members list is updated by synchronization. During this process,
objects with changes but incomplete provisioning are not handled. These objects are
logged in the synchronization log.
You can restrict single provisioning of memberships with a condition. Once single provi-
sioning has been disabled for a table, the condition is deleted. Table that have had the
condition deleted or edited are marked with the following icon: . You can restore the
original condition at any time.

To restore the default condition

1. Select the auxiliary table for which you want to restore the condition.
2. Right-click on the selected row and select the Restore original values

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 32
Native database connector for SQL Server databases
 context menu item.
3. Save the changes.

For more detailed information about provisioning memberships, see the One Identity
Manager Target System Synchronization Reference Guide.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 33
Native database connector for SQL Server databases
 2

Error handling

For detailed information about correcting errors during synchronization of object

hierarchies, see the One Identity Manager Target System Synchronization Reference
Guide.

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 34
Error handling
 About us

About us

One Identity solutions eliminate the complexities and time-consuming processes often
required to govern identities, manage privileged accounts and control access. Our solutions
enhance business agility while addressing your IAM challenges with on-premises, cloud and
hybrid environments.

Contacting us
For sales and other inquiries, such as licensing, support, and renewals, visit
[Link]

Technical support resources

Technical support is available to One Identity customers with a valid maintenance contract
and customers who have trial versions. You can access the Support Portal at
[Link]
The Support Portal provides self-help tools you can use to solve problems quickly and
independently, 24 hours a day, 365 days a year. The Support Portal enables you to:

l Submit and manage a Service Request

l View Knowledge Base articles
l Sign up for product notifications
l Download software and technical documentation
l View how-to videos at [Link]/OneIdentity
l Engage in community discussions
l Chat with support engineers online
l View services to assist you with your product

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 35
About us
 Index
I ndex

A publish 30

application role 5 One Identity Manager version 13

application role for synchronztion 9 outstanding object 28

application server 13

P
B provisioning

base object 14 members list 31

D Q

database query method 13

encrypted 13
database connector R
native 4
remote connection server 14
REST API 13
E
encrypted database 13 S
schema
J changes 26

Job server shrink 26

edit 10 update 26
synchronization
start 27
M
synchronization configuration 14, 16
membership
synchronization log 28
modify provisioning 31
synchronization server 14
configure 10
O install 10
object Job server 10
delete immediately 30
outstanding 28, 30

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 36
Index
T
target system synchronization
table to assign 29
target system type 29

V
variable set 14

W
workflow 14

One Identity Manager 8.1.2 Native Database Connector User Guide for
Connecting SQL Server Databases 37
Index