SQL Server

Power internal tools, in-app experiences, and more

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:

Log into your Azure Console Dashboard.
Click on SQL databases under Services.
Select the SQL database you want to update.
Select Set server firewall .
Under Public network access, select Selected networks.
Scroll to Firewall rules and click Add a firewall rule.
Allowlist the Hightouch IP addresses for your region.
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.

Connection string

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

Syncing

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.

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.

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

Field mapping

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

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

Common errors

If you encounter an error or question not listed below and need assistance, don't hesitate to . We're here to help.

If you see the following error message, you need to update your firewall settings in your Azure Console.

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.

Live debugger

Hightouch provides complete visibility into the API calls made during each of your sync runs. We recommend reading our article on debugging tips and tricks to learn more.

Sync alerts

Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.

SQL Server

Setup