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

You can use the SQL Server connector from the CData Sync application to move data
from any supported source to the SQL Server destination. To do so, you need to add
the connector, authenticate to the connector, and complete your connection.

## Prerequisites

This section describes the prerequisites that you must complete before adding a SQL Server destination connector in CData Sync. It outlines the supported SQL Server versions and the recommended approach for creating a dedicated user account to support secure and reliable data replication.

### Version Support

Sync supports connections to SQL Server 2008 (major version 10) and later.

### Creating a Sync User

You can connect to SQL Server by using any SQL user account that has sufficient permissions. However, as a best practice, CData recommends that you create a dedicated SQL Server user specifically for data replication from the Sync application. You can submit the following commands in SQL Server Management Studio to create a new SQL Server login and an associated database user named **sync\_user**:

```sql theme={null}
  -- Create the login sync_user with password '<Password>'.

CREATE LOGIN sync_user
WITH PASSWORD = '<Password>';
GO
  -- Create a database user for this new sync_user login.
CREATE USER sync_user
FOR LOGIN sync_user;
GO
```

By default, the new **sync\_user** account has no assigned permissions or roles. To allow Sync to write, modify, and delete data in a SQL Server destination connection, you must grant INSERT, UPDATE, and DELETE permissions to **sync\_user**. You can set these permissions at the database level by granting the **db\_datawriter** role to **sync\_user**, which allows INSERT, UPDATE, and DELETE operations on all tables in the database.

SQL Server destination connections must also have permission to read table schemas in the target database to properly map the incoming data to an existing table. To accomplish this, you also need to grant the **db\_datareader** role to **sync\_user**.

Submit the following commands to grant both roles to **sync\_user**:

```sql theme={null}
  -- Grant the db_datawriter role to sync_user.
ALTER ROLE db_datawriter ADD MEMBER sync_user;
  -- Grant the db_datareader role to sync_user,
ALTER ROLE db_datareader ADD MEMBER sync_user;
```

**Note:** Individual permissions such as INSERT, UPDATE, and DELETE can also be granted separately and restricted to the object level, as necessary.

Because the **db\_datawriter** role does not grant permission to create, alter, or drop database tables, you must also grant ALTER permissions to **sync\_user**. You can grant this permission at the schema level by using the following command:

```sql theme={null}
GRANT ALTER ON SCHEMA::[<SchemaName>] TO [sync_user];
```

For a general guide to creating SQL Server users, see [Create a Database User - SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver17).

## Add the SQL Server Connector

To enable Sync to use data from SQL Server, you first must add the
connector, as follows:

<ol>
  <li>Open the **Connections** page of the Sync dashboard.</li>
  <li>Click **Add Connection** to open the **Select Connectors** page.</li>
  <li>Click the **Destinations** tab and locate
  the **SQL Server** row.</li>

  <li>
    Click the **Configure Connection** icon at the end of that row to open
    the **New Connection** page. This action opens the
    **Add Connection** dialog.

    **Note:** If the **Configure Connection** icon is not
    available, click the **Download Connector** icon to install
    the SQL Server connector.
  </li>

  <li>Enter a name for your connection in the **Add Connection** dialog.</li>
  <li>Click **Add** to open the **Settings** tab for your connector.</li>
</ol>

For more information about installing new connectors, see
[Connections](https://docs.sync.cdata.com/26.4/en/connections/connections.md).

## Authenticate to SQL Server

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

* **Server:** Enter the host name or IP address of the server that hosts the SQL Server database. The default server is **localhost**.
* **Port:** Enter the port number for SQL Server. The default port value is **1433**.
* **Database:** Enter the default database to which you want to connect when you connect to SQL Server.
* **User:** Enter the username that you use to authenticate to SQL Server.
* **Password:** Enter the password that you use to authenticate to SQL Server.

## Complete Your Connection

To complete your connection:

1. For **Trust Server Certificate**, select whether you want to trust the server certificate. By default, the **Enable** checkbox is selected.
2. Define advanced connection settings on the **Advanced** tab. (In most cases, though, you should not need these settings.)
3. Click **Create & Test** to create your connection.

## Data-Type Mapping

CData Sync uses the following data-type conversions when it loads data into your SQL Server database:

| **CData Type** | **SQL Server Type** | **Notes** |
| :-: | :-: | - |
| bigint | bigint | |
| binary | varbinary(\<ColumnSize>) | If ColumnSize > 4000, ColumnSize = MAX |
| boolean | bit | |
| date | date | |
| decimal | decimal | |
| double | float | |
| float | float | |
| integer | int | |
| localdatetime | datetime2 | |
| smallint | smallint | |
| time | time | |
| timestamp | datetimeoffset | |
| varchar | nvarchar(\<ColumnSize>) | If ColumnSize > 4000, ColumnSize = MAX |

Most destinations that are available in Sync allow custom column mapping. See [Mapping Columns](https://docs.sync.cdata.com/26.4/en/transformations/etl.md#mapping-columns) for information about how to remove or adjust column mappings and apply different transformations to tasks in Sync.

## Advanced Job Options

The following table lists advanced job options that are most frequently used with SQL Server destination connections. For a complete list of available job options, see [Advanced Job Options](https://docs.sync.cdata.com/26.4/en/jobs/advanced-job-options.md).

| Option Name | Default Value | Description |
| :- | :-: | :- |
| **Truncate Table Mode** | **Delete** | Specifies the command that is used to remove existing rows when the **Truncate Table Data** replication option is enabled. The **Truncate** value resets identity column values to their seed values and cannot run against tables that are referenced by foreign keys. |
| **Command Timeout** | **300** | Controls the timeout, in seconds, for destination connections. |
| **Convert Date-Time Values to GMT** | **Not enabled** | Converts all local date and time values to GMT before inserting them into the destination database. |
| **PreserveCharDatatype** | **False** | Preserves fixed-length character data types when Sync creates destination columns. When enabled, CHAR(*n*) source columns create CHAR(*n*) destination columns (for lengths 1–8,000) and NCHAR(*n*) source columns create NCHAR(*n*) destination columns (for lengths 1–4,000). Source columns that exceed these limits fall back to NVARCHAR(MAX). Data-type overrides at the column level take precedence. |

## Always Encrypted Support

CData Sync connects to SQL Server destinations by using the CData JDBC driver. The JDBC driver supports `Always Encrypted` columns, which enables Sync to securely read and write data that is protected by client-side encryption.

When Sync writes to `Always Encrypted` columns, the JDBC driver performs the required client-side encryption before the data is sent to the destination. This behavior allows encrypted columns to be included in replication workflows without causing write failures.

## Common SQL Server Issues

When you connect to SQL Server destinations, you might occasionally encounter errors. Common errors and issues fall into the following categories:

* timeout errors
* additional errors and issues

### Timeout Errors

Timeout issues are more likely to occur when you replicate larger datasets and are typically straightforward to resolve. For SQL Server destinations, timeout issues are most often related to the **Command Timeout** value that is configured in Sync.

Applications that are external to SQL Server, including Sync, use a command timeout to determine how long the application waits for a response from SQL Server before cancelling a query. If the query does not complete within the specified time, SQL Server returns a timeout error.

In Sync, the default **Command Timeout** value is 300 seconds. Queries that consistently fail at or near the five-minute mark are commonly encountering a command timeout condition. In these cases, increasing the **Command Timeout** value can give SQL Server additional time to complete query processing and may help prevent timeout errors.

You can modify the **Command Timeout** value at the job level from your job's **Advanced** tab. (For details about these options, see [Advanced Job Options](https://docs.sync.cdata.com/26.4/en/jobs/advanced-job-options.md).) While increasing this value often resolves timeout-related issues, adjusting the timeout alone might not be sufficient in all scenarios. For example, reducing the amount of data that is processed by each query can also improve execution times and help avoid timeouts.

If you continue to experience timeout errors after adjusting the **Command Timeout** value, contact [CData Technical Support](https://www.cdata.com/support/submit.aspx).

For specific guidance about diagnosing and resolving query timeout errors for SQL Server, see Microsoft's documentation [Troubleshoot query time-out errors – SQL Server](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts).

### Additional Errors and Issues

For more help and troubleshooting resources for other issues and errors that you might encounter, explore the [CData Knowledge Base](https://www.cdata.com/kb/), join the [CData Community](https://community.cdata.com/community), or contact [CData Technical Support](https://www.cdata.com/support/submit.aspx) to open a support ticket.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.