Azure SQL Database Destination Setup Guidelink

Follow our setup guide to connect Azure SQL Database as a destination to Fivetran.

Prerequisiteslink

To connect your Azure SQL Database destination to Fivetran, you need:

SQL Server Version 2012 or above
IP (e.g., 1.2.3.4) or host (your.server.com)
Port (usually 1433)
Access to your SQL Server through Fivetran's IPs for your database's region
A Fivetran-specific SQL Server user with WRITE-level permissions
Fivetran account owner permission to add destinations

Setup instructionslink

Choose connection method link

Decide whether to connect to your Azure SQL Database destination 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 database's 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 SQL Database destination. 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 destination 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.

Configure server firewall link

Log in to the Azure console.
In the left menu, select SQL databases.
Select the SQL database that you want to replicate to.
In the Overview section, go to the Set server firewall tab.
Add a new firewall rule to safelist Fivetran's IPs.
Click Save.

Find server details link

In the Overview section, make a note of the Server name. You will need it to configure Fivetran.

azure-database-overview

Create Fivetran userlink

Execute the following commands to add a contained database user. Choose a memorable username (for example, fivetran). Replace <database> with the name of your database and <password> with a password of your choice:

  USE [<database>];
  CREATE USER fivetran WITH PASSWORD = '<password>';

Grant permissionslink

Execute the following commands to grant the fivetran user CREATE permission for the database:

GRANT CREATE SCHEMA ON DATABASE::[<database>] TO fivetran;
GRANT CREATE TABLE ON DATABASE::[<database>] TO fivetran;

Complete Fivetran configuration link

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 SQL Database as the destination type.
In the destination setup form, enter the Host name you found in Step 3.
Enter the Port number. For example, 1433.
Enter the User name you created in Step 4. Your username must follow 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 want to replicate to.
Choose your Connection method. You can choose to Connect directly or Connect via an SSH. 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 & Test.

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

Documentation