Importing entities from a database - Card Synchronization 4.0.0

You can import entities from an external system using a Microsoft SQL, Azure SQL, Oracle, or ODBC-compliant database that conforms to the Synergis™ Card Synchronization schema. After the import, changes made in that data source can be synchronized in Security Center.

Before you begin

Familiarize yourself with the database data source information in this guide.
Ensure that the plugin can log into the database server using either the ODBC connection string provided by your database administrator or (if using Microsoft SQL) granting access to Windows user running the Genetec™ Server service user on the plugin role server.
Ensure that the Genetec Server running the plugin role can reach the database server. If you are using Microsoft SQL, you can use the Microsoft SQL Management Studio tool to test your connection.
Complete the steps outlined in Preparing to import entities.

What you should know

Including pictures in the data source increases import time.
It is best practice to keep Transparent Data Encryption (TDE) enabled for Azure SQL.
It is best practice to use TLS version 1.2 for your Azure SQL configuration.
IMPORTANT: The Always Encrypted feature is unsupported for Card Synchronization 4.0.0.
Legacy data sources from Synergis™ Card Synchronization versions 3.0 and 3.1 do not support Azure SQL.
NOTE: If you are upgrading from version 3.0 or 3.1 of the Card Synchronization plugin, the data source configuration appears as they were in the previous version. For instructions on how to import these entities, refer to the plugin guide that came with the previous version of the plugin. To see the Data source configuration wizard shown in this topic, you must delete your data sources from the plugin, and then recreate them.

Procedure

From the Config Tool homepage, open the Plugins task.
In the Plugins task, select the Card Synchronization plugin role from the entity browser, and click the Properties tab.
In the Data sources section, click Add data source ().
The Data source configuration window opens.
From the Source list, select Database, and then click Next.

In the Configuration window, select a data source by doing the following:

For this type of connection:	Do this:
Microsoft SQL Server without a connection string	In the SQL server type list, select Microsoft SQL Server. Clear the Use custom connection stream checkbox. In the Server list, select or type the name of the database server and database. Example: `ServerName\SQLEXPRESS` In the Database list, select or type the name of the database instance. NOTE: When connecting to Microsoft SQL Server without a connection string, the plugin uses Integrated Windows Authentication. Make sure that the Windows user running the Genetec™ Server has access to the required database. To use SQL authentication, you must use a connection string.
Microsoft SQL Server with a connection string Azure SQL Server with a connection string	In the SQL server type list, select Microsoft SQL Server. Select the Use custom connection stream checkbox. Enter the connection string for your database. Example: `Data Source=myServer\myDatabase;Database=myDatabaseInstance; User Id=myUsername;Password=myPassword;` NOTE: For information on which parameters are valid, see SqlConnection.ConnectionString Property at the Microsoft Learn website.
ODBC connection to a database	In the SQL server type list, select ODBC. Enter the connection string for your database. Example: `DSN=myDSN;User Id=myUsername;Password=myPassword`; NOTE: Ask your database provider for the parameters and values needed to log into your database. When adding an ODBC data source for a Microsoft Access database, your data source must match the OS architecture (32-bit or 64-bit) of the server on which the plugin is running. Otherwise, you receive a validation error.
Oracle database	From the Oracle ODAC Runtime Downloads page, download and unzip the latest Managed ODP.NET package. Copy the file Oracle.ManagedDataAccess.dll from the Oracle package to the root folder of the plugin on your local system. By default, the location is C:\Program Files (x86)\Security Center Plugins\Card Synchronization. Restart the plugin. In the SQL server type list, select Oracle Database. Enter the connection string for your database. Example: `Data Source=MyOracleServerName:1521/MyOracleServiceName;User Id=myUsername;Password=myPassword;` NOTE: For information on which parameters are valid, search for "Connecting to Oracle Database" in the Oracle Help Center.

If you upgraded from Card Synchronization 3.0, in the External custom fields table name, select the name of the table in your database that contains the custom fields. If you do not have custom fields, leave this field blank.
If your data is formatted using a Card Synchronization 3.0 schema and you want to use the features found in Card Synchronization 4.0.0, select the table that contains the custom fields.
For data in the external database is configured according to the Card Synchronization 3.0 schema, in the External custom fields table name list, select the name of the table that contains your data.
Omit this step for a new installation of Card Synchronization.
Select the entity types to import and synchronize with Security Center, and then click Next.

Cardholders

Imports the cardholders from the external data source into Security Center.

Cardholder groups

Imports the cardholder groups from the external data source into Security Center.
IMPORTANT: This does not put cardholders into their respective cardholder groups.

Cardholder group memberships

Synchronizes the relationship of cardholders and cardholder groups from the external data source.
IMPORTANT: This option assigns imported cardholders to cardholder groups. If this option is not selected, imported cardholders are not linked to cardholder groups.

Credentials

Imports the credentials from the data source into Security Center.
From the Table name list, select the table you want to configure.
Map each external field to a Security Center field by selecting the column name from the corresponding drop-down.
For an explanation of the external fields, refer to the following topics:
- Cardholders schema.
- Cardholder groups schema.
- Cardholder group members schema.
- Credentials schema.
- (If applicable) Custom fields schema.
(Optional) Choose one or more fields to be global keys.
(Optional) If the external data contains terms that you want to replace, do the following:
1. Click Find and replace text using regular expressions ().
2. Enter the text to find and replace.
  For example, you could look for variations of a country name and replace them with a country code, or replace a native card format name with a supported card format alias.
3. If necessary, change the order using the up and down arrows.
  The transform expressions are processed in the order specified in the Add transform expressions for field dialog.
1. Click Save.
Tip: If required, you can select the row of any expressions that you no longer require, and click delete .

A script icon () is shown in the Sample value column when the field text is being replaced by a regular expression.
(Optional) Click Refresh () to update the external fields data from your source. This refresh option is used in situations where the existing data has been modified, new data rows have been added, or new attribute columns have been added.
If there are external fields without a corresponding Security Center field, you can create new custom fields and associate them now: Click Manage custom fields, fill in the form, and then click Save.

NOTE: To map an external field that is, turn on the Display all external fields option.
Click Next.
In the Data source name field, enter a name for the data source, and then click Finish.
Click Apply.

Results

When a synchronization occurs, the database data source is added to the list of data sources, and the entities are created in Security Center.