---
title: "Connect Azure SQL & Synapse"
description: "Connect Datarelix to Azure SQL Database or Azure Synapse Analytics."
canonical: https://docs.datarelix.ai/guides/connections/azure-sql/
---

# Connect Azure SQL & Synapse

Datarelix connects to Azure SQL Database and Azure Synapse Analytics through a read-only query service using the [ODBC Driver 18 for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server). Both T-SQL flavors share the same connector.

## Synapse pool types

Synapse has two SQL surfaces and their auth models differ:

| Pool type | How access is granted |
|-----------|----------------------|
| **Dedicated SQL pool** | Same as Azure SQL Database — requires a contained database user (`CREATE USER ... FROM EXTERNAL PROVIDER`) |
| **Serverless SQL pool** | [Synapse RBAC roles](https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-manage-synapse-rbac-role-assignments) assigned in Synapse Studio — no SQL commands needed |

Where the setup steps below differ between pool types, a callout marks the difference.

## Prerequisites

- An Azure SQL Database, Synapse dedicated SQL pool, or Synapse serverless SQL pool.
- A SQL user (for password auth) or an Entra principal (for Entra auth) with at minimum `SELECT` on the schemas/tables you want to analyze.
- Firewall: whitelist Datarelix's egress IPs under the SQL server's **Networking → Public access** blade (or use a Private Endpoint). See [Azure SQL firewall rules](https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure).

## Auth modes

### Username & password

Classic SQL authentication. Username and password are passed to the ODBC driver; the password is stored encrypted. Straightforward for break-glass service accounts.

**Setup — create a read-only SQL user**

Connect as a server admin and run:

```sql
CREATE LOGIN datarelix_reader WITH PASSWORD = 'choose-a-strong-password';
USE your_db;
CREATE USER datarelix_reader FOR LOGIN datarelix_reader;
ALTER ROLE db_datareader ADD MEMBER datarelix_reader;
```

This grants read-only access via the [`db_datareader`](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles) fixed database role.

**Where to find your credentials**

| Field | Where to get it |
|-------|----------------|
| Host | Azure portal → your SQL server → **Overview** → Server name (e.g. `yourserver.database.windows.net`) |
| Port | `1433` (default) |
| Database | The database name |
| Username | The login you created above |
| Password | The password set in `CREATE LOGIN` |

**What to enter**

```ini
Host:            yourserver.database.windows.net
Port:            1433
Database:        your_db
Allowed schema:  dbo
Username:        datarelix_reader
Password:        ••••••••
```

---

### Connection string

Paste an ODBC connection string. Useful when you already have one from your Azure portal or connection management tooling.

**Where to find your connection string**

Azure portal → your SQL Database → **Connection strings** → **ODBC** tab. Copy and replace `{your_password}` with the actual password.

**What to enter**

```
Driver={ODBC Driver 18 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=your_db;Uid=datarelix_reader;Pwd=your-password;Encrypt=yes;TrustServerCertificate=no
```

---

### Entra OBO

The connection inherits the **currently signed-in user's** Entra identity. The user's bearer token is exchanged via OAuth On-Behalf-Of for a token scoped to `https://database.windows.net`, which the ODBC driver uses directly. No password stored. Best for interactive analysts where you want per-user audit attribution at the database side.

For background, see [Microsoft Entra authentication for Azure SQL](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview) and the [OAuth 2.0 On-Behalf-Of flow](https://learn.microsoft.com/en-us/entra/identity-platform/v2-oauth2-on-behalf-of-flow).

**Setup**

1. In your Azure SQL Database, [map your Entra users to database roles](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure). Connect as the Entra admin and run:
   ```sql
   CREATE USER [user@yourtenant.onmicrosoft.com] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [user@yourtenant.onmicrosoft.com];
   ```
   Or for a group/service principal:
   ```sql
   CREATE USER [YourEntraGroupName] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [YourEntraGroupName];
   ```

2. The Datarelix Entra app registration must have `https://database.windows.net/user_impersonation` configured as a delegated permission. Contact your administrator if your tenant requires admin consent for this.

**Where to find your credentials**

No additional credentials to enter — the connection uses the identity of the signed-in user automatically.

**What to enter**

```ini
Host:            yourserver.database.windows.net
Port:            1433
Database:        your_db
Allowed schema:  dbo
Auth mode:       Entra OBO
```

---

### Service principal — client secret

An Azure AD app registration authenticates using a client ID and client secret. Good for unattended workloads. The client secret is stored encrypted.

**Setup**

1. In the Azure portal, [register an application](https://learn.microsoft.com/en-us/entra/identity-platform/quickstart-register-app) in Microsoft Entra ID. Give it a name (e.g. `datarelix-sql-reader`).
2. Under **Certificates & secrets → Client secrets**, [add a client secret](https://learn.microsoft.com/en-us/entra/identity-platform/how-to-add-credentials). Copy the **Value** immediately — it's only shown once.
3. Copy the **Application (client) ID** from the app's Overview page.
4. Copy your **Directory (tenant) ID** from the same Overview page.
5. Grant the app read access — the method depends on pool type:

   **Azure SQL Database or Synapse dedicated SQL pool** — connect as the Entra admin and run:
   ```sql
   CREATE USER [datarelix-sql-reader] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [datarelix-sql-reader];
   ```

   **Synapse serverless SQL pool** — skip the SQL step. In [Synapse Studio](https://web.azuresynapse.net) go to **Manage → Access control → Add role assignment** and assign the service principal the **Synapse SQL Administrator** role (or a [custom Synapse RBAC role](https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-synapse-rbac-roles) scoped to the serverless pool). No `CREATE USER` command is needed.

**Where to find your credentials**

| Field | Where to get it |
|-------|----------------|
| Tenant ID | Azure AD → your app registration → **Overview** → Directory (tenant) ID |
| Client ID | Azure AD → your app registration → **Overview** → Application (client) ID |
| Client secret | Azure AD → your app registration → **Certificates & secrets** → the secret value you copied at creation |

**What to enter**

```ini
Host:            yourserver.database.windows.net
Port:            1433
Database:        your_db
Allowed schema:  dbo
Auth mode:       Service Principal (secret)
Tenant ID:       xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Client ID:       xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Client secret:   ••••••••
```

---

### Service principal — certificate

App registration authenticates using a certificate (public cert uploaded to Entra, private key stored on the connection). Same use case as the secret variant but with a longer-lived credential that is harder to exfiltrate.

**Setup**

1. Generate a certificate pair:
   ```bash
   openssl req -x509 -newkey rsa:2048 -keyout key.pem -out cert.pem -nodes -days 365 \
     -subj "/CN=datarelix-sql-reader"
   ```
2. In your [app registration](https://learn.microsoft.com/en-us/entra/identity-platform/quickstart-register-app) → **Certificates & secrets → Certificates**, [upload `cert.pem`](https://learn.microsoft.com/en-us/entra/identity-platform/how-to-add-credentials) (the public cert). Azure displays the thumbprint after upload.
3. Grant the app read access using the same method as the secret variant above — `CREATE USER` / `ALTER ROLE` for Azure SQL and dedicated pools; Synapse RBAC role assignment in Synapse Studio for serverless pools.

**Where to find your credentials**

| Field | Where to get it |
|-------|----------------|
| Tenant ID | Azure AD → app registration → **Overview** → Directory (tenant) ID |
| Client ID | Azure AD → app registration → **Overview** → Application (client) ID |
| Certificate thumbprint | Azure AD → app registration → **Certificates & secrets → Certificates** → 40-char hex string shown after upload |
| Certificate PEM | The `key.pem` file you generated — paste the full `-----BEGIN PRIVATE KEY-----` … `-----END PRIVATE KEY-----` block |

**What to enter**

```ini
Host:                   yourserver.database.windows.net
Port:                   1433
Database:               your_db
Allowed schema:         dbo
Auth mode:              Service Principal (certificate)
Tenant ID:              xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Client ID:              xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Certificate thumbprint: AABBCCDDEEFF...
Certificate PEM:        -----BEGIN PRIVATE KEY-----
                        ...
                        -----END PRIVATE KEY-----
```

---

## Scope semantics

The **Allowed schema** field limits introspection and queries to one schema. Default: `dbo`. T-SQL uses `[schema].[table]` qualification.

To analyze multiple schemas, create one connection per schema.

## Discovery

Fully supported. The introspector walks `sys.tables`, `sys.columns`, `sys.foreign_keys`, and `sys.key_constraints` to surface tables, columns, primary keys, and foreign keys.

## Limitations

- No write or DDL queries — the validator rejects `INSERT/UPDATE/DELETE/MERGE/CREATE/ALTER/DROP/GRANT/REVOKE/TRUNCATE`.
- Server-side `TOP` enforcement: the validator AST-rewrites the `SELECT` to add `TOP <row_limit>` when missing.
- Synapse-specific features (PolyBase external tables, `COPY INTO`) are not blocked by the validator, but introspection treats external tables as regular tables.

## Troubleshooting

| Symptom | Likely cause | Fix |
|---------|--------------|-----|
| `Login failed for user` | Bad SQL credentials or firewall | Check **Networking** on the Azure SQL server → whitelist Datarelix's egress IPs. |
| `Could not open a connection to SQL Server` | TLS or driver mismatch | Confirm ODBC Driver 18 is in use; for older servers you may need to adjust TLS enforcement settings. |
| Entra OBO returns 401 | Audience or consent mismatch | Verify the Entra app has `https://database.windows.net/user_impersonation` as a delegated permission and that admin consent has been granted if required by your tenant policy. |
| `AADSTS65001: The user or administrator has not consented` | Tenant requires admin consent | Ask your Azure AD admin to grant consent for the app at: `https://login.microsoftonline.com/<tenant>/adminconsent?client_id=<client-id>` |
| `Forbidden: Principal is not authorized` | User not mapped in the database | Dedicated pool / Azure SQL: run `CREATE USER ... FROM EXTERNAL PROVIDER` and add to `db_datareader`. Serverless pool: assign the Synapse RBAC role in Synapse Studio. |