Azure SQL Database Setup Guide
Follow these instructions to replicate your Azure SQL database to your destination via Fivetran.
Prerequisites
To connect your SQL Server database to Fivetran, you need:
- SQL Server version 2012 or above
- An Azure account with a DB Owner, SQL Server Contributor, or SQL Security Manager role
- ALTER ANY USER permissions in your database server
- Your database host's IP (e.g.,
1.2.3.4
) or domain (e.g.,your.server.com
) - Your database's port (usually
1433
) - (If you want to connect using SSH) An SSH server
IMPORTANT: We do not support single-user mode.
Setup instructions
IMPORTANT: Do not perform the Choose connection method step if you want to use Hybrid Deployment for your data pipeline.
Choose authentication method
Decide on your preferred authentication method.
Authenticate with Microsoft Entra ID (formerly Azure Active Directory)
IMPORTANT: You must have a properly configured Entra ID enabled within your Azure account to authenticate with Microsoft Entra ID (formerly Azure Active Directory). Follow Azure's Active Directory for Azure SQL setup instructions to properly enable Entra ID Authentication on your Azure SQL Database. You must have an AD admin user role to authorize Fivetran's access.
Microsoft Entra ID allows you to authenticate Fivetran using OAuth-style authentication. You can easily limit permissions and revoke Fivetran's access at any time, while also not sharing any user or password credential information with Fivetran.
In your connector setup form, choose the Connect via Microsoft Entra ID authentication method.
In Microsoft Entra ID, click the Properties button.
Find and copy your tenant ID.
Enter the Tenant ID in your connector setup form, then click Authorize.
In your Azure SQL database, create a user using the following command. Even though you don't share user or password information with Fivetran, the database eventually requires a user to issue queries. The
<PRINCIPAL-NAME>
must either match your Active Directory user's User Principal Name or be a Group Principal Name assigned to that user.
CREATE USER [<PRINCIPAL-NAME>] FROM EXTERNAL PROVIDER;
Authenticate with username and password
With this method, you provide Fivetran with a username and associated password for a properly-configured user on the database. You'll create this user later in this setup guide.
Choose connection method
Next, decide whether to connect your SQL Server database directly, using an SSH tunnel, using Azure Private Link, or using Proxy Agent.
Connect directly (TLS required)
IMPORTANT: 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.
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.
To connect using SSH, create a firewall rule to allow access to your SSH tunnel server's IP address.
Before you proceed to the next step, you must follow our SSH connection instructions to give Fivetran access to your SSH tunnel. If you want Fivetran to tunnel SSH over TLS, follow Azure's TLS setup instructions to enable TLS on your database.
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 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 SQL databases.
Click on the SQL database that you want to connect to Fivetran.
On the database overview page, find the server name and make a note of it. You will need it to configure Fivetran.
Click Set server firewall.
On the Firewall settings page, 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're connecting directly, enter Fivetran's IPs for your database's region.
- If you're connecting using an SSH tunnel, enter your SSH tunnel server's IP address and set the Connection Policy to Proxy.
Click Save.
Create user
If you're authenticating Fivetran with a username and password, create a database user for Fivetran's exclusive use.
IMPORTANT: 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 database.
Add a container database user for Fivetran by running the following command. 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
Once you've created the Fivetran user, grant it SELECT permission for the database, 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>];
or all but a set of specific columns in a table:
GRANT SELECT ON [<schema>].[<table>] TO <username>;
DENY SELECT ON [<schema>].[<table>] ([<column X>], [<column Y>], ...) TO [<username>];
or add a managed database-level role:
ALTER ROLE <role-name> add member [<username>];
Enable incremental updates
For incremental updates, we use one of the following tracking mechanisms:
These mechanisms 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.
Choose to enable change tracking, change data capture, or Fivetran Teleport Sync.
Change tracking
Expand for instructions
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.
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>;
Change data capture
Expand for instructions
Enable change data capture at the database level:
USE TEST; EXEC sys.sp_cdc_enable_db;
Enable CDC for each table you want to integrate:
EXEC sys.sp_cdc_enable_table @source_schema = [<schema>], @source_name = [<table>], @role_name = [<username>];
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.
Set your 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.
Fivetran Teleport Sync
You do not need to do any additional configuration to use Fivetran Teleport Sync.
(Optional) Schema changes for change tracking (CT) and change data capture (CDC) Private Preview
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.
Expand for instructions
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 + '''; 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 -- 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>;
Finish Fivetran configuration
In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example,
1.2.3.4
) or domain (for example,your.server.com
).Enter your database instance's port number. The port number is usually
1433
.For the Database, enter the name of the database you want to connect to Fivetran.
Choose your authentication method. If you selected Connect with a username and password, provide the following information:
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Enable hybrid deployment toggle is set to ON and the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
(Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
- If you enabled TLS on your database in Step 2, set the Require TLS through tunnel toggle to ON.
Choose your incremental update method.
Click Save & Test. Fivetran tests and validates our connection to your Azure SQL 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 database 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 Connecting to Host 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 Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
- The Connecting to Database Test checks that we can access your database.
- The Checking Access to Schema Test checks that we have the correct permissions to access the schemas in your database. It then verifies that your database contains at least one table.
- The Validating Replication Config Test verifies that your database has an incremental sync method enabled (either CDC or CT).
NOTE: The tests may take a few minutes to finish running.
Related articles
description Connector Overview
account_tree Schema Information
settings API Connector Configuration