Microsoft Entra ID support

This guide provides comprehensive information about using Microsoft Entra ID (formerly Azure Active Directory) authentication with the mssql-python driver to connect to SQL Server.

Overview

The mssql-python driver provides robust support for Microsoft Entra ID authentication, enabling secure connections to SQL databases using various authentication methods.

Key Benefits

Centralized Identity Management: Use your organization's identity provider
Multi-Factor Authentication Support: Enhanced security with MFA
Passwordless Authentication: Reduce credential management overhead
Cross-Platform Support: Works on Windows, macOS, and Linux
Zero Code Changes: Seamless integration with existing applications

Authentication Methods

Overview Table

Authentication Method	Windows Support	macOS/Linux Support	Notes
ActiveDirectoryPassword	✅ Yes	✅ Yes	Username/password-based authentication
ActiveDirectoryInteractive	✅ Yes	✅ Yes	Interactive login via browser; requires user interaction
ActiveDirectoryMSI	✅ Yes	✅ Yes	For Azure VMs/containers with managed identity
ActiveDirectoryServicePrincipal	✅ Yes	✅ Yes	Use client ID and secret or certificate
ActiveDirectoryIntegrated	✅ Yes	✅ Yes	Requires Kerberos configuration on Linux/macOS
ActiveDirectoryDeviceCode	✅ Yes	✅ Yes	Device code flow for authentication
ActiveDirectoryDefault	✅ Yes	✅ Yes	Uses default auth based on environment

1. Using Password Authentication

Active Directory Password Authentication allows users to authenticate to Azure data sources using Microsoft Entra ID, supporting both native and federated Entra ID accounts. This mode requires the user's credentials (username and password) to be explicitly included in the connection string.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryPassword;
UID=user@yourdomain.com;
PWD=your-password;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

2. Using Integrated Authentication

Active Directory Integrated Authentication enables seamless, passwordless authentication using Kerberos tickets. This method allows users to connect to Azure SQL data sources without explicitly providing credentials in the connection string.

On Windows, this mode works with domain-joined machines federated with Microsoft Entra ID (typically via Active Directory Federation Services). On Linux and macOS, it requires proper Kerberos configuration to obtain and cache tickets for authentication.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryIntegrated;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

Platform-Specific Requirements

Windows:

Domain-joined machine with Active Directory federated to Microsoft Entra ID
Network connectivity to domain controllers
Proper Kerberos/SSPI configuration

Linux/macOS:

Kerberos client installed and configured (krb5.conf)
Valid Kerberos ticket obtained via kinit command
Network connectivity to Kerberos Key Distribution Center (KDC)

ℹ️ Note: On Linux/macOS, ensure your Kerberos ticket is valid before attempting to connect. Tickets typically expire after a set period and need to be renewed using kinit.

3. Using Interactive Authentication

Active Directory Interactive Authentication supports multi-factor authentication (MFA) for connecting to Azure SQL data sources. When this authentication mode is specified in the connection string, an interactive Azure login window appears, prompting the user to enter their credentials and complete any required MFA steps.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryInteractive;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

4. Using Service Principal Authentication

Active Directory Service Principal Authentication allows a client application to connect to Azure SQL data sources using the client ID and secret of a registered service principal. This method enables secure, non-interactive authentication — ideal for automated or backend processes.

Steps to use this mode:

Register an application in Microsoft Entra ID and generate a client secret.
Grant the service principal the necessary permissions (e.g., db_datareader, db_datawriter, or admin access) on the Azure SQL resource.
Use the client ID and client secret in the connection string to authenticate. The tenant is automatically inferred from the Azure SQL server endpoint.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryServicePrincipal;
UID=your-client-id;
PWD=your-client-secret;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

5. Using Device Code Flow Authentication

Active Directory Device Code Flow Authentication enables client applications to connect to Azure SQL data sources from devices or environments that lack an interactive web browser. The login is performed interactively on a separate device, making this method ideal for headless systems or command-line environments.

Key Notes:

Do not specify a username or password in the connection string.
The Credential property of SqlConnection cannot be set in this mode.
Suitable for headless systems, CLI tools, or remote VMs.

ℹ️ For more information, see OAuth 2.0 Device Code Flow.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryDeviceCodeFlow;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

⚠️ NOTE

The authentication process for Active Directory Device Code Flow may take some time to complete.
Ensure your network and firewall settings allow the authentication flow to complete successfully.

6. Using Managed Identity Authentication

Managed Identity Authentication is the recommended approach for programmatic access to Azure SQL when running within Azure. It allows a client application to authenticate using the system-assigned or user-assigned managed identity of an Azure resource via Microsoft Entra ID.

By using the managed identity to obtain access tokens:

You eliminate the need to manage credentials or secrets.
You simplify access control and improve security.

This method is ideal for services like Azure VMs, App Services, and Azure Functions that support managed identities.

System-Assigned Managed Identity
This identity is automatically created and managed by Azure as part of an Azure resource—such as an Azure SQL managed instance or logical server. It is tied to the lifecycle of the resource: when the resource is deleted, the identity is also removed. A system-assigned identity can only be associated with a single Azure resource.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryMSI;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

7. Using Default Authentication

ActiveDirectoryDefault authentication offers the most flexible and versatile approach to authenticating with Azure SQL data sources. This method supports a wide range of identity sources from the client environment—such as Visual Studio Code, Visual Studio, Azure CLI, and others.

Under the hood, this mode uses the DefaultAzureCredential class from the Azure Identity library, which attempts to acquire an access token by sequentially evaluating multiple credential sources in a predefined order. This makes it ideal for applications that run across diverse development, testing, and production environments without requiring changes to the authentication logic.

Credential Sources Evaluated by DefaultAzureCredential (in order):

Environment variables (Service Principal)
Managed Identity
Shared Token Cache (e.g., Visual Studio, Visual Studio Code)
Azure CLI
Azure PowerShell
Azure Developer CLI

Example: Service Principal (via Environment Variables)

Steps:

Set the following environment variables:
- AZURE_CLIENT_ID
- AZURE_TENANT_ID
- AZURE_CLIENT_SECRET
Ensure that the service principal has the required role or permission to access the Azure SQL resource.
These variables are picked up by most SDKs and the Azure Identity library.
Works cross-platform.

Example: Managed Identity (For Azure VMs)

Steps:

Assign a system-assigned or user-assigned managed identity to your Azure resource (e.g., Virtual Machine, App Service, Azure Function).
Ensure the managed identity has the appropriate RBAC roles (e.g., SQL Server Contributor or a custom role with sufficient permissions) on the target Azure SQL resource.
No code or configuration changes are needed if using DefaultAzureCredential.
The Azure SDK will automatically detect and use the managed identity credentials when running on the Azure resource.

⚠️ Ensure the Azure resource has network access to the SQL Server and necessary firewall rules configured.

Example: Visual Studio (Windows Only)

Steps:

Sign in to Visual Studio with your Microsoft Entra ID account.
Azure SDKs on Windows will use the cached token from Visual Studio when DefaultAzureCredential is used.
No additional configuration required if no other credentials are specified.

⚠️ Not available on macOS/Linux.
❗ Should not be used in production environments.

Example: Azure CLI

Steps:

Run the following command to authenticate using the Azure CLI:
```
az account clear
az login
```
When using DefaultAzureCredential, the Azure SDKs and tools automatically retrieve the cached access token from Azure CLI after a successful az login.

✅ Supported on: Windows, macOS, and Linux
🛠 If you encounter token-related issues, try:

az account clear
az login

Example: Azure PowerShell

Steps:

Run Connect-AzAccount in PowerShell.
Tokens are cached and can be picked up by SDKs or tools that support PowerShell-based credentials.
Works best in PowerShell environments. Less common in code-based integrations.

Example: Azure Developer CLI (azd)

Steps:

Run azd auth login.
Credentials are cached in your developer environment.
SDKs that integrate with azd will use this context.

Below is an example of how to use this authentication method with mssql_python:

import mssql_python

connection_string = """
Server=your-server.database.windows.net;
Database=your-database;
Authentication=ActiveDirectoryDefault;
Encrypt=yes;
"""

conn = mssql_python.connect(connection_string)

⚠️ NOTE

Using multiple credential sources may trigger sequential lookups, which can impact performance. Avoid this pattern in environments with tight SLAs or latency-sensitive workloads. Set only the intended credential source to prevent unnecessary fallbacks.

8. Authenticating with an Access Token

The SQL_COPT_SS_ACCESS_TOKEN attribute (used with attrs_before) allows direct use of an access token obtained from Microsoft Entra ID for authentication—bypassing the need for a username/password or the driver's built-in token acquisition flow.

This method is particularly useful when you've already acquired a valid token externally, such as by using the Azure Identity SDK.

To authenticate using an access token, set the SQL_COPT_SS_ACCESS_TOKEN in the attrs_before parameter when creating the database connection. Below is an example of how to use this authentication method with mssql_python:

import mssql_python
from azure.identity import DefaultAzureCredential
import struct

def get_conn():
    credential = DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("utf-16le")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    return token_struct

SQL_COPT_SS_ACCESS_TOKEN = 1256

conn_str = """
Server=your-server.database.windows.net;
Database=your-database;
"""

conn = mssql_python.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: get_conn()})

⚠️ NOTE

The connection string must not include UID, PWD, Authentication, or Trusted_Connection.

9. Using a Custom Token Provider (`token_provider=`)

The token_provider= parameter on connect() lets you pass an Azure Identity credential (or any compatible object) directly, instead of encoding the authentication method in the connection string or hand-packing a token struct with attrs_before. The driver calls the provider's .get_token(scope) method to acquire an access token for SQL Server, bypassing the driver's built-in credential map.

import mssql_python
from azure.identity import AzureCliCredential

conn = mssql_python.connect(
    "Server=your-server.database.windows.net;Database=your-database;Encrypt=yes;",
    token_provider=AzureCliCredential(),
)

What counts as a token provider (supported)

A token provider is any object that exposes a callable .get_token(scope) method returning an object with a .token attribute (the raw JWT string). The following are supported:

All azure-identity credential classes — for example DefaultAzureCredential, AzureCliCredential, ManagedIdentityCredential, CertificateCredential, ClientSecretCredential, EnvironmentCredential, and InteractiveBrowserCredential.
Any custom object with a matching .get_token(scope) method — for example, a thin wrapper around a token obtained from Microsoft Fabric's mssparkutils. You do not need to depend on azure-identity for this to work.

Examples

Each snippet assumes import mssql_python and a conn_str holding your Server=...;Database=...;Encrypt=yes; string.

# Portable default: picks the right credential per environment (CLI in dev, MI in prod)
from azure.identity import DefaultAzureCredential

conn = mssql_python.connect(conn_str, token_provider=DefaultAzureCredential())

# User-assigned managed identity (production)
from azure.identity import ManagedIdentityCredential

cred = ManagedIdentityCredential(client_id="<user-assigned-mi-client-id>")
conn = mssql_python.connect(conn_str, token_provider=cred)

# Custom provider: any object with .get_token(scope) -> object with a .token attribute
from types import SimpleNamespace

class FabricTokenProvider:
    def get_token(self, scope):
        raw_jwt = mssparkutils.credentials.getToken("https://database.windows.net/")
        return SimpleNamespace(token=raw_jwt)

conn = mssql_python.connect(conn_str, token_provider=FabricTokenProvider())

When to use `token_provider=` vs. `Authentication=`

For environment-portable code, prefer Authentication=ActiveDirectoryDefault in the connection string — the built-in DefaultAzureCredential automatically picks the right credential per environment (Azure CLI during development, Managed Identity in production).
Use token_provider= when you need explicit control over token acquisition — for example, excluding specific providers, using a credential that is not in the built-in map, or passing custom options (such as a user-assigned managed identity client ID) to the credential constructor.

Rules and behavior

One token source only. You may supply exactly one of: token_provider=, Authentication= in the connection string, or attrs_before[SQL_COPT_SS_ACCESS_TOKEN]. Combining token_provider= with either of the other two raises InterfaceError at connect() time.
UID, PWD, and Trusted_Connection are ignored. When token_provider= is set, access-token authentication takes over. If those keywords are present in the connection string they are silently dropped and a UserWarning is emitted — remove them to avoid the warning.
Fail-fast validation. The token is acquired once, up front, so an invalid or unauthorized credential fails immediately at connect() — InterfaceError (missing .get_token, or an empty/invalid token) or OperationalError (token acquisition failed) — rather than later during a query.
Bulk copy is supported. The same credential is honored by the bulk copy APIs, which acquire a fresh token from the provider for the bulk-copy connection.

Out of scope / current limitations

Commercial cloud only. The token scope is fixed to the Azure commercial cloud (https://database.windows.net/.default). Sovereign clouds — Azure US Government, Azure China (21Vianet), and Azure Germany — are not supported through this parameter; a token acquired for a different audience is rejected by SQL Server at login. For sovereign clouds, acquire the token yourself and pass it via attrs_before[SQL_COPT_SS_ACCESS_TOKEN] instead (see section 8).
No token refresh on a live connection. The access token is a pre-connect attribute and cannot be refreshed on an already-open connection. For long-lived connections, your application must close and reopen the connection before the token expires. Pooled token_provider= connections are keyed on the token and are not expiry-aware — they are reused until closed. See Connection pooling.
Continuous Access Evaluation (CAE) is not handled. CAE claims challenges require native driver support and are tracked as follow-up work.
Interactive credentials block connect(). Credentials such as InteractiveBrowserCredential pause connect() until the user completes sign-in. Prefer non-interactive credentials in server or unattended contexts.

Working around the limitations

# No refresh on a live connection: reopen before the token expires
from azure.identity import DefaultAzureCredential

def open_conn():
    return mssql_python.connect(conn_str, token_provider=DefaultAzureCredential())

conn = open_conn()
# For a long-lived worker, periodically recycle to pick up a fresh token:
conn.close()
conn = open_conn()

# Sovereign cloud (e.g. Azure US Government): acquire the token yourself,
# then pass it via attrs_before instead of token_provider=
from azure.identity import AzureAuthorityHosts, DefaultAzureCredential
import struct

SQL_COPT_SS_ACCESS_TOKEN = 1256
cred = DefaultAzureCredential(authority=AzureAuthorityHosts.AZURE_GOVERNMENT)
token_bytes = cred.get_token(
    "https://database.usgovcloudapi.net/.default"
).token.encode("utf-16le")
token_struct = struct.pack(f"<I{len(token_bytes)}s", len(token_bytes), token_bytes)

conn = mssql_python.connect(
    conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct}
)

Uh oh!

Microsoft Entra ID support

Overview

Key Benefits

Authentication Methods

Overview Table

1. Using Password Authentication

2. Using Integrated Authentication

Platform-Specific Requirements

3. Using Interactive Authentication

4. Using Service Principal Authentication

Steps to use this mode:

5. Using Device Code Flow Authentication

Key Notes:

⚠️ NOTE

6. Using Managed Identity Authentication

7. Using Default Authentication

Credential Sources Evaluated by DefaultAzureCredential (in order):

Example: Service Principal (via Environment Variables)

Example: Managed Identity (For Azure VMs)

Example: Visual Studio (Windows Only)

Example: Azure CLI

Example: Azure PowerShell

Example: Azure Developer CLI (azd)

⚠️ NOTE

8. Authenticating with an Access Token

⚠️ NOTE

9. Using a Custom Token Provider (token_provider=)

What counts as a token provider (supported)

Examples

When to use token_provider= vs. Authentication=

Rules and behavior

Out of scope / current limitations

Working around the limitations

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally

9. Using a Custom Token Provider (`token_provider=`)

When to use `token_provider=` vs. `Authentication=`