Azure SQL Managed Instance Destination Setup Guide

Follow our setup guide to connect Azure SQL Managed Instance database as a destination to Fivetran.

Prerequisites

To connect Azure SQL Managed Instance to Fivetran, you need the following:

• 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
• A Fivetran role with the Create Destinations or Manage Destinations permissions

Setup instructions

Choose connection method

IMPORTANT: Do not perform this step if you want to use the Hybrid Deployment model for your data pipeline.

Decide whether to connect to your Azure SQL Managed Instance database directly, using an SSH tunnel, or using Azure Private Link. 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 Server Managed Instance 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 destination is contained within an inaccessible subnet.

To connect using SSH, do the following:

1. In the destination setup form, 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.

Connect using Azure Private Link

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

Azure Private Link allows Virtual Networks (VNets) and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft's Azure Private Link documentation.

Follow our Azure PrivateLink setup guide to configure Private Link for your destination.

Enable access

Configure a server firewall to grant Fivetran's data processing servers access to your database.

1. Log in to the Azure console.

2. On the Azure main page, select Azure SQL.

3. Click the Azure SQL managed instance that you want to connect to Fivetran.

   

4. On the instance overview page, find the host URL and make a note of it. You will need it to configure Fivetran.

   

5. In the left menu, go to the Security section. Click Networking.

6. Set the Public endpoint toggle to Enable.

   

7. Click Save.

8. In the left menu, click Overview.

9. Click the Virtual network/subnet link.

   

10. In the left menu, go to the Settings section. Click Subnets.

11. On the Subnets page, find the security group and make a note of it.

    

12. Return to the resource group that contains your managed instance. Click the network security group that you found in the last step.

    

13. In the left menu, go to the Settings section. Click Inbound security rules.

14. On the Inbound security rules page, click Add to add a new firewall rule with a memorable name (for example, Fivetran). What you enter in the IP fields depends on whether you're connecting directly or using an SSH tunnel.

    • If you are connecting directly, use the following settings:

      • Source : Fivetran's IPs for your managed instance's region
      • Source Port Range : *
      • Destination : Any
      • Service: Custom
      • Destination port ranges : 3342
      • Protocol : TCP
      • Action : Allow
      • Priority : Set a higher priority than the deny_all_inbound rule.

    • If you are connecting using an SSH tunnel, use the following settings:

      • Source : Your SSH tunnel server's IP address
      • Source Port Range : *
      • Destination : Any
      • Service: Custom
      • Destination port ranges : 3342
      • Protocol : TCP
      • Action : Allow
      • Priority : Set a higher priority than the deny_all_inbound rule.

15. Click Add.

Create Fivetran user

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 permissions

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

1. Log in to your Fivetran account.

2. Go to the Destinations page and click Add destination.

3. Enter a Destination name of your choice and then click Add.

4. Select Azure SQL Managed Instance as the destination type.

5. (Enterprise and Business Critical accounts only) Choose your deployment model:

   • SaaS Deployment
   • Hybrid Deployment

6. If you choose Hybrid Deployment, select an existing Hybrid Deployment Agent in the Select an existing agent drop-down menu or configure a new agent.

   NOTE: For more information about configuring a new agent, see our Hybrid Deployment setup guides.

7. In the destination setup form, enter the Host name you found in Step 2.

8. Enter the Port number. For example, 1433.

9. Enter the User name you created in Step 4.

10. Enter your Password.

11. Enter the Database name you want to replicate to.

12. (Not applicable to Hybrid Deployment) Choose your Connection method:

    • Connect directly
    • Connect via an SSH
    • Connect via Private Networking

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

13. (Not applicable to Hybrid Deployment) If you choose Connect via an SSH tunnel in the Connection method drop-down menu, enter the following details:

    • SSH Host
    • SSH Port
    • SSH User
    • (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.

14. (Not applicable to Hybrid Deployment) 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 Destinations documentation.

    IMPORTANT: If you are using Azure Private Link, select Azure in the Cloud service provider drop-down menu.

15. Choose your Time zone.

16. (Optional for Business Critical accounts and not applicable to Hybrid Deployment) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.

17. Click Save & Test.

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

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.

IMPORTANT: If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.

Setup tests

Fivetran performs the following Azure SQL Server Managed Instance destination 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 articles

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home