title	Microsoft Entra Authentication with mssql-python
description	Learn how to connect to Azure SQL using Microsoft Entra ID authentication with the mssql-python driver.
author	dlevy-msft-sql
ms.author	dlevy
ms.date	07/13/2026
ms.service	sql
ms.subservice	connectivity
ms.topic	how-to
ai-usage	ai-assisted

Microsoft Entra authentication with mssql-python

Microsoft Entra ID provides identity-based authentication for Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric through the mssql-python driver. Microsoft Entra authentication offers these capabilities over SQL authentication:

Centralized identity management through Microsoft Entra ID.
Token-based authentication that eliminates the need for passwords.
Support for conditional access policies.
Managed identities for Azure-hosted applications.

The mssql-python driver supports seven Microsoft Entra authentication modes, all configured through the Authentication connection string keyword.

Authentication modes

Set the Authentication keyword in your connection string to one of the following values:

Authentication value	Description
`ActiveDirectoryDefault`	Uses `DefaultAzureCredential`, which tries multiple methods automatically.
`ActiveDirectoryInteractive`	Browser-based interactive sign-in.
`ActiveDirectoryDeviceCode`	Code entry at https://microsoft.com/devicelogin.
`ActiveDirectoryPassword`	Username and password with Microsoft Entra ID. Deprecated.
`ActiveDirectoryMSI`	Managed identity (system-assigned or user-assigned).
`ActiveDirectoryServicePrincipal`	Service principal with client ID and secret.
`ActiveDirectoryIntegrated`	Windows Integrated with Microsoft Entra ID (Kerberos).

Note

The ActiveDirectoryDefault, ActiveDirectoryInteractive, and ActiveDirectoryDeviceCode modes require the azure-identity package. Install it with pip install azure-identity.

DefaultAzureCredential

The ActiveDirectoryDefault mode uses DefaultAzureCredential from the Azure Identity SDK, which tries these authentication methods in order:

Environment variables.
Workload identity for Kubernetes.
Managed identity.
Azure CLI credentials.
Azure PowerShell credentials.
Azure Developer CLI credentials.
Interactive browser, if enabled.

Example: Default authentication

The following example connects with ActiveDirectoryDefault, which uses the DefaultAzureCredential chain to find a valid credential automatically:

import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes;"
)

cursor = conn.cursor()
cursor.execute("SELECT USER_NAME()")
print(f"Connected as: {cursor.fetchval()}")

Use this mode for local development because it picks up Azure CLI credentials automatically. For production, use a specific authentication mode (ActiveDirectoryMSI, ActiveDirectoryServicePrincipal) instead. DefaultAzureCredential walks through multiple credential providers on each first connection, which adds latency that production workloads don't need.

Interactive authentication

For interactive applications, use browser-based authentication. The user must have a database account created with CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER. For full prerequisites, see Configure Microsoft Entra authentication.

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryInteractive;"
    "Encrypt=yes;"
)

On Windows, this mode delegates to the ODBC driver's native interactive flow. On other platforms, it uses the Azure Identity SDK's browser-based authentication.

Device code authentication

Use device code authentication for environments without a browser, such as SSH sessions or containers. The user must have a database account created with CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER. For prerequisites, see Configure Microsoft Entra authentication.

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDeviceCode;"
    "Encrypt=yes;"
)
# Output: To sign in, use a web browser to open https://microsoft.com/devicelogin
# and enter the code XXXXXXX to authenticate.

Follow the prompt to authenticate in a browser on another device.

Service principal authentication

Use service principal authentication for automated applications that don't require user interaction:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryServicePrincipal;"
    "UID=<client-id>;"       # Application (client) ID
    "PWD=<client-secret>;"   # Client secret
    "Encrypt=yes;"
)

Create a service principal

Register an application in Microsoft Entra ID.
Create a client secret.
Grant the service principal access to your database:

-- In Azure SQL
CREATE USER [app-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-name];
ALTER ROLE db_datawriter ADD MEMBER [app-name];

Tip

If CREATE USER fails with error 33131 (duplicate display name), use WITH OBJECT_ID to specify the service principal's Object ID from the Enterprise applications page in the Azure portal (not the App registration page):

CREATE USER [app-name] FROM EXTERNAL PROVIDER
    WITH OBJECT_ID = '<enterprise-app-object-id>';

For details, see Microsoft Entra logins and users with nonunique display names.

Managed identity

Use managed identity authentication for Azure-hosted applications, such as App Service, Azure Functions, and VMs:

System-assigned managed identity

Connect using the identity assigned directly to the Azure resource:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryMSI;"
    "Encrypt=yes;"
)

User-assigned managed identity

Specify the client ID of a user-assigned managed identity in the UID field:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryMSI;"
    "UID=<managed-identity-client-id>;"
    "Encrypt=yes;"
)

Configure database access

Grant the managed identity access in your database. A Microsoft Entra admin must be configured on the server before you can create external users. To enable managed identity on your Azure resource, see Managed identities for Azure resources.

-- Replace 'my-app-service' with your Azure resource name
CREATE USER [my-app-service] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-app-service];
ALTER ROLE db_datawriter ADD MEMBER [my-app-service];

Password authentication (deprecated)

[!INCLUDE Microsoft Entra password authentication deprecation]

Use password authentication when you need a username and password with a Microsoft Entra account. The user must have a database account created with CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryPassword;"
    "UID=<login@domain.com>;"
    "PWD=<password>;"
    "Encrypt=yes;"
)

Windows Integrated authentication

Use Windows Integrated authentication for domain-joined Windows environments with Kerberos. This mode requires your on-premises Active Directory to be federated with Microsoft Entra ID and a Microsoft Entra admin configured on the server:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryIntegrated;"
    "Encrypt=yes;"
)

This mode uses the current Windows user's Kerberos credentials. On Linux and macOS, you must configure Kerberos manually (krb5.conf and a valid keytab or ticket). See Use Active Directory authentication with SQL Server on Linux for client-side Kerberos setup.

Access token authentication

You might acquire tokens externally, for example, through a custom token provider or shared token cache. In these cases, use SQL_COPT_SS_ACCESS_TOKEN with the attrs_before parameter to pass the token directly. This approach bypasses the driver's built-in token acquisition flow.

import mssql_python
from azure.identity import DefaultAzureCredential
import struct

def get_token():
    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 = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;",
    attrs_before={SQL_COPT_SS_ACCESS_TOKEN: get_token()}
)

Important

When using SQL_COPT_SS_ACCESS_TOKEN, the connection string must not include UID, PWD, Authentication, or Trusted_Connection. The token itself handles authentication.

Choose an authentication mode

Scenario	Recommended mode
Development machine	`ActiveDirectoryDefault` (uses Azure CLI)
Azure App Service / Functions	`ActiveDirectoryMSI` (faster than Default)
Azure Kubernetes Service	`ActiveDirectoryDefault` (workload identity)
Automated scripts on-premises	`ActiveDirectoryServicePrincipal`
Interactive desktop app	`ActiveDirectoryInteractive`
SSH/container without browser	`ActiveDirectoryDeviceCode`

Troubleshoot

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

Verify that the user or managed identity exists in the database:

CREATE USER [identity-name] FROM EXTERNAL PROVIDER;

"AADSTS700016: Application not found"

The service principal or application ID is incorrect. Verify the client ID and that the app is registered in your Microsoft Entra tenant.

"Managed Identity endpoint not reachable"

Verify that managed identity is enabled on the Azure resource.
For user-assigned identity, verify the client ID is correct.
Check that the resource has network access to the identity endpoint.

Token acquisition timeout

ActiveDirectoryDefault uses DefaultAzureCredential, which walks a chain of credential providers in sequence until one succeeds. This chain walk adds seconds of latency on the first connection, especially when earlier providers in the chain (environment variables, workload identity) fail before reaching the one that works. In production, specify the credential type directly to skip the chain:

# Slow: DefaultAzureCredential tries multiple providers
conn = mssql_python.connect(connection_string, authentication="ActiveDirectoryDefault")

# Fast: Skip directly to managed identity
conn = mssql_python.connect(connection_string, authentication="ActiveDirectoryMSI")

Navigation Menu

Microsoft Entra authentication with mssql-python

Authentication modes

DefaultAzureCredential

Example: Default authentication

Interactive authentication

Device code authentication

Service principal authentication

Create a service principal

Managed identity

System-assigned managed identity

User-assigned managed identity

Configure database access

Password authentication (deprecated)

Windows Integrated authentication

Access token authentication

Choose an authentication mode

Troubleshoot

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

"AADSTS700016: Application not found"

"Managed Identity endpoint not reachable"

Token acquisition timeout

Related content

FilesExpand file tree

entra-authentication.md

Latest commit

History

entra-authentication.md

File metadata and controls

Microsoft Entra authentication with mssql-python

Authentication modes

DefaultAzureCredential

Example: Default authentication

Interactive authentication

Device code authentication

Service principal authentication

Create a service principal

Managed identity

System-assigned managed identity

User-assigned managed identity

Configure database access

Password authentication (deprecated)

Windows Integrated authentication

Access token authentication

Choose an authentication mode

Troubleshoot

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

"AADSTS700016: Application not found"

"Managed Identity endpoint not reachable"

Token acquisition timeout

Related content