SQL Server Connector

For more information: List of relational databases

If your system is not in the previous list, it's possible to include it easily!

For more information to check if your system may be synchronized with this connector youconnector, do not hesitate to contact us through our Contact form

We can also manage more relational databases, for more information you can check the List of relational databases.

Prerequisites

It is needed a user with access and permissions to the schemes and tables required in the scope of the integration.

To configure DB2/400  or Sybase it is mandatory to install the drivers in the lib directory of the Sync Server.

The Java-ODBC bridge is deprecated in Java, and the support will be removed shortly.

Download and Install

TheThis SQLaddon is partlocated ofin the defaultConnectors connectors,section youand doits notname needis to install it, but you can upgrade it from the download management section.SQLServer.

You can visit the Connector Getting started page forFor more information about the installation process.process, you can visit the Addons Getting started page.

Agent Configuration

This connector could manage User and Role objects.

Basic

Generic parameters

After the installation of the addon, you may create and configure agent instances.

To configure this SQL  Server connector you must select "Customizable SQLSQLServer agent" in the attribute "Type" of the generic parameters section in the agent's page configuration.

For more information about how you may configure the generic parameters of the agent, see the following link: Agents configuration

Custom parameters

Below there are the specific parameters for this agent implementation:

jdbc:mariadb://<HOST>/<DATA_BASE>

jdbc:mysql://<HOST>/<DATA_BASE>

jdbc:postgresql://<HOST>/<DATA_BASE>

jdbc:oracle:<drivertype>:@<database>

jdbc:sqlserver://<HOST>;databaseName=<DATA_BASE>

{SHA1}BzE/DjIPIsv6Nc/CIFCOs/9FfH4=

{SHA256}AIEM+LlNb8ucXeSE077EGHYgs+KHblmquQ2FL+Dxj7Y=

AttributeLoad mappingtriggers

This connector can manage users, accounts, roles, groups, and grants.

Properties

Some agents require to configure some custom attributes, you will use the properties section to do that.

Any SQL sentence gets its parameters in three step process:

1. The synchronization engine creates the Soffid object.
2. The Soffid object is translated into a managed system object, using the attribute translation rules.
3. Soffid parser looks for any identifier preceded by a colon (:) symbol. For any symbol found, the symbol is replaced by a parameter whose value is the managed system attribute with the replaced identifier.

Once the SQL sentence has been executed, in the case of SELECT clauses, the column names are used to generate a virtual managed system object. The last step is to apply the attribute translation to generate the Soffid object to be populated.

These are the properties required to map every object of the mapping:

SELECT * FROM USERS

SELECT * FROM ROLES

SELECT ID FROM USERS WHERE USER=:USER

SELECT ID FROM ROLES WHERE ROLE=:ROLE

INSERT INTO USERS VALUES (:USER, :FIRST_NAME, :LAST_NAME, :MAIL, :GROUP)

INSERT INTO USER_ROLES (USETNAME, ROLNAME) VALUES (:USERNAME, :ROLNAME)

UPDATE USERS SET FIRST_NAME=:FIRST_NAME, LAST_NAME=:LAST_NAME, MAIL=:MAIL, GROUP=:GROUP WHERE ID=:ID

UPDATE ROLES SET DESCRIPTION=:DESCRIPTION WHERE ROLE=:ROLE

DELETE FROM USERS WHERE ID=:ID

DELETE FROM USER_ROLES WHERE ID=:ID

SELECT * FROM USER_ROLES WHERE USERNAME=:USER

SELECT * FROM ROLES WHERE ROLE=:ROLE

UPDATE USERS SET PASS=:PASS WHERE USER=:USER

SELET 1 FROM USERS WHERE PASS=:PASS AND USER=:USER

Attributes

You can customize attribute mappings, you only need to select system objects and the Soffid objects related, manage their attributes, and make either inbound or outbound attribute mappings.

You may map the attributes of the target system with the Soffid available attributes.

• For the target system attributes are required to be accessible to its specification
• For the Soffid attributes, you may follow the next link

For more information about how you may configure attribute mapping, see the following link: Soffid Attribute Mapping Reference

Example for roles:

Example for accounts:

Triggers

You can define JavaScript or BeanShell scripts that will be triggered when data is loaded into the target systemSoffid (outgoingincoming triggers). The trigger result will be a boolean value, true to continue or false to stop.

Triggers can be used to validate or perform a specific action just before performing an operation or just after performing an operation oninto targetSoffid objects. 

To view some examples, visit the OutgoingIncoming triggers examples pagepage..

IntegrationAccess flowsControl

Update

SQL User

Visitconnector has the Integrationability flowsto Updateestablish an access control for SQL Server Users.

If the access control checkbox is enabled, only the users and applications that are listed on the access control page will be allowed to log in. So, you can restrict the IP address and application a user pagecan connect from.

This restriction does not apply to DBA users.

Account metadata

Agents allow you to create additional data, on the "Account metadata" tab, to customize the accounts created for morethat agent. This additional information

Updatewill Account

Visitloaded with the Integrationagent's flowsinformation, Updateor accountcalculated pageas fordefined morein informationthe mappings.
The additional data can be used in both mappings and triggers.

(*)that you define here will be shown when you click on the proper account, on the Accounts Tabs on the user page.

https://mariadb.com/kb/en/about-mariadb-connector-j/Operational

https://docs.microsoft.com/es-es/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16

Monitoring

(**)After Soffidthe providesagent twoconfiguration synchronizationyou types:can check on the monitoring page if the service is running in the Synchronization Server, please go to:

• FullStart synchronizationMenu > Administration > Monitoring and reporting > Syscserver monitoring

Tasks

Authoritative

If you checked "Authorized identity source", an automatic task to load identities from the managed system to Soffid is available, please go to:

• IncrementalStart synchronizationMenu > Administration > Monitoring and reporting > Scheduled tasks

TheAnd firstyou type,will do something like "Import authoritative data from <AGENT_NAME>".

Reconcile

To manage an automatic task to synchronize user objects from the fullmanaged system to Soffid is available, please go to:

• Start Menu > Administration > Monitoring and reporting > Scheduled tasks

And you will do something like "Reconcile all accounts from <AGENT_NAME>".

Synchronization

Regarding the synchronization method,of persiststhe objects, there are two possible options:

• If you check the generic attribute "Read Only" in the "Basics" tab, only the changes madein the managed systems will be updated in Soffid,Soffid. regardlessWe recommend these options until the global configuration of Soffid is tested.
• If you do not check the possiblegeneric changesattribute made"Read Only" in the target"Basics" system.
  tab,

  For the second type, the incremental synchronization method, Soffid has developed a synchronization system, using custom internal attributes, to check what changes have been made to the different attributes of an object. Thus, it tries to avoid losingall the changes thatin haveSoffid been made inor the target system. First, Soffid's changes will be propagated to the target system, and then the changes on the targetmanaged system will be madeupdated in the Soffidother. system.Note Ifthat thethis changessynchronization aremust be configured in the same"Attribute attribute,mapping" tab correctly.

For more information about how you may configure the Soffidgeneric valueparameters isof the oneagent, thatsee willthe persist.following link: Agents configuration