Azure Synapse Setup Guide
Setup warehouse using Azure portal
Create a warehouse using your Azure SQL portal.
Get warehouse connection details
Find your fully qualified server name and database name for connecting to Azure Synapse.
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.
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>';
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';
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
- Host (Your server name)
- User (User name should be
<server_name>is part of your azure host url: