Azure Synapse Setup Guide

Setup warehouse using Azure portal

Create a warehouse using your Azure SQL portal.

How to create an Azure Synapse?

Get warehouse connection details

Find your fully qualified server name and database name for connecting to Azure Synapse.

How to find server name and database name using azure portal?

Enable access

In the setup wizard, decide if you'd like to connect your database directly or connect using an SSH tunnel.

• If you decide to connect directly to your database, you will need to create a firewall rule to allow access. This is the simplest and most secure method.
• If you decide to connect using an SSH tunnel, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet on a virtual network. To connect using an SSH tunnel, follow these instructions.

Configure server level firewall

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

How to add server level firewall?

Create a Fivetran user

Connect to your Azure Synapse using SQL Server Management Studio or SQL Pro as an admin user and execute the following SQL commands to create a user for Fivetran:

In master database:

CREATE LOGIN fivetran WITH PASSWORD = '<password>';

In 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;

Once user fivetran is created, grant it the following permissions to your warehouse:

GRANT CONTROL to fivetran;

Add a suitable resource class to the created user depending upon the memory requirement for columnstore index creation. For example, integrations like marketo, salesforce will need higher resource class because of the higher number of columns/ higher volume of data which requires more memory to create columnstore indexes.

Using static resource classes is recommended. You can start with resource class staticrc20 which allocates 200 MB for user irrespective of the performance level you use. If columnstore indexing fails with the current resource class, we have to increase the resource class.

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

For more information, check out the documents for memory and concurrency limits and resource classes

CONTROL permission is needed to create database scoped credentials which will be used while loading files from Blob Storage using PolyBase.

Enter the credentials

Enter the credentials to access Azure Synapse

1. Host (Your server name)
2. Port
3. Database
4. User (User name should be fivetran@<server_name> where <server_name> is part of your azure host url: <server_name>.database.windows.net)
5. Password