Skip to main content

Azure Synapse Setup Guide

Justin Stevenson
Updated

Azure Synapse Setup Guide

Follow our setup guide to connect your Azure Synapse data warehouse to Fivetran.

Prerequisites

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

  • Permissions to create a user for Fivetran
  • Fivetran role with the permissions
  • Permissions to add or change resource classes for user

IMPORTANT: Fivetran does not support Azure Synapse Serverless.

Setup instructions

Choose connection method

Decide whether to connect to your Azure Synapse data warehouse directly, using an SSH tunnel, or using Azure Private Link. For more information, see our .

Connect directly

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

Configure your firewall and/or other access control systems to allow incoming connections to your host and port from 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 data warehouse. You must then configure your tunnel server's security group to allow Fivetran access and configure the instance's security to allow access from the tunnel.

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

To connect using SSH, do the following:

  1. In the , select the Connect via an SSH tunnel option.

  2. Copy Fivetran's public SSH key.

  3. 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.

IMPORTANT: You must have a Business Critical plan to use Azure Private Link.

If you select Private Link as a connection method, Fivetran uses to move your data securely between our system and your destination.

To set up Private Link for your Azure Synapse destination, follow the instructions in the .

Create warehouse

using your Azure portal. Fivetran does not support Azure Synapse Serverless.

Find server details

  1. Go to the .

  2. Click Azure Synapse Analytics.

  3. Click on the SQL pool you created in .

  4. 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 address in your server-level firewall to allow incoming connections to your Azure Synapse data warehouse from Fivetran.

See for more information.

Create Fivetran user

  1. Connect to your Azure Synapse data warehouse using SQL Server Management Studio or SQL Pro as an Admin user.

  2. Execute the following SQL command to create a Fivetran user in the master database. In the SQL command, replace <username> with a username and <password> with a password of your choice.

    CREATE LOGIN <username> WITH PASSWORD = '<password>';
content_copy

  3. Switch to your data warehouse. Execute the following SQL commands to create a Fivetran user in the data warehouse:

    CREATE USER <username> FOR LOGIN <username>;
content_copy

  4. Execute the following command to grant the necessary permissions to the Fivetran user:

    GRANT CREATE TABLE, CREATE SCHEMA, SELECT, INSERT, ALTER, UPDATE, DELETE, ADMINISTER DATABASE BULK OPERATIONS TO <username>;
content_copy

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 greater number of columns. Higher volume of data requires more memory to create columnstore indexes. See Microsoft's documentation on and 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. In the command, replace <username> with the username of the Fivetran user you created in .

 EXEC sp_addrolemember '<resource_class_name>', '<username>';
content_copy

IMPORTANT: You must have a Business Critical plan to use Azure Private Link.

Fivetran uses Private Link to move your data securely between our system and your Azure Synapse destination.

Prerequisites

To set up Azure Private Link, you need:

  • A Fivetran instance configured to run in Azure
  • An Azure Synapse destination in one of

Configure Private Link for Azure Synapse destination

  1. Contact your Fivetran account manager and provide the fully-qualified Resource ID (including the Resource name and Resource type) of your Azure Synapse destination.

    NOTE: To create private endpoints to an Azure Synapse workspace, use Microsoft.Synapse/workspaces as the Resource type. Select your Azure Synapse workspace as the Resource. You can use Sql as the Target sub-resource for your Synapse workspace. For more information, see .

  2. Wait to receive Private Endpoint request details from Fivetran. We create a Private Endpoint using your Resource ID, type, and sub-resource. We then initiate a Private Link connection request as part of the Private Endpoint setup and share the details of that request with you.

  3. In the Azure Portal or CLI, verify and approve the Private Link connection request from Fivetran. Fivetran then finishes setting up Private Link for your Azure Synapse destination on our side.

Complete Fivetran configuration

  1. Log in to your Fivetran account.

  2. Go to the , then click + Add Destination.

  3. On the Add destination to your account page, enter a Destination name of your choice.

  4. Click Add.

  5. Select Azure Synapse as the destination type.

  6. In the destination setup form's Host field, enter one of the following values:

    • (Direct or SSH connections) The host name you found in
    • (Private Link connections) The private endpoint URL

  7. Enter the Port number.

  8. Enter the User name of the Fivetran user you created in . The username must be in the <username>@<server_name> format, where <server_name> is part of your Azure host URL: <server_name>.database.windows.net.

  9. Enter your Password.

  10. Enter the Database name you found in .

  11. Choose your Connection method:

    • Connect directly
    • Connect via an SSH tunnel
    • Connect via Private Link

      NOTE: The Connect via Private Link option is only available for Business Critical accounts.

  12. (Optional) If you choose Connect via an SSH tunnel, enter the following details:

    • SSH Host
    • SSH Port
    • SSH User

  13. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our .

  14. Choose your Time zone.

  15. (Optional for Business Critical accounts) To enable , set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make note of the IP addresses of the secondary region and safelist these addresses in your firewall.

  16. Click Save and Test.

Fivetran 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

Fivetran performs the following Azure Synapse connection 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 data 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.

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk