Follow our setup guide to connect Azure SQL Database as a destination to Fivetran.
Prerequisiteslink
To connect Azure SQL Database to Fivetran, you need the following:
-
SQL Server Version 2012 or above
-
IP (e.g.,
1.2.3.4) or host (your.server.com)NOTE: If you have enabled Private Link for the database instance, use the public IP instead of the hostname to connect directly.
-
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
NOTE: To grant permissions, open Azure Active Directory and uncheck the Support only Azure Active Directory authentication for this server option.
-
Fivetran role with the Create Destinations or Manage Destinations permissions
Setup instructionslink
Choose connection method link
Decide whether to connect to your Azure SQL Database 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 database instance and port.
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. 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 database is contained within an inaccessible subnet.
To connect using SSH, do the following:
-
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_keysfile 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.
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 Add Destination.
- 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. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and AWS region as described in our Destinations documentation.
- 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.
Setup testslink
Fivetran performs the following Azure SQL Database connection tests:
-
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 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 Database Certificate Validation Test generates a pop-up window where you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. We skip this test if you aren’t connecting directly.
-
The SQL Server Connection Test checks if we can access your database.
-
The Permission Test checks that we have the correct permissions to create schemas and tables in your database.
-
The SQL Server Type Test validates the SQL Server service type. The test checks if your SQL Server implementation matches the destination type. For example, this test will fail if you try to set up an Azure SQL Database destination using a generic SQL Server database.
NOTE: The tests may take a couple of minutes to finish running.
Related Contentlink
description Destination Overview
settings API Destination Configuration