> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sync.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server

export const CommonDatasourceMoreInformation = ({datasource = "the data source", advancedurl = "", siteName = "CData Sync", driverVersion = ""}) => {
  return <>
      <p>
        For more information about interactions between {siteName} and {datasource}, see{' '}
        <a href={`https://cdn.cdata.com/help/${advancedurl}${driverVersion}/synch/default.htm`}>
          {datasource} Connector for {siteName}
        </a>.
      </p>
    </>;
};

export const CommonAuthSchemeAzureserviceprincipalcert = () => {
  return <>
      <p>To connect with an Azure service principal and client certificate, set the following properties:</p>
      <ul>
        <li><strong>Auth Scheme:</strong> Select <strong>AzureServicePrincipalCert</strong>.</li>
        <li><strong>Azure Tenant:</strong> Enter the Microsoft Online tenant to which you want to connect.</li>
        <li><strong>OAuth Client Id:</strong> Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.</li>
        <li><strong>OAuth JWT Cert:</strong> Enter your Java web tokens (JWT) certificate store.</li>
        <li><strong>OAuth JWT Cert Type:</strong> Enter the type of key store that contains your JWT Certificate. The default type is <strong>PEMKEY_BLOB</strong>.</li>
        <li>(Optional) <strong>OAuth JWT Cert Password:</strong> Enter the password for your OAuth JWT certificate.</li>
        <li>(Optional) <strong>OAuth JWT Cert Subject:</strong> Enter the subject of your OAuth JWT certificate.</li>
      </ul>
      <p>To obtain the OAuth certificate for your application:</p>
      <ol>
        <li>Log in to the <a href="https://portal.azure.com" target="_blank" rel="noopener noreferrer">Azure portal</a>.</li>
        <li>In the left navigation pane, select <strong>All services</strong>. Then, search for and select <strong>App registrations</strong>.</li>
        <li>Click <strong>New registrations</strong>.</li>
        <li>Enter an application name and select <strong>Any Azure AD Directory - Multi Tenant</strong>.</li>
        <li>After you create the application, copy the application (client) Id value that is displayed in the <strong>Overview</strong> section. Use this value as the OAuth client Id.</li>
        <li>Navigate to the <strong>Certificates & Secrets</strong> section and select <strong>Upload certificate</strong>. Then, select the certificate to upload from your local machine.</li>
        <li>Specify the duration and save the client secret. After you save it, the key value is displayed.</li>
        <li>Copy this value because it is displayed only once. You will use this value as the OAuth client secret.</li>
        <li>On the <strong>Authentication</strong> tab, make sure to select <strong>Access tokens (used for implicit flows)</strong>.</li>
      </ol>
    </>;
};

export const CommonAuthSchemeAzureserviceprincipal = () => {
  return <>
      <p>To connect with an Azure service principal and client secret, set the following properties:</p>
      <ul>
        <li><strong>Auth Scheme:</strong> Select <strong>AzureServicePrincipal</strong>.</li>
        <li><strong>Azure Tenant:</strong> Enter the Microsoft Online tenant to which you want to connect.</li>
        <li><strong>OAuth Client Id:</strong> Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.</li>
        <li><strong>OAuth Client Secret:</strong> Enter the client secret that you were assigned when you registered your application with an OAuth authorization server.</li>
      </ul>
      <p>To obtain the OAuth client Id and client secret for your application:</p>
      <ol>
        <li>Log in to the <a href="https://portal.azure.com" target="_blank" rel="noopener noreferrer">Azure portal</a>.</li>
        <li>In the left navigation pane, select <strong>All services</strong>. Then, search for and select <strong>App registrations</strong>.</li>
        <li>Click <strong>New registrations</strong>.</li>
        <li>Enter an application name and select <strong>Any Azure AD Directory - Multi Tenant</strong>.</li>
        <li>After you create the application, copy the application (client) Id value that is displayed in the <strong>Overview</strong> section. Use this value as the OAuth client Id.</li>
        <li>Navigate to the <strong>Certificates & Secrets</strong> section and select <strong>New Client Secret</strong> for the application.</li>
        <li>Specify the duration and save the client secret. After you save it, the key value is displayed.</li>
        <li>Copy this value because it is displayed only once. You will use this value as the OAuth client secret.</li>
        <li>On the <strong>Authentication</strong> tab, make sure to select <strong>Access tokens (used for implicit flows)</strong>.</li>
      </ol>
    </>;
};

export const CommonAuthSchemeAzuremsi = ({siteName = "CData Sync"}) => {
  return <>
      <p>
        To leverage Azure Managed Service Identity (MSI) when {siteName} is running on an Azure
        virtual machine, select <strong>Azure MSI</strong> for <strong>Auth Scheme</strong>. No
        additional properties are required.
      </p>
    </>;
};

export const CommonAuthSchemeAzuread = ({siteName = "CData Sync"}) => {
  return <>
      <p>
        To connect with an Azure Active Directory (AD) user account, select <strong>AzureAD</strong>{' '}
        for <strong>Auth Scheme</strong>. {siteName} provides an embedded OAuth application with
        which to connect, so no additional properties are required.
      </p>
    </>;
};

export const CommonDatasourceAddConnector = ({datasource = "the data source", title = "the connector", destination = false, siteNameShort = "Sync"}) => {
  return <>
      <p>
        To enable {siteNameShort} to use data from {datasource}, you first must add the
        connector, as follows:
      </p>
      <ol>
        <li>Open the <strong>Connections</strong> page of the {siteNameShort} dashboard.</li>
        <li>Click <strong>Add Connection</strong> to open the <strong>Select Connectors</strong> page.</li>
        <li>
          Click the <strong>{destination ? "Destinations" : "Sources"}</strong> tab and locate
          the <strong>{title}</strong> row.
        </li>
        <li>
          Click the <strong>Configure Connection</strong> icon at the end of that row to open
          the <strong>New Connection</strong> page. This action opens the{' '}
          <strong>Add Connection</strong> dialog box.
          <br />
          <strong>Note:</strong> If the <strong>Configure Connection</strong> icon is not
          available, click the <strong>Download Connector</strong> icon to install
          the {title} connector.
        </li>
        <li>Enter a name for your connection in the <strong>Add Connection</strong> dialog box.</li>
        <li>Click <strong>Add</strong> to open the <strong>Settings</strong> tab for your connector.</li>
      </ol>
      <p>
        For more information about installing new connectors, see{' '}
        <a href="../connections">Connections</a>.
      </p>
    </>;
};

export const CommonDatasourceIntroSource = ({datasource = "the data source", siteName = "CData Sync"}) => {
  return <p>
      You can use the {datasource} connector from the {siteName} application to capture data
      from {datasource} and move it to any supported destination. To do so, you need to add
      the connector, authenticate to the connector, and complete your connection.
    </p>;
};

export const driverVersion = "M";

export const siteNameShort = "Sync";

export const siteName = "CData Sync";

export const datasource = "SQL Server";
export const pageTitle = "SQL Server";
export const advancedurl = "RU";
export const oauthschemes = "AzurePassword, AzureAD, AzureMSI, AzureServicePrincipal, or AzureServicePrincipalCert";

<CommonDatasourceIntroSource datasource={datasource} siteName={siteName} />

## Add the SQL Server Connector

<CommonDatasourceAddConnector datasource={datasource} title={pageTitle} destination={false} siteNameShort={siteNameShort} />

## Authenticate to SQL Server

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

{siteName} supports authenticating to {pageTitle} in several ways. Select your authentication method below to proceed to the relevant section that contains the authentication details.

* [**Password**](#password) (default)
* [**NTLM**](#ntlm)
* [**Kerberos**](#kerberos)
* [**AzurePassword**](#azurepassword)
* [**AzureAD**](#azure-active-directory)
* [**AzureMSI**](#azure-managed-service-identity)
* [**AzureServicePrincipal**](#azure-service-principal)
* [**AzureServicePrincipalCert**](#azure-service-principal-certificate)

### 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

<CommonAuthSchemeAzuread siteName={siteName} siteNameShort={siteNameShort} datasource={datasource} />

### Azure Managed Service Identity

<CommonAuthSchemeAzuremsi siteName={siteName} siteNameShort={siteNameShort} datasource={datasource} />

### Azure Service Principal

<CommonAuthSchemeAzureserviceprincipal siteName={siteName} siteNameShort={siteNameShort} datasource={datasource} />

### Azure Service Principal Certificate

<CommonAuthSchemeAzureserviceprincipalcert siteName={siteName} siteNameShort={siteNameShort} datasource={datasource} />

## 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.
2. Define advanced connection settings on the **Advanced** tab. (In most cases, though, you should not need these settings.)
3. {oauthschemes ? `If you authenticate with ${oauthschemes}, click` : "Click"} **Connect to {datasource}** to connect to your {pageTitle} account.
4. Click **Create & Test** to create your connection.

## Configure Change Data Capture and Change Tracking

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 {siteNameShort}. 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.

<Note>If both of these methods are enabled on a table, {siteNameShort} uses CDC.</Note>

To use CDC for the SQL Server source in {siteNameShort}, ensure that you have the following prerequisites in place:

* CDC must be enabled on the SQL Server database (see [step 1](#step1) in the section [Enable Change Data Capture for {siteName}](#enable-change-data-capture-for-cdata-sync).
* The SQL Server agent must be running.
* You must be a member of the `db_owner` fixed database role for the database.

### Enable Change Data Capture for {siteName}

1. Enable CDC on your database by submitting the following statements:

   ```
   USE [DatabaseName];
   EXEC sys.sp_cdc_enable_db;
   GO 
   ```

   To enable CDC on an individual table, submit the following statements:

   ```
   USE [DatabaseName];
   EXEC sys.sp_cdc_enable_table 
   @source_schema = [SchemaName],
   @source_name   = [TableName],
   @role_name     = NULL
   GO 
   ```
2. Create a job in {siteNameShort} with your SQL Server source and select the **Change Data Capture** option.

### Enable Change Tracking for {siteName}

1. <a name="step1" />Enable change tracking on your database by submitting the following statement:

   ```
   ALTER DATABASE [DatabaseName] SET CHANGE_TRACKING=ON (CHANGE_RETENTION=7 DAYS, AUTO_CLEANUP=ON);
   ```

   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 {siteNameShort} time to resolve conflicts and errors. If the last successful job run is outside the retention period, {siteNameShort} replicates the full table automatically to ensure that no changes are missed.

   To enable change tracking on an individual table, submit the following statement:

   ```
   ALTER TABLE [SchemaName].[TableName] ENABLE CHANGE_TRACKING;
   ```

   <Note>To use change tracking, each table must have at least one primary key.</Note>

2. Create a job in {siteNameShort} with your SQL Server source and select the **Change Data Capture** option.

### Alter Schema

When you use change tracking, {siteNameShort} updates the destination table automatically when changes (like adding a column or changing a data type) are made to the source table structure. When you use CDC, SQL Server does not track new columns automatically. So, you must create a new CDC instance and drop the old instance. This action triggers a full refresh of the table in {siteNameShort}.

## 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](../../getting-started/features#incremental-replication).

## Always Encrypted Support

{siteName} connects to {datasource} sources by using the CData JDBC driver. The JDBC driver supports `Always Encrypted` columns, which enables {siteNameShort} to read data from columns that use client-side encryption.

When {siteNameShort} 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

<CommonDatasourceMoreInformation datasource={datasource} advancedurl={advancedurl} siteName={siteName} driverVersion={driverVersion} />