Azure Synapse Setup Guide

Follow our setup guide to connect Azure Synapse to Fivetran.

Prerequisites

To connect a Azure Synapse data warehouse to Fivetran, you need the following:

Permissions to create a user for Fivetran
Fivetran account owner permission to add destinations
Permissions to add or change resource classes for user

Setup instructions

Choose connection method

Decide whether to connect to your Azure Synapse warehouse directly or using an SSH tunnel. For more information, see our destination connection options documentation.

Connect directly

If you connect directly, you must create a rule in a security group that allows Fivetran access to your destination port and database instance.

Configure your firewall and/or other access control systems to allow incoming connections to your host and port from Fivetran's IPs for your region.

Connect using an SSH tunnel

If you connect using an SSH tunnel, Fivetran connects to a separate server in your network that provides an SSH tunnel to your Azure Synapse warehouse. You must then configure your tunnel server's security group to allow Fivetran access and configure the warehouse instance's security to allow access from the tunnel.

You must connect through SSH if your warehouse is contained within an inaccessible subnet.

In the destination setup form, select the Connect via an SSH tunnel option.
Copy Fivetran's public SSH key.
Add the public key to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

Create warehouse

Create a warehouse using your Azure portal.

Find server details

Go to the Azure portal.
Click Azure Synapse Analytics.
Click on the SQL pool you created in Step 2.
In the Server name field, find the fully qualified server name and database name. Make a note of the names. You will need them to complete the destination setup in Fivetran.

Configure server-level firewall

Add Fivetran's IP address in your server-level firewall to allow incoming connections to your Azure Synapse warehouse from Fivetran.

See Azure's documentation on how to create a server-level firewall rule for more information.

Create a Fivetran user

Connect to your Azure Synapse using SQL Server Management Studio or SQL Pro as an Admin user.
Execute the following SQL command to create a Fivetran user in the master database:
```
CREATE LOGIN fivetran WITH PASSWORD = '<password>';
```

Execute the following SQL command to create a Fivetran user in the warehouse:

CREATE USER fivetran_user_without_login without login;
CREATE USER fivetran FOR LOGIN fivetran;
GRANT IMPERSONATE on USER::fivetran_user_without_login to fivetran;

Grant CONTROL permissions to the Fivetran user. Execute the following command:
```
GRANT CONTROL to fivetran;
```
NOTE: We need CONTROL permission to create database scoped credentials that we use while loading files from Blob Storage using PolyBase.

Add resource class

Add a suitable resource class to the Fivetran user depending upon the memory requirement for columnstore index creation. We recommend using static resource classes. You can start with the staticrc20 resource class that allocates 200 MB for the user irrespective of the performance level.

NOTE: Some connectors need higher resource class because of the higher number of columns. Higher volume of data requires more memory to create columnstore indexes. See Microsoft's documentation on memory and concurrency limits and resource classes for more information.

You must increase the allocated resource class, if the columnstore indexing fails with the current resource class. Execute the following command to increase the resource class:

 EXEC sp_addrolemember '<resource_class_name>', 'fivetran';

Complete Fivetran configuration

Log in to your Fivetran account.
Go to the Manage Account page.
In the Destinations tab, click +Destination.
On the Add Destination To Fivetran page, enter a destination name of your choice.
Click Continue.
Select Azure Synapse as the destination type.
In the destination setup form, enter the Host name you found in Step 3.
Enter the Port number.
Enter your User name, using the 'fivetran@<server_name>' format where <server_name> is part of your Azure host URL: <server_name>.database.windows.net.
Enter your Password.
Enter the Database name you found in Step 3.
Choose your Connection method: Connect directly or Connect via an SSH tunnel. If you choose Connect via an SSH tunnel, enter the following details:
- SSH Host
- SSH Port
- SSH User
- (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
Choose the Data processing location.
Choose your Timezone.
Click Save and Test.

Fivetran tests and validates the Azure Synapse connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the Azure Synapse destination.

Fivetran tests and validates the Azure Synapse connection. Upon successful completion of the setup tests, you can sync your data using Fivetran connectors to the Azure Synapse destination.

Setup tests

The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.
The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.
The UserName Has ServerName Test verifies the user details you provided in the setup form. The test checks if you have appended the server name to the user name. For example, fivetran@server_name.
The Azure Warehouse Connection Test checks the connectivity to the warehouse.
The Azure Warehouse Type Test checks if you are using Microsoft Azure SQL Data Warehouse.
The Azure Warehouse Permission Test checks if we have permissions to create schemas and tables on the database.

NOTE: The tests may take a couple of minutes to finish running.