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

# SQL Server

> Power internal tools, in-app experiences, and more

## Connect to SQL Server

> [!NOTE]
>
> You may need to allowlist Hightouch's IP addresses to let our systems connect to your SQL Server instance. Reference our [networking docs](https://docs.hightouch.com/security/networking#ip-addresses) to determine which IP addresses you need to allowlist.
>
> You can also securely connect to your SQL Server instance using [AWS PrivateLink](https://docs.hightouch.com/security/aws/privatelink.md) or [GCP Private Service Connect](https://docs.hightouch.com/security/gcp/private-service-connect.md). AWS PrivateLink and GCP Private Service Connect are [Business Tier](https://hightouch.com/pricing) features.

### SSH tunneling

Hightouch can connect directly to SQL Server over the public internet or via an SSH tunnel. Since data is encrypted in transit via TLS, a direct connection is suitable for most use cases. You may need to set up a tunnel if your SQL Server instance is on a private network or virtual private cloud (VPC).

Hightouch supports both standard and reverse SSH tunnels. To learn more about SSH tunneling, refer to Hightouch's [tunneling documentation](https://docs.hightouch.com/security/ssh-tunneling.md).

> [!NOTE]
>
> The [connection string](#connection-string) method doesn't currently support SSH tunneling. Please [reach out to us](#pylon-chat)  if you're interested in this feature.

### Azure firewall setup

By default, Azure protects your SQL Server from any external IP address from connecting to your SQL Server. Follow these steps to add Hightouch's IP addresses to the firewall:

1. Log into your [Azure Console Dashboard](https://portal.azure.com/#home).
2. Click on **SQL databases** under **Services**.
3. Select the SQL database you want to update.
4. Select **Set server firewall** .

   ![Azure Console Dashboard with settings](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/sources/source-sqlserver-firewall.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=a15e17c4783711c354be464240a9bf08)
5. Under **Public network access**, select **Selected networks**.

   ![Azure Console Dashboard with settings](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/sources/source-sqlserver-firewall-setup-1.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=b21bf4858d1072bea78c5ac4558624f8)
6. Scroll to **Firewall rules** and click **Add a firewall rule**.

   ![Azure Console Dashboard with settings](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/sources/source-sqlserver-firewall-setup-2.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=f2a972f825c9aaaa0f62d2029c3084ad)
7. Allowlist the [Hightouch IP addresses](https://docs.hightouch.com/security/networking#ip-addresses) for your region.
8. Click the **Save** button on the bottom left.

#### Required permissions

Both setup options require:

* Server name (IP address or Azure hostname)
* Port number (1433 by default)
* Database name
* SQL Server Username
* SQL Server Password

Your credentials must be able to:

* Add/update/delete rows in your sync's table.
* View these tables:
  (\*These are used for gathering metadata to set up the sync.)
  * `INFORMATION_SCHEMA.COLUMNS`
  * `INFORMATION_SCHEMA.TABLE_CONSTRAINTS`
  * `INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE`
  * `SYS.COLUMNS`

To ensure your credentials are correct, click **Test connection**. This will confirm
if Hightouch is able to properly connect to your database by running a simple `SELECT` query.

#### Password form

![](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-password-form-setup.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=31fb6ec51aa4c0fb917e52c16b980a04)

#### Connection string

Connection string must be in this format:
`Server=server;Database=database;User Id=username;Password=password;Encrypt=boolean`

![](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-connection-string-setup.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=a8efb471aaeb474394c8edcd5415a6a6)

## Sync configuration

Hightouch supports **Upsert** mode with the option to delete removed rows.

Select the schema and table you want to sync to. A common schema to use is the database's default, `dbo`.

![](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-schema-table-selections.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=616a6dda3023205e29de257e45aaba4c)

### Record matching

Records can be matched from your source to your database table using any primary key or unique column. Hightouch only shows columns that are of the `PRIMARY KEY` or `UNIQUE` type in the dropdown.

![](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-externalIdMapping.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=75411d0602da11a8d34998a9bc993176)

**Note**: We do not display `IDENTITY` columns as those can't be edited or set
when inserting.

If there are no fields in the dropdown, you must add a primary key or unique constraint to your table in SQL Server. Then, click the refresh icon to access the newly created column.

### Field mapping

Select the fields from your source that you want to sync to SQL Server.

![](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-mappings.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=a3dcf26fa8202de1de6d5f9d35caf51e)

> [!WARNING]
>
> Make sure you account for column types and columns that are **non-nullable**.
> If you upsert any row with the wrong column type or set a null value for a
> non-nullable column, the entire batch with that row (up to 1k rows) will fail.

## Tips and troubleshooting

To see the exact API requests and responses from each sync run, use the [live debugger](https://docs.hightouch.com/syncs/debugger.md). To get notified by Slack, PagerDuty, SMS, or email when a sync fails, set up [sync alerts](https://docs.hightouch.com/syncs/alerting.md). If you run into an issue this page doesn't cover, [contact support](#pylon-chat).

## Common errors

### Cannot open server 'hightouch-test' request by login

If you see the following error message, you need to [update your firewall settings](#azure-firewall-setup) in your Azure Console.

```json theme={null}
Cannot open server 'hightouch-test' request by login.
Client with IP address '54.196.30.169' is not allowed to access the server.
To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule
on the master database to create a firewall rule for this IP address or address range.
It may up take up to five minutes for this change to take effect.
```

### "Invalid time" error

This error occurs when incorrectly formatted data is sent to a [SQL Server `time` field](https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-ver16).
To resolve this issue, you need to reformat your model column data. For example, if your model column contains values formatted like `hh:mm:ss`, you need to use the [template mapper](https://docs.hightouch.com/syncs/mapping-data.md#template-mapping) to change the data's format to:

```
1970-01-01T{{ model.row['column_name'] }}Z
```

Be sure to replace `column_name` with the name of your time column.

![Template mapping in the Hightouch UI](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/destinations/destination-sqlserver-template-mapping.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=c00f2cd3af3942f64983498a496cff36)

SQL Server accepts these types of values and converts them to the `hh:mm:ss.nnnnnnn` format SQL Server expects.