Azure SQL Database Setup Guide

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 Database. For more information, see Configure Microsoft Entra authentication for Azure SQL.
• A Microsoft Entra admin set on your Azure SQL logical server. 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:

1. Open the Azure Portal and sign in.

2. Go to Azure Active Directory.

3. In the left-hand menu, click Properties.

   

4. Make a note of the Tenant ID. You need it to complete the Fivetran connection setup form.

   

5. In your Azure SQL Database, 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.

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

Before you begin, make sure you have:

• Microsoft Entra ID (formerly Azure Active Directory) authentication enabled on your Azure SQL Database. For more information, see Configure Microsoft Entra authentication for Azure SQL.
• A user who has the Microsoft Entra admin role on your Azure SQL logical server. 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:

1. (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.

      

2. To allow your Managed Identity (UAMI or SAMI) to access the Azure SQL Database, 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.

Microsoft Entra ID Service Principal with Client Secret authentication lets Fivetran connect to your Azure SQL Database 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:

1. Open the Azure Portal and sign in.

2. Navigate to Microsoft Entra ID > App registrations.

3. Click + New registration.

4. Enter a name for the app (for example, Fivetran-SQL-Connector).

5. Click Register.

6. Go to the app's Overview tab.

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

   

8. In the left-hand menu of your app, go to Certificates & secrets.

9. Under Client secrets, click + New client secret.

10. Enter a description and choose an expiration period.

11. Click Add.

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

13. To allow your Service Principal to connect to the Azure SQL Database, 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.

Microsoft Entra ID Service Principal with Client Certificate authentication lets Fivetran securely connect to your Azure SQL Database 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:

1. Open the Azure Portal and sign in.

2. Navigate to Microsoft Entra ID > App registrations.

3. Click + New registration.

4. Enter a name for the app (for example, Fivetran-SQL-Cert-Auth).

5. Click Register.

6. Go to the app's Overview tab.

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

   

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

9. Navigate to your App registration in the Azure Portal.

10. Go to Certificates & secrets > Certificates.

11. Click Upload certificate.

12. Select a valid .cer, .crt, or .pem file.

13. To allow your Service Principal to connect to the Azure SQL Database, 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.

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

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.

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, do the following:

1. In the connection setup form, select Connect via an SSH tunnel as the Connection Method.

2. Make a note of the Public Key. You will need it to set up your SSH client.

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

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

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.

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

2. Enable CT for each table you want to integrate:

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

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

1. Enable change data capture at the database level:

   USE TEST;
   EXEC sys.sp_cdc_enable_db;
   

2. Enable CDC for each table you want to integrate:

   EXEC sys.sp_cdc_enable_table  
   @source_schema = [<schema>],
   @source_name   = [<table>],
   @role_name     = [<username>],
   @supports_net_changes = 0;
   

   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.

   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.

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

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.

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

2. 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>;