Version: Classic

SQL

Configuration

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 database types are supported via the ConnectionProvider element:

System.Data.Odbc
System.Data.SqlClient
MySqlConnector
Oracle.ManagedDataAccess.Client

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>

Configuration​

Configuration