SQL

---

User data from an SQL database can be synchronized via the SqlSyncSource. The connection is established via the ConnectionString element, which can also be stored encrypted.

The following ADO.NET providers are supported via the ConnectionProvider element. User synchronization runs server-side; the required provider libraries ship with the server and are registered by primedocs at startup — no additional installation is required:

• System.Data.SqlClient — Microsoft SQL Server
• MySqlConnector — MySQL/MariaDB (the older name MySql.Data.MySqlClient is still accepted as an alias)
• Oracle.ManagedDataAccess.Client — Oracle
• System.Data.Odbc — generic ODBC access; this additionally requires a matching ODBC driver/DSN for the target database to be present on the server (operating-system level)

The query is defined using the Query element. The queryKey is required as a parameter. The syntax in the Query element must correspond to the SQL database system:

Example: MS SQL

SELECT FirstName, LastName FROM Users WHERE Email = @queryKey

ℹ️ Info Mapping is supported as well.

---

Configuration

<?xml version="1.0" encoding="utf-8"?>
<UserSyncConfig>
  <SqlSyncSource name="Sql" queryKey="OneOffixxIdentifier">
    <ConnectionString>Data Source=sqlserver.local;Initial Catalog=SampleData;User ID=user;Password=pw;Encrypt=False</ConnectionString>
    <ConnectionProvider>System.Data.SqlClient</ConnectionProvider>
    <Query>SELECT [givenname],[surname], [email] FROM [Table] WHERE UserId = @queryKey</Query>
    
    <!-- Optional: Result Mapping Syntax is supported -->
    <ResultMapping>
      <Mapping>
        <Map Source="email" Target="fromMappingEMail" />
      </Mapping>
    </ResultMapping>
    
    <!-- Claim Mapping - column names are property names-->
    <Claims>
      <Claim type="http://schema.oneoffixx.com/ws/2011/01/identity/claims/givenname" ignoreClaimIfEmpty="true" property="givenname" />
      <Claim type="http://schema.oneoffixx.com/ws/2011/01/identity/claims/surname" ignoreClaimIfEmpty="true" property="surname" />
      <Claim type="http://schema.oneoffixx.com/ws/2011/01/identity/claims/email" ignoreClaimIfEmpty="true" property="fromMappingEMail" />
    </Claims>
  </SqlSyncSource>
</UserSyncConfig>

Options

In addition to the common SyncSource attributes (name, queryKey, …), the following elements are available:

Element	Required	Description
ConnectionString	yes	Connection string to the database. Can be stored encrypted.
ConnectionProvider	yes	Database driver, e.g. System.Data.SqlClient (see supported types above).
Query	yes	SQL query with the @queryKey parameter; the syntax must match the database system used.
ResultMapping	no	Optional mapping of the columns to target values.