# 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](#prerequisites)

[Azure SQL Server Guide](#azure-sql-server-guide)\
   [Choose Authentication Method](#step-1-choose-authentication-method)\
   [Enable Access](#step-2-enable-access)\
   [Create User](#step-3-create-user)\
   [Grant User Permissions](#step-4-grant-user-permissions)\
   [Enable Incremental Updates](#step-5-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`)

{% hint style="info" %}
**IMPORTANT**: We do not support single-user mode.
{% endhint %}

## Azure SQL Server Guide <a href="#azure-sql-server-guide" id="azure-sql-server-guide"></a>

### Step 1: Choose Authentication Method <a href="#step-1-choose-authentication-method" id="step-1-choose-authentication-method"></a>

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

**Authenticate with Microsoft Entra ID (formerly Azure Active Directory)**

{% hint style="info" %}
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](https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure) to properly enable Entra ID Authentication on your Azure SQL Database. You must have an AD admin user role to authorize Drivetrain's access
{% endhint %}

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.

<figure><img src="/files/Nu6sRQw9UqWyq5Ttfm6c" alt=""><figcaption></figcaption></figure>

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

<figure><img src="/files/vkKlOGUASixx34q7NM5v" alt=""><figcaption></figcaption></figure>

4. 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;
```

{% hint style="info" %}
Note: Please share the **Tenant ID** along with other details which you will setup in this guide to your customer success manager
{% endhint %}

**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 <a href="#step-2-enable-access" id="step-2-enable-access"></a>

1. Log in to the [Azure console](https://azure.microsoft.com/en-us/features/azure-portal/).
2. On the Azure main page, select **SQL databases.**
3. Click on the SQL database that you want to connect to Drivetrain.

<figure><img src="/files/TiqIzREUqATWFxV397lQ" alt=""><figcaption></figcaption></figure>

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

<figure><img src="/files/o2vMv1XkwtaQEtZJczbP" alt=""><figcaption></figcaption></figure>

5. Click **Set server firewall.**
6. On the Firewall settings page, add a new firewall rule with a name as “**Drivetrain Integration**”. Use [this](https://fivetran.com/docs/using-fivetran/ips) link to enter the IP for your database region. Click on **Save**

{% hint style="info" %}
Note: Drivetrain connects to Azure Directly, for which you must have enabled TLS on your database. Follow Azure's [TLS setup instructions](https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings#minimal-tls-version) to enable TLS on your database.

Please note, above link mentioned in 6th point contains information about Fivetran IPs.<br>
{% endhint %}

<figure><img src="/files/xNiMhGcuDJVF2MKGLGAN" alt=""><figcaption></figcaption></figure>

### Step 3: Create User <a href="#step-3-create-user" id="step-3-create-user"></a>

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

{% hint style="info" %}
IMPORTANT: If you are authenticating with Microsoft Entra ID (formerly Azure Active Directory), skip ahead to Grant user permissions step.
{% endhint %}

1. Open a connection to your Azure SQL database.
2. Add a [container database user](https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable) 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>';

```

{% hint style="info" %}
Please nore username and password in the same secure document.
{% endhint %}

### Step 4: Grant User Permissions <a href="#step-4-grant-user-permissions" id="step-4-grant-user-permissions"></a>

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:<br>

   ```
   /*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](https://fivetran.com/docs/connectors/databases/sql-server#changetracking) with [history mode](https://fivetran.com/docs/using-fivetran/features#historymode) 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](https://fivetran.com/docs/connectors/databases/sql-server/troubleshooting/grant-view-server-state-linux) 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](https://fivetran.com/docs/connectors/databases/sql-server#updatingdata). 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);
```

{% hint style="info" %}
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](https://fivetran.com/docs/connectors/databases#automaticfullsourceresyncforsqldatabases)
{% endhint %}

2. Enable CT for each table you want to integrate

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

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

{% hint style="info" %}
(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.
{% endhint %}

{% hint style="info" %}
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](http://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)
   {% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://integrations.drivetrain.ai/integration-guide/integrate-with-drivetrain/databases-and-warehouses/integrating-with-azure-sql-server.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.