Azure SQL / Synapse

datarelix.ai connects to Azure SQL Database and Azure Synapse Analytics through a read-only query service using the ODBC Driver 18 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 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.ai’s egress IPs under the SQL server’s Networking → Public access blade (or use a Private Endpoint). See Azure SQL firewall rules.

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:

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

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 and the OAuth 2.0 On-Behalf-Of flow.

Setup

In your Azure SQL Database, map your Entra users to database roles. Connect as the Entra admin and run:

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:

CREATE USER [YourEntraGroupName] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [YourEntraGroupName];

The datarelix.ai 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

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

In the Azure portal, register an application in Microsoft Entra ID. Give it a name (e.g. datarelix-sql-reader).
Under Certificates & secrets → Client secrets, add a client secret. Copy the Value immediately — it’s only shown once.
Copy the Application (client) ID from the app’s Overview page.
Copy your Directory (tenant) ID from the same Overview page.
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:
```
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 go to Manage → Access control → Add role assignment and assign the service principal the Synapse SQL Administrator role (or a custom Synapse RBAC role 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

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

Generate a certificate pair:

openssl req -x509 -newkey rsa:2048 -keyout key.pem -out cert.pem -nodes -days 365 \
  -subj "/CN=datarelix-sql-reader"

In your app registration → Certificates & secrets → Certificates, upload cert.pem (the public cert). Azure displays the thumbprint after upload.
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

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.ai’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.