Generic SQL Server Setup Guide

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

Fivetran connects directly to your database instance. This is an easy and secure connection method.

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your SQL Server host and port (usually 1433) from Fivetran's IPs for your database's region. How you do this will vary based on how your SQL Server database is hosted (cloud platform, on-premises, etc.)

When connecting to a named instance, provide your SQL Server host and instance name as the Host value (in the format <host>\<instance_name>) in your connection setup form. If the SQL Server Browser service is active on your database, you can use port 1433 as the Port value if you want Fivetran to dynamically retrieve the port number of your named instance using this service. Otherwise, use the named instance port as the Port value.

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.

To connect using SSH, do the following:

1. In your connection setup form, select Connect via an SSH tunnel to expose Fivetran's public SSH key. Copy the key by clicking the blue clipboard icon.

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

3. If you want Fivetran to tunnel SSH over TLS, follow Microsoft's TLS setup instructions to enable TLS on your database. Even though TLS is optional, TLS 1.0 is never supported. This is because the server will attempt to encrypt the authentication traffic with an obsolete TLS version, and Fivetran will refuse any TLS 1.0 connection, as it has been deprecated by the IETF.

WARNING: You can only connect to named instances through an SSH tunnel if you specify your SQL Server host as the Host value and the named instance port as the Port value in your connection setup form.

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in AWS’ PrivateLink documentation.

Follow our AWS PrivateLink setup guide to configure PrivateLink for your database.

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

If you're unsure of your TLS version, you can check it in the following way:

1. Navigate to the run menu.
2. Run regedit.
3. Check the following registry values:
   • for TLS 1.1:
     • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\DisabledByDefault must be set to 0
     • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\Enabled must be set to 1
   • for TLS 1.2:
     • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\DisabledByDefault must be set to 0
     • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\Enabled must be set to 1

1. Open SQL Server Configuration Manager.

2. In the tree pane, click SQL Server Network Configuration to expand it.

   

3. Click Protocols for YourInstanceName. If you specified the default instance during installation, the instance name will be MSSQLSERVER.

   

4. In the Status column, verify that TCP/IP is Enabled.

   

   TIP: If Disabled appears, right-click TCP/IP, then click Enable.

5. Right-click TCP/IP, then select Properties.

6. Go to the IP Addresses tab and scroll all the way down.

7. In the IPAll section, enter your database's port number (usually 1433) for the TCP Port, then click Apply.

   

8. Click OK in the warning dialog box that pops up.

   

9. Click OK in the TCP/IP Properties dialog box.

10. In the tree pane, click SQL Native Client Configuration to expand it, then click Client Protocols.

11. In the right-hand column, verify that Enabled appears next to TCP/IP.

    

    TIP: If Disabled appears, right-click TCP/IP, then click Enable.

12. Right-click TCP/IP, then select Properties.

13. Verify that the Default Port is 1433 and that Yes appears next to Enabled.

    

14. Click OK to exit the TCP/IP Properties dialog box.

15. In the tree pane, click SQL Server Services.

16. In the right pane, right-click SQL Server (YourInstanceName), then click Restart.

Change tracking (CT) records when a row in a table has changed, but does not capture the data that was changed. CT also does not capture how many times the row changed or record any previous changes. CT is a built-in tracking mechanism of SQL Server.

IMPORTANT: You cannot use CT if you are connecting Fivetran to a replica.

To learn more, see our change tracking documentation.

Change data capture (CDC) tracks every change that is applied to a table and records those changes in a shadow history table, so you can see how many times a row has changed and view past changes. CDC is a built-in tracking mechanism of SQL Server.

Learn more in our CDC documentation.

Fivetran Teleport Sync is a proprietary incremental sync method that can add delete capture with no additional setup other than a read-only SQL connection.

Learn more in our Fivetran Teleport Sync documentation.

Binary Log Reader is a proprietary incremental sync method that uses a dynamic link library (DLL) to read files directly in your SQL Server host machine's file system. It captures what data was changed and when, so you can see how many times a row has changed and view past changes.

Learn more in our Binary Log Reader documentation.

1. Grant the Fivetran user SELECT permission for the databases, schemas, tables, or specific columns you want Fivetran to sync.

   You can grant access to everything in a given database:

   GRANT SELECT on DATABASE::<database> to <username>;
   

   or all tables in a given schema:

   GRANT SELECT on SCHEMA::<schema> to <username>;
   

   or a specific table:

   GRANT SELECT ON [<schema>].[<table>] TO <username>;
   

   or a set of specific columns in a table:

   GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO <username>;
   

2. (Optional) For SQL Server for Linux, if you are using Change Tracking with history mode enabled, to improve performance and reduce delays, you may want to grant the necessary permissions to the Fivetran user by running the following command:

   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. See the Sync Delays with SQL Server for Linux Using Change Tracking with History Mode Enabled troubleshooting page for details.

Configure the permissions model that best fits your specific business needs:

• SysAdmin model: This model is the easiest to configure. It offers broad permissions and authority over the entire SQL Server instance. A SysAdmin has unrestricted permissions, including the ability to modify server settings, manage databases, and write to any table.
• Minimal model: This model requires the least permissions. It does not require sysadmin or db_owner rights. Instead, the Fivetran database user performs specific actions by using wrapper stored procedures. These procedures encapsulate only the necessary functionality from higher-privileged roles, ensuring Fivetran can execute required operations without broader access. This approach provides a controlled way to delegate permissions while maintaining security.

ALTER SERVER ROLE sysadmin ADD MEMBER <loginname>;

1. Enable change tracking at the database level:

   ALTER DATABASE [<database>] 
   SET CHANGE_TRACKING = ON 
   (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
   

   NOTE: 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 CT 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;
   

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

   NOTE: 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 the CDC retention period:

   EXEC sys.sp_cdc_change_job
   @job_type = N'cleanup',
   @retention = 10080; -- Sets the retention period to 7 days
   

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

4. Set the CDC polling interval:

   EXEC sys.sp_cdc_change_job 
   @job_type = N'capture',
   @pollinginterval = 5; -- in Seconds (5 seconds)
   
   

   NOTE: We recommend using the default polling interval of 5 seconds. Increasing the polling interval can introduce delays in capturing changes. If the polling interval exceeds the incremental update frequency, changes made during that sync window may not be captured, leading to potential data inconsistencies.

1. Acquire the Fivetran Binary Log Reader DLL file from the Fivetran Support team.

2. Place the Binary Log Reader DLL file on your SQL Server host machine.

3. Make a note of the directory path to the DLL file (for example, C:\MSSQL-CLR\Fivetran_Log_Reader.dll), you’ll need it in the following steps.

4. Connect to your SQL Server database as an admin user to configure the Binary Log Reader. You must use the same admin user that you’ll use to create the connector. Using the admin user is a one-time requirement during the installation.

5. Run the following SQL command to enable CLR on your SQL Server database. Perform this step once for each database host.

   IMPORTANT: If CLR is already enabled on your SQL Server database, skip ahead to step 5 (sign the DLL) in this section. To check whether you have CLR enabled, run the SELECT * FROM sys.configurations WHERE name = 'clr enabled' command.

   use master
   if (SELECT value_in_use
   FROM sys.configurations
   WHERE name = 'clr enabled') = 0
   BEGIN
       EXEC sp_configure 'clr enabled', 1;  
       RECONFIGURE;  
   END; 
   

6. Run the following SQL command to sign the DLL. Perform this step once for each database host. Replace '{Path to Fivetran Binary Log Reader DLL}' with the full directory path to the Fivetran Binary Log Reader DLL file on your SQL Server host machine.

   use master;
   IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
   BEGIN
       DROP LOGIN FivetranUser
   END
   IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'FivetranCLRKey')
   BEGIN
       DROP ASYMMETRIC KEY FivetranCLRKey
   END
   IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'FivetranCLRKey')
   BEGIN
       CREATE ASYMMETRIC KEY FivetranCLRKey FROM EXECUTABLE FILE = '{Path to Fivetran Binary Log Reader DLL}';
   END;
   

7. Run the following SQL command to create a login for the Fivetran user from the asymmetric key and grant it the UNSAFE ASSEMBLY permission. Perform this step once for each database host.

   use master
   IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
   BEGIN
       CREATE LOGIN FivetranUser FROM ASYMMETRIC KEY FivetranCLRKey;    
       GRANT UNSAFE ASSEMBLY TO FivetranUser ;  
   END
   

8. Create a stored procedure on your database that knows how to call the log reader DLL. Perform this step once for each database that you want to sync with Fivetran. For example, if you have two databases called sales and analytics on the same SQL Server instance, run the following commands twice - first by replacing <database> with sales, then with analytics.

   i. Run the following SQL command to create the assembly that the stored procedure will execute. Replace <database> with the name of your database and '{Path to Fivetran Binary Log Reader DLL}' with the full directory path to the Fivetran Binary Log Reader DLL file on your SQL Server host machine.

   USE <database>;
   IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Fivetran')
   BEGIN
       DROP PROCEDURE sp_Fivetran_ReadLog
       DROP ASSEMBLY Fivetran
   END
   CREATE ASSEMBLY Fivetran
   FROM '{Path to Fivetran Binary Log Reader DLL}'
   WITH PERMISSION_SET = UNSAFE; 
   

   ii. Run the following SQL command to link the stored procedure to the assembly (created in the previous step) so that invocations of the stored procedure will call the assembly.

   CREATE OR ALTER PROCEDURE sp_Fivetran_ReadLog (@process int, @handle bigint, @offset bigint, @bufSize int, @parity tinyint, @filter varbinary(1000) = null)
   AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ReadLog;
   

   NOTE: Once you complete this step, the DLL/stored procedure is operable by Fivetran.

9. (Minimal permissions model only) Grant the Fivetran user execute permissions on the sp_fivetran_readlog stored procedure you created in the Grant user permissions step.

   GRANT EXECUTE ON sp_fivetran_readlog TO <username>;
   

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

   NOTE: 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>;