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)

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)

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;

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

Step 3: Create User

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

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>';

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:

   Copy

   /*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);

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