Skip to main content

​
Add the SQL Server Connector

​
Authenticate to SQL Server

After you add the connector, you need to set the required properties.

CData Sync supports authenticating to SQL Server in several ways. Select your authentication method below to proceed to the relevant section that contains the authentication details.

​
Password

To connect with your user credentials, specify the following properties:
  • Auth Scheme: Select Password.
  • User: Enter the username that you use to authenticate to SQL Server.
  • Password: Enter the password that you use to authenticate to SQL Server.

​
NTLM

To connect with your NTLM user credentials, specify the following properties:
  • Auth Scheme: Select NTLM.
  • User: Enter the username that you use to authenticate to SQL Server.
  • Password: Enter the password that you use to authenticate to SQL Server.
  • (Optional) Domain: Enter the name of the domain for a Windows (NTLM) security login.
  • (Optional) NTLM Version: Select the NTLM version that you want to use. The default version is 1.

​
Kerberos

To connect with your Kerberos credentials, specify the following properties:
  • Auth Scheme: Select Kerberos.
  • User: Enter the username that you use to authenticate to SQL Server.
  • Password: Enter the password that you use to authenticate to SQL Server.
  • Kerberos KDC: Enter the this to the host name or IP Address of your Kerberos Key Distribution Center (KDC) machine.
  • Kerberos Realm: Enter the Kerberos realm that you use to authenticate to Kerberos.
  • Kerberos SPN: Enter the service principal name (SPN) for the Kerberos domain controller.
  • (Optional) Kerberos Keytab File: Enter the full file path to your Kerberos keytab file.
  • (Optional) Kerberos Ticket Cache: Enter the full file path to an MIT Kerberos credential cache file.

​
AzurePassword

To connect with your Azure user credentials, specify the following properties:
  • Auth Scheme: Select AzurePassword.
  • User: Enter the username that you use to authenticate to Azure.
  • Password: Enter the password that you use to authenticate to Azure.

​
Azure Active Directory

​
Azure Managed Service Identity

​
Azure Service Principal

To connect with an Azure service principal and client secret, set the following properties:

  • Auth Scheme: Select AzureServicePrincipal.
  • Azure Tenant: Enter the Microsoft Online tenant to which you want to connect.
  • OAuth Client Id: Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.
  • OAuth Client Secret: Enter the client secret that you were assigned when you registered your application with an OAuth authorization server.

To obtain the OAuth client Id and client secret for your application:

  1. Log in to the Azure portal.
  2. In the left navigation pane, select All services. Then, search for and select App registrations.
  3. Click New registrations.
  4. Enter an application name and select Any Azure AD Directory - Multi Tenant.
  5. After you create the application, copy the application (client) Id value that is displayed in the Overview section. Use this value as the OAuth client Id.
  6. Navigate to the Certificates & Secrets section and select New Client Secret for the application.
  7. Specify the duration and save the client secret. After you save it, the key value is displayed.
  8. Copy this value because it is displayed only once. You will use this value as the OAuth client secret.
  9. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

​
Azure Service Principal Certificate

To connect with an Azure service principal and client certificate, set the following properties:

  • Auth Scheme: Select AzureServicePrincipalCert.
  • Azure Tenant: Enter the Microsoft Online tenant to which you want to connect.
  • OAuth Client Id: Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.
  • OAuth JWT Cert: Enter your Java web tokens (JWT) certificate store.
  • OAuth JWT Cert Type: Enter the type of key store that contains your JWT Certificate. The default type is PEMKEY_BLOB.
  • (Optional) OAuth JWT Cert Password: Enter the password for your OAuth JWT certificate.
  • (Optional) OAuth JWT Cert Subject: Enter the subject of your OAuth JWT certificate.

To obtain the OAuth certificate for your application:

  1. Log in to the Azure portal.
  2. In the left navigation pane, select All services. Then, search for and select App registrations.
  3. Click New registrations.
  4. Enter an application name and select Any Azure AD Directory - Multi Tenant.
  5. After you create the application, copy the application (client) Id value that is displayed in the Overview section. Use this value as the OAuth client Id.
  6. Navigate to the Certificates & Secrets section and select Upload certificate. Then, select the certificate to upload from your local machine.
  7. Specify the duration and save the client secret. After you save it, the key value is displayed.
  8. Copy this value because it is displayed only once. You will use this value as the OAuth client secret.
  9. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

​
Complete Your Connection

To complete your connection:
  1. For the Database property (optional), enter the default database to which you want to connect when you connect to SQL Server.

​
Set Up SQL Server for Change Data Capture

SQL Server supports two methods for tracking the changes from your source database:
  • Change data capture (CDC): Change data capture tracks every change that is applied to a table and records those changes in a shadow history table. Rather than capturing only the primary key (for example, Change Tracking), CDC records the full row data to the history table.
  • Change tracking: Change tracking provides an efficient tracking mechanism for Sync. Once change tracking is configured on your tables, any DML statement that affects rows in the source table causes change-tracking information to be recorded to the change-tracking table for each modified row.
If both of these methods are enabled on a table, Sync uses CDC.
Follow the steps in the relevant section to set up your preferred method.

​
Enable Change Data Capture for CData Sync

SQL Server CDC must be enabled for the database and for each table that you want to replicate with CDC. The following sections explain how to enable CDC and how Sync handles the columns that are included in a CDC capture instance.

​
Enabling CDC

To use CDC for the SQL Server source in Sync, ensure that you have the following prerequisites in place:
  • The SQL Server agent must be running.
  • You must be a member of the db_owner fixed database role for the database.
After you verify these prerequisites, enable CDC on your database by submitting the following statements:
To enable CDC on an individual table, submit the following statements:
By default, this statement captures all columns.

​
Configuring CDC Captured Columns

In SQL Server, you can capture a subset of columns by adding the @captured_column_list parameter, as shown in this example:
When SQL Server CDC is configured to capture only a subset of columns, Sync automatically detects the captured column list and limits replication to those columns during the initial load and all subsequent CDC runs. In the column mapping, only the captured columns are displayed, and they are selected by default. When Sync transitions to a new capture instance after a schema change, it detects differences in the captured columns between the old and new instances. For information about how Sync transitions between capture instances, see Alter Schema.

​
Enable Change Tracking for CData Sync

You can enable change tracking on a database or on individual tables. To enable change tracking on your database, submit the following statement:
The CHANGE_RETENTION parameter specifies the time period for which change-tracking information is kept in your database. As a best practice, set a longer time frame to give Sync time to resolve conflicts and errors. If the last successful job run is outside the retention period, Sync replicates the full table automatically to ensure that no changes are missed. To enable change tracking on an individual table, submit the following statement:
To use change tracking, each table must have at least one primary key.

​
Alter Schema

The way that Sync handles schema changes for source tables depends on whether you use CDC or change tracking.

​
Change Data Capture

When you use CDC, Sync supports seamless transitions between SQL Server CDC capture instances after a schema change, allowing replication to continue without data loss or a full refresh. Because SQL Server does not track new columns on an existing capture instance, apply schema changes as follows:
  1. Alter the schema of the source table.
  2. Create a new CDC capture instance by running the sys.sp_cdc_enable_table stored procedure with a new @capture_instance name. SQL Server runs the old and new capture instances in parallel. At the start of the next CDC job run, Sync detects the new capture instance and continues reading from the original instance until all changes up to the new instance’s starting log sequence number (min_lsn) are processed. Sync then switches to the new capture instance automatically and updates its stored CDC metadata.
When Sync transitions to the new capture instance, it compares the old and new instances and handles any column differences as schema changes:
  • Columns added to the new capture instance are added to the destination schema.
  • Columns removed from the new capture instance are no longer replicated. Existing destination columns remain but are no longer updated.
  • Column type changes trigger a destination schema alteration.
After Sync completes the transition, you can safely remove the old capture instance by running sys.sp_cdc_disable_table. This feature is supported for standard CDC replication and CDC replication with History Mode enabled.
  • SQL Server supports a maximum of two concurrent capture instances per table. If SQL Server detects more than two instances, it generates an error.
  • Do not remove the original capture instance until Sync has completed the transition. If the original instance is dropped too early, the job fails because Sync still needs to read the remaining changes from it.

​
Change Tracking

When you use change tracking, Sync updates the destination table automatically when changes (like adding a column or changing a data type) are made to the source table structure.

​
Support for Incremental Replication

The SQL Server source supports incremental replication. Incremental replication reduces the workload tremendously and minimizes bandwidth use and latency of synchronization. Moving data in increments offers great flexibility when you are dealing with slow APIs or daily quotas. You must configure the incremental check column based on the SQL Server source columns. For details about how to set up incremental replication, see Incremental Replication.

​
Always Encrypted Support

CData Sync connects to SQL Server sources by using the CData JDBC driver. The JDBC driver supports Always Encrypted columns, which enables Sync to read data from columns that use client-side encryption. When Sync reads from Always Encrypted columns, the JDBC driver returns decrypted plaintext values instead of binary or hexadecimal representations. This behavior allows encrypted columns to be included in replication workflows without causing read failures.

​
More Information