Azure SQL Managed Instance Setup Guide

Follow these instructions to replicate your Azure SQL managed instance to your destination using Fivetran.

Prerequisites

To connect your Azure SQL managed instance to Fivetran, you need:

An Azure account with a DB Owner, SQL Server Contributor, or SQL Security Manager role
ALTER ANY USER permission in your database (required for username and password authentication only)
Credentials for your chosen authentication method
Your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com)
Your database's port (usually 3342)
Additional requirements depending on the connection method you choose
(Optional, Private Preview only) To manage credentials outside of Fivetran, you need an External Secret Manager configured for your account before starting setup.

We do not support single-user mode. We only support the proxy connection type. See Microsoft's Azure SQL documentation for more information on the proxy connection type.

Setup instructions

Choose authentication method

Decide on your preferred authentication method and complete the required configuration steps in the Azure Portal and Azure SQL Managed Instance.

Authenticate with Microsoft Entra ID OAuth

Microsoft Entra ID lets Fivetran authenticate using OAuth 2.0 with a secure and centralized identity provider. This method lets you:

Avoid sharing usernames or passwords with Fivetran
Maintain control over access permissions through Azure roles or groups
Revoke access at any time through the Microsoft Entra ID portal

Before you begin, make sure you have:

A properly configured Microsoft Entra ID tenant within your Azure account.
Entra ID authentication configured on your Azure SQL Managed Instance. For more information, see Configure Microsoft Entra authentication for Azure SQL.
A Microsoft Entra admin set on your managed instance. This role is required to create Entra ID users in the database and enable Fivetran to authenticate using Entra ID.

To set up this method:

Open the Azure Portal and sign in.
Go to Azure Active Directory.
In the left-hand menu, click Properties.
Make a note of the Tenant ID. You need it to complete the Fivetran connection setup form.
In your Azure SQL Managed Instance, run the following command. Replace <PRINCIPAL-NAME> with the User Principal Name (UPN) of the Entra ID user (for example, user@yourdomain.com) or, if the user is a member of an Entra security group authorized to access the database, the Group Principal Name.
```
CREATE USER [<PRINCIPAL-NAME>] FROM EXTERNAL PROVIDER;
```
Even though Fivetran authenticates using OAuth, your database still requires a mapped user to issue queries. The user can be an individual account or a group that has been assigned access.
In the Grant user permissions step, you must grant the database permissions to this Entra ID user or group.

Authenticate with Microsoft Entra ID Managed Identity (only available on Hybrid Deployment)

Microsoft Entra ID Managed Identity lets Fivetran authenticate securely without using or storing usernames or passwords.

This method is supported only in Hybrid Deployment. Both the Hybrid Deployment Agent and your Azure SQL Managed Instance must reside under the same Microsoft Entra tenant.

Before you begin, make sure you have:

Microsoft Entra ID (formerly Azure Active Directory) authentication enabled on your Azure SQL Managed Instance. For more information, see Configure Microsoft Entra authentication for Azure SQL.
A user who has the Microsoft Entra admin role on your managed instance. You must be signed in as this user.
A Managed Identity (either User Assigned or System Assigned) configured on the Azure resource (such as a VM) where the Fivetran Hybrid Deployment Agent is installed. Follow the Managed identities for Azure resources documentation to enable and assign a Managed Identity to that resource.

To set up this method:

(User Assigned Managed Identity only) Retrieve the Managed Identity Client ID:
1. Open the Azure Portal and sign in.
2. In the top search bar, search for Managed Identities.
3. Select the User Assigned Managed Identity you want to use.
4. In the Overview tab, make a note of the Client ID. You need it to complete the Fivetran connection setup form.
To allow your Managed Identity (UAMI or SAMI) to access the Azure SQL Managed Instance, create a user in the database using the following command. Replace <PRINCIPAL-NAME> with the name of your Managed Identity.
```
CREATE USER [<PRINCIPAL-NAME>] FROM EXTERNAL PROVIDER;
```
In the Grant user permissions step, you must grant the appropriate database permissions to this Managed Identity user.

Authenticate with Microsoft Entra ID Service Principal with Client Secret

Microsoft Entra ID Service Principal with Client Secret authentication lets Fivetran connect to your Azure SQL Managed Instance using a Service Principal identity registered in your Microsoft Entra ID tenant.

This method requires a Service Principal registered in Microsoft Entra ID, along with its Client ID (Application ID) and Client Secret. The following steps show you how to create them.

To set up this method:

Open the Azure Portal and sign in.
Navigate to Microsoft Entra ID > App registrations.
Click + New registration.
Enter a name for the app (for example, Fivetran-SQL-Connector).
Click Register.
Go to the app's Overview tab.
Make a note of the Application (client) ID. This is your Service Principal Client ID. You need it to complete the Fivetran connection setup form.
In the left-hand menu of your app, go to Certificates & secrets.
Under Client secrets, click + New client secret.
Enter a description and choose an expiration period.
Click Add.
Make a note of the Value of the secret immediately. It's shown only once. This is your Service Principal Client Secret. You also need it to complete the Fivetran connection setup form.
Keep this secret secure and monitor its expiration to avoid disruptions.
To allow your Service Principal to connect to the Azure SQL Managed Instance, run the following command. Replace <PRINCIPAL-NAME> with the name of your Service Principal.
```
CREATE USER [<PRINCIPAL-NAME>] FROM EXTERNAL PROVIDER;
```
In the Grant user permissions step, you must grant the appropriate database permissions to this Service Principal user.

Authenticate with Microsoft Entra ID Service Principal with Client Certificate

Microsoft Entra ID Service Principal with Client Certificate authentication lets Fivetran securely connect to your Azure SQL Managed Instance using a Service Principal that is authenticated using a client certificate.

This method requires a Service Principal registered in Microsoft Entra ID, its Client ID (Application ID), and a valid client certificate with its private key (if stored separately from the certificate). The following steps show you how to set them up.

To set up this method:

Open the Azure Portal and sign in.
Navigate to Microsoft Entra ID > App registrations.
Click + New registration.
Enter a name for the app (for example, Fivetran-SQL-Cert-Auth).
Click Register.
Go to the app's Overview tab.
Make a note of the Application (client) ID. This is your Service Principal Client ID. You need it to complete the Fivetran connection setup form.
Generate a certificate. Your Service Principal must be configured with a valid public certificate to authenticate. You can use any method that fits your organization's tooling or security policies. Two commonly used approaches are:
Generate a certificate using OpenSSL
```
openssl req -x509 -newkey rsa:2048 -keyout private-key.key -out certificate.crt -days 365 -nodes
```
Generate a certificate using Azure Key Vault
1. In the Azure Portal, go to Key Vault > Certificates.
2. Generate or import a certificate in .pfx format.
3. Export the certificate and private key if needed:
```
openssl pkcs12 -in certificate.pfx -clcerts -nokeys -out certificate.crt
openssl pkcs12 -in certificate.pfx -nocerts -out private-key.key
```
  Azure Key Vault doesn't allow downloading the private key for certificates generated inside Key Vault. To retrieve the private key, you must have imported a .pfx file during creation.
Regardless of the method you choose, the certificate you upload to the App registration must be in one of the following supported formats: .cer, .crt, or .pem.
Navigate to your App registration in the Azure Portal.
Go to Certificates & secrets > Certificates.
Click Upload certificate.
Select a valid .cer, .crt, or .pem file.
To allow your Service Principal to connect to the Azure SQL Managed Instance, run the following command. Replace <PRINCIPAL-NAME> with the name of your Service Principal.
```
CREATE USER [<PRINCIPAL-NAME>] FROM EXTERNAL PROVIDER;
```
In the Grant user permissions step, you must grant the appropriate database permissions to this Service Principal user.

Authenticate with username and password

This method lets you authenticate Fivetran using a username and password for a properly configured user in your Azure SQL Managed Instance. You'll create this user later in the setup process.

Choose connection method

Decide on your preferred method for connecting Fivetran to your Azure SQL managed instance, and then configure the necessary settings for that method. This connector supports the following connection methods:

Connect directly (TLS required)

You must have TLS enabled on your database to connect directly to Fivetran. Follow Azure's TLS setup instructions to enable TLS on your database.

We require TLS 1.2 or higher.

Fivetran connects directly to your database instance. This is the simplest connection method.

To connect directly, create a firewall rule to allow access to Fivetran's IPs for your database's region.

Connect using SSH (TLS optional)

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet on a virtual network.

You must create a firewall rule to allow access to your SSH tunnel server's IP address.

To connect using SSH, follow our SSH connection instructions. If you want Fivetran to tunnel SSH over TLS, follow Azure's TLS setup instructions to enable TLS on your database.

If you enable TLS, we require TLS 1.2 or higher.

Connect using Azure Private Link

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

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

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

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Enable access

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

Log in to the Azure console.
On the Azure main page, select Azure SQL.
Click the Azure SQL managed instance that you want to connect to Fivetran.
On the instance overview page, find the host URL and make a note of it. You will need it to configure Fivetran.
In the left menu, under Security, click Networking.
Set the Public endpoint toggle to Enable.
Click Save.
In the left menu, click Overview.
Click the Virtual network/subnet link.
In the left menu, under Settings, click Subnets.
On the Subnets page, find the security group and make a note of it.
Return to the resource group that contains your managed instance.
Click the network security group that you noted in the previous step.
In the left menu, under Settings, click Inbound security rules.
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 your connection method.
- 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.
- If you are connecting using Azure Private Link or Proxy Agent, you don't need to configure inbound security rules. Skip ahead to Create user.
Click Add.

Create user

If you're authenticating Fivetran with a username and password, create a database user for Fivetran's exclusive use.

If you are authenticating with Microsoft Entra ID (formerly Azure Active Directory), skip ahead to Grant user permissions.

Open a connection to your Azure SQL Managed Instance.
Add a container database user by running the following SQL statements. Replace <database> with the name of your database, <username> with the username of your choice, and <password> with a password of your choice:
```
USE [<database>];
CREATE USER <username> WITH PASSWORD = '<password>';
```

Grant user permissions

Grant the Fivetran user SELECT permission for the data you want to sync. Choose the most appropriate scope:
- Database-level access: Grants access to all objects in a database.
```
GRANT SELECT ON DATABASE::[<database>] TO [<username>];
```
- Schema-level access: Grants access to all current and future tables in a schema.
```
GRANT SELECT ON SCHEMA::[<schema>] TO [<username>];
```
- Table-level access: Grants access only to specific tables.
```
GRANT SELECT ON [<schema>].[<table>] TO [<username>];
```
  If you grant table-level access, newly created tables aren't accessible automatically. Grant SELECT on each new table you want Fivetran to sync.
- Column-level access: Grants access only to specific columns in a table.
```
GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO [<username>];
```
  If you grant column-level access, newly added columns aren't accessible automatically. Rerun the command with the additional columns.
- Exclude specific columns: Grants access to all columns in a table except the ones you specify.
```
GRANT SELECT ON [<schema>].[<table>] TO [<username>];
DENY SELECT ON [<schema>].[<table>] ([<column X>], [<column Y>], ...) TO [<username>];
```
Grant VIEW DEFINITION permission to the same principal that has SELECT access so we can read column default definitions during schema changes. We need this access to detect and backfill new columns with deterministic default values. If the Fivetran user already has equivalent access through other permissions, you don't need an additional VIEW DEFINITION grant.
```
GRANT VIEW DEFINITION ON DATABASE::[<database>] TO [<username>];
```
You can also grant VIEW DEFINITION on a specific schema or table if you want to scope it more narrowly.
(Optional) If you are using Change Tracking with history mode enabled, to improve performance and reduce sync delays, you may want to grant the Fivetran user the necessary permissions by running the following SQL statement:
```
USE [master]; GRANT VIEW SERVER STATE TO [<username>];
```
This grants the Fivetran user access to server state information, minimizing the dependency on checkpoint timing and reducing update delays.

Enable incremental updates

For incremental updates, choose an incremental sync method. These incremental sync methods let Fivetran copy only the rows that have changed since the last data sync, so we don't have to copy the whole table every time. Learn more in our updating data documentation.

Read changes using CT or CDC mechanisms

Use this incremental sync method if you want Fivetran to read new, modified, or deleted data using SQL Server-native change capture mechanisms: change tracking (CT), change data capture (CDC), or both. You don't need to enable both CT and CDC for every table.

Fivetran chooses the mechanism to use for each table based on your SQL Server configuration:

If a table has only CT enabled, we use CT.
If a table has only CDC enabled, we use CDC.
If a table has both CT and CDC enabled, we use CDC.
If a table has neither CT nor CDC enabled, the table appears in Fivetran but is disabled until you enable CT or CDC.

Use the following instructions to enable CT, CDC, or both based on the tables you want to sync.

Enable change tracking

Enable change tracking at the database level:
```
ALTER DATABASE [<database>] 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
```
While we recommend seven days of change retention, you can set your retention period as low as one day. However, a shorter retention period increases the risk that your logs will expire in between syncs, triggering an automatic full source re-sync.

Enable CT for each table you want to integrate:

ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;

Grant the Fivetran user VIEW CHANGE TRACKING permission for each of the tables that have change tracking enabled:
```
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO [<username>];
```

Enable change data capture

Enable change data capture at the database level:
```
USE [<database>];
EXEC sys.sp_cdc_enable_db;
```
Enable CDC for each table you want to integrate:
```
EXEC sys.sp_cdc_enable_table  
@source_schema = N'<schema>',
@source_name   = N'<table>',
@role_name     = N'<username>',
@supports_net_changes = 0;
```
When @supports_net_changes is set to 1, an additional non-clustered index is created on the change table. Because this index needs to be maintained, enabling net changes can degrade CDC performance.
Set your CDC retention period:
```
EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 10080; -- Sets the retention period to 7 days
```
While we recommend seven days of change retention, you can set your retention period as low as one day. However, a shorter retention period increases the risk that your logs will expire in between syncs, triggering an automatic full source re-sync.

Fivetran only supports tables with a single CDC capture instance. Our syncs only include tables and columns that are present in a CDC instance. If you add new tables or columns, you must create a new CDC instance that includes them and delete the old instance.

Automatically enable CT or CDC for schema changes (optional)

By default, when you create a new table in your database, you must manually enable CT or CDC for that table before we can sync it. Additionally, if you add a new column to an existing CDC-enabled table, you must manually recreate the CDC capture instance before we can sync the column.

If you want Fivetran to automatically enable CT or CDC for newly created tables and to update CDC capture instances when a new column is added, install the following stored procedures on your database. If your connector contains only CT-enabled tables, then any subsequent new tables will be CT-enabled. If your connector has a mixture of CT- and CDC-enabled tables or contains CDC-enabled tables only, then we will enable CDC over CT by default.

Install the following script on your database. Before running the script, replace <database> with your database name.

If you have configured your connector to Allow columns, you can exclude the [dbo].[sp_ft_enable_cdc] and [dbo].[sp_ft_enable_change_tracking] stored procedures. If you have configured your connector to Block All, you can exclude all the following stored procedures.

USE [<database>];

/****** Object:  StoredProcedure [dbo].[sp_ft_enable_cdc]    Script Date: 1/17/2024 11:12:55 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_cdc]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

   -- Enable CDC for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);

   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);

      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');

      -- Build and execute the SQL statement to enable CDC for the table
      SET @SqlStatement = '
      BEGIN TRY
         EXEC sys.sp_cdc_enable_table
             @source_schema = ''' + PARSENAME(@TableName, 2) + ''',
             @source_name   = ''' + PARSENAME(@TableName, 1) + ''',
             @role_name     = ''' + @FivetranUser + ''',
             @supports_net_changes = 0;
         
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';

     -- Execute the dynamic SQL statement
     EXEC sp_executesql @SqlStatement;
   END

   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO


/****** Object:  StoredProcedure [dbo].[sp_ft_enable_change_tracking]    Script Date: 1/17/2024 11:10:45 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_change_tracking]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;
      
   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));
      
   -- Enable change tracking for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);
      
   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);
      
      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');
         
      -- Build and execute the SQL statement to enable change tracking
      SET @SqlStatement = '
      BEGIN TRY
         ALTER TABLE ' + @TableName + ' ENABLE CHANGE_TRACKING;
         GRANT VIEW CHANGE TRACKING ON ' + @TableName + ' TO ' + @FivetranUser + ';
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';
         
      -- Execute the dynamic SQL statement
      EXEC sp_executesql @SqlStatement;
   END
      
   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO

  
/****** Object:  StoredProcedure [dbo].[sp_ft_get_tables_with_ddl_changes]    Script Date: 1/30/2024 9:10:47 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_get_tables_with_ddl_changes]
   @TableList NVARCHAR(MAX),
   @ddlTime DATETIME = NULL -- New parameter for ddlTime
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Declare a table variable to store the results
   DECLARE @ChangedTables TABLE (
      TableName NVARCHAR(MAX),
      DdlTime DATETIME
   );

   -- Split the comma-separated list into a table variable
   DECLARE @TableNames TABLE (
      TableName NVARCHAR(MAX)
   );
      
   INSERT INTO @TableNames (TableName)
   SELECT value
   FROM STRING_SPLIT(@TableList, ',');
      
   -- Check for DDL changes in cdc.ddl_history
   INSERT INTO @ChangedTables (TableName, DdlTime)
   SELECT
      tn.TableName,
      MAX(dh.ddl_time) AS LatestDdlTime
   FROM cdc.ddl_history dh
   INNER JOIN @TableNames tn ON CONCAT(OBJECT_SCHEMA_NAME(dh.source_object_id), '.', OBJECT_NAME(dh.source_object_id)) = tn.TableName
   WHERE @ddlTime IS NULL OR dh.ddl_time >= @ddlTime
   GROUP BY tn.TableName;
      
   -- Return the list of tables with the latest DDL changes
   SELECT TableName, DdlTime
   FROM @ChangedTables;
END;
GO



/****** Object:  StoredProcedure [dbo].[sp_ft_update_cdc]    Script Date: 1/31/2024 1:19:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_update_cdc]
   @TableName NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX),
   @CaptureInstance NVARCHAR(MAX),
   @CapturedColumnList NVARCHAR(MAX)
WITH EXECUTE AS OWNER
AS
BEGIN
   SET NOCOUNT ON;

    -- Table to store tables with results
    CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

    DECLARE @SourceSchema NVARCHAR(MAX);
    DECLARE @SourceName NVARCHAR(MAX);

    BEGIN TRY
        -- Assign values to variables for PARSENAME function
        SET @SourceSchema = PARSENAME(@TableName, 2);
        SET @SourceName = PARSENAME(@TableName, 1);

        -- Step 1: Disable the current CDC instance
        EXEC sys.sp_cdc_disable_table
            @source_schema    = @SourceSchema,
            @source_name      = @SourceName,
            @capture_instance = @CaptureInstance;

        -- Step 2: Create a new CDC instance
        EXEC sys.sp_cdc_enable_table
            @source_schema        = @SourceSchema,
            @source_name          = @SourceName,
            @role_name            = @FivetranUser,
            @capture_instance     = @CaptureInstance,
            @captured_column_list = @CapturedColumnList,
            @supports_net_changes = 0;

        -- Insert the result into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, 'Success');
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        -- Insert the error message into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, ERROR_MESSAGE());
    END CATCH;

    -- Return the list of tables with results
    SELECT * FROM #ResultTables;

    -- Drop the temporary table
    DROP TABLE #ResultTables;
END;
GO

Grant the Fivetran user execute permission.

USE [<database>];
GRANT EXECUTE ON dbo.sp_ft_enable_cdc TO <username>;
GRANT EXECUTE ON dbo.sp_ft_enable_change_tracking TO <username>;
GRANT EXECUTE ON dbo.sp_ft_get_tables_with_ddl_changes TO <username>;
GRANT EXECUTE ON dbo.sp_ft_update_cdc TO <username>;

Detect changes using Fivetran Teleport Sync

You don't need to do any additional configuration to use Fivetran Teleport Sync.

Finish Fivetran configuration

In your connection setup form, enter a Destination schema prefix. This is used as the connection name and cannot be modified once the connection is created.
In the Destination names field, choose the naming convention you want Fivetran to use for the schemas, tables, and columns in your destination:
- Source naming: Preserves the original schema, table, and column names from the source system in your destination, and ignores the Destination schema prefix specified in the setup form. However, when multiple connections share the same source schema name (for example, public), Fivetran stores their tables in the same destination schema. Tables with duplicate names may lead to overwrites and data inconsistencies. Be sure to use unique table names across connections that write to the same schema.
- Fivetran naming: Standardizes the schema, table, and column names in your destination according to the Fivetran naming conventions.
If you want to modify your selection, make sure you do it before you start the initial sync.
Depending on your selection, we will either prefix the connection name to each replicated schema or use the source schema names instead.
In the Host field, enter the host you noted in the Enable access step.
In the Port field, enter your database instance's port number. The port is usually 3342.
In the Database field, enter the name of the database that you want to connect to Fivetran.
Choose your Authentication Method from the options available in the drop-down. Based on your selection, complete the following fields:
Microsoft Entra ID OAuth (default)
- In the Tenant ID field, enter your Microsoft Entra ID tenant ID you found in the Choose authentication method step.
- Click Authorize to start the OAuth sign-in process. You will be redirected to the Microsoft Entra ID sign-in and consent page. After granting consent, you will be redirected back to the Fivetran connection setup form, and the authorization will be saved automatically. If you need to re-authenticate later, click Re-Authorize Connection.
Microsoft Entra ID Managed Identity (only available on Hybrid Deployment)
- If you are using a User Assigned Managed Identity, enter the Client ID you found in the Choose authentication method step in the User Assigned Managed Identity (optional) field.
- If you are using a System Assigned Managed Identity, leave the User Assigned Managed Identity (optional) field blank.
Microsoft Entra ID Service Principal + Client Secret
- In the Client ID field, enter the Service Principal Client ID you found in the Choose authentication method step.
- In the Client Secret field, enter the client secret you found in the Choose authentication method step.
Microsoft Entra ID Service Principal + Client Certificate
- In the Client ID field, enter the Service Principal Client ID you found in the Choose authentication method step.
- In the Client Certificate field, upload the client certificate you prepared in the Choose authentication method step.
- (Optional) In the Private Key for Client Certificate (optional) field, upload the private key file from the Choose authentication method step if it is stored separately from the certificate.
Username and Password
- In the User field, enter <username>@<servername>. The <servername> is the first part of the server name you noted in the Enable access step (for example, <servername>.database.windows.net).
- In the Password field, enter the password for the Fivetran-specific user you created in the Create user step.
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected for the connection. To assign a different agent, click Replace agent, select the agent you want to use, and click Use Agent.
(Not applicable to Hybrid Deployment) From the Connection method drop-down menu, select how Fivetran connects to your database and enter the required information:
- Connect directly
- Connect via an SSH tunnel
  - Make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel.
  - In the SSH Host field, enter the hostname or IP address of your SSH server. Do not use a load balancer's IP address or hostname.
  - In the SSH Port field, enter the port used by the SSH server.
  - In the SSH User field, enter the username of the SSH user.
  - If you enabled TLS on your database in the Choose connection method step, keep the Require TLS through Tunnel toggle turned ON.
- Connect via private networking
  - Fivetran enables TLS for private networking connections by default. To disable it, turn the Require TLS when using Private Networking toggle OFF.
- Connect via Proxy Agent
  - Select an existing agent from the Proxy agents drop-down list or click + Configure a new proxy agent to set up a new agent.
  - If you enabled TLS on your database in the Choose connection method step, keep the Require TLS when using Proxy Agent toggle turned ON.
Choose your incremental Update Method:
- Read changes using CT or CDC mechanisms
- Detect Changes via Fivetran Teleport Sync
Click Save & Test. Fivetran tests and validates our connection to your Azure SQL managed instance database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Azure SQL managed instance and that it is properly configured:

The Connecting to SSH tunnel test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH tunnel. We skip this test if you aren't connecting using SSH.
The Checking Host connectivity test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
The Connecting to database test checks that we can access your database and validates the TLS certificate if TLS is enabled.
The Validating replication config test verifies that your database has CT or CDC enabled. We skip this test if you're using Fivetran Teleport Sync.

The tests may take a few minutes to finish running.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home

Azure SQL Managed Instance Setup Guide

Prerequisites

Setup instructions

Choose authentication method

Choose connection method

Enable access

Create user

Grant user permissions

Enable incremental updates

Finish Fivetran configuration

Setup tests

Related articles