Integrating with Azure SQL Server

Drivetrain uses Fivetran to integrate with Azure SQL Server. We connect to your Azure account directly using TLS. This guide covers the detailed steps for the integration process.

Prerequisites

Azure SQL Server Guide Choose Authentication Method Enable Access Create User Grant User Permissions Enable Incremental Updates

Prerequisites

To connect Azure SQL Server to Drivetrain, 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)

IMPORTANT: We do not support single-user mode.

Azure SQL Server Guide

Step 1: Choose Authentication Method

Select the preferred authentication method, below are the details for both of the authentication methods.

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 Drivetrain's access

Microsoft Entra ID allows you to authenticate Drivetrain using OAuth-style authentication. You can easily limit permissions and revoke Drivetrain's access at any time, while also not sharing any user or password credential information with Drivetrain.

1. Go to the Azure portal, and search Microsoft Entra ID on the search bar.

2. On the Microsoft Entra ID page, click the Properties button.

1. Find and copy your tenant ID and store it in a secure document.

1. In your Azure SQL database, create a user using the following command. Even though you don't share user or password information with Drivetrain, 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;

Note: Please share the Tenant ID along with other details which you will setup in this guide to your customer success manager

Authenticate with Username and Password

With this method, you provide Drivetrain with a username and associated password for a properly-configured user on the database. You'll create this user later in this setup guide.

Step 2: Enable Access

1. Log in to the Azure console.
2. On the Azure main page, select SQL databases.

3. Click on the SQL database that you want to connect to Drivetrain.

1. On the database overview page, find the server name and make a note of it in the same secure document.

1. Click Set server firewall.

2. On the Firewall settings page, add a new firewall rule with a name as “Drivetrain Integration”. Use this link to enter the IP for your database region. Click on Save

Note: Drivetrain connects to Azure Directly, for which you must have enabled TLS on your database. Follow Azure's TLS setup instructions to enable TLS on your database.

Please note, above link mentioned in 6th point contains information about Fivetran IPs.

Step 3: Create User

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

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

1. Open a connection to your Azure SQL database.

2. Add a container database user for Drivetrain 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>';

Please nore username and password in the same secure document.

Step 4: Grant User Permissions

1. Once you have created user for Drivetrain, grant it SELECT permissions for the database, schemas, tables or specific columns you want to sync. Below are sample snippets for every type of access:

   /*For granting access to everything in database*/
   
   GRANT SELECT on DATABASE::[<database>] to [<username>];
   
   /*For granting access to all tables in particular schema*/
   
   GRANT SELECT on SCHEMA::[<schema>] to [<username>];
   
   /*For granting access to a specific table*/
   
   GRANT SELECT ON [<schema>].[<table>] TO [<username>];
   
   /*For granting access to specific columns on a table*/
   
   GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO [<username>];
   
   /*For granting access to all columns but a set of specific columnns*/
   
   GRANT SELECT ON [<schema>].[<table>] TO <username>;
   DENY SELECT ON [<schema>].[<table>] ([<column X>], [<column Y>], ...) TO [<username>];
   
   /*For granting a managed database-level role*/
   ALTER ROLE <role-name> add member [<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 Drivetrain user by running the following command:

USE [master]; GRANT VIEW SERVER STATE TO <username>;

This grants the Drivetrain 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.

Step 5: Enable Incremental Updates

For incremental updates, we use change tracking mechanism. With this mechanism, Drivetrain 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 this updating data documentation. Below are the instructions for this:

1. Enable change tracking at 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

1. Enable CT for each table you want to integrate

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

1. Grant the Drivetrain user VIEW CHANGE TRACKING permission for each of the tables that have change tracking enabled:

GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO <username>;

(Optional) Schema changes for change tracking (CT) - By default, when you create a new table in your database, you must manually enable CT. If you want to automate this process, we have to deploy a script on the database. Please reach out to your customer succces manager for that.

That’s it share the below details with your customer success manager to complete the integration.

1. Database Host’s IP (for example, 1.2.3.4) or Domain (your.server.com)
2. Database Instance’s Port Number

3. Database Name

4. Authentication Method Name

5. Tenant ID (For Entra ID based Auth); Username & Password (For the second type of Auth)