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:

DefaultAzureCredential

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

1. Environment variables.
2. Workload identity for Kubernetes.
3. Managed identity.
4. Azure CLI credentials.
5. Azure PowerShell credentials.
6. Azure Developer CLI credentials.
7. 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

1. Register an application in Microsoft Entra ID.
2. Create a client secret.
3. 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];

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

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)

Microsoft Entra ID password authentication is based on the OAuth 2.0 Resource Owner Password Credentials (ROPC) grant, which allows an application to sign in the user by directly handling their password.

Microsoft recommends that you don't use the ROPC flow because it's incompatible with MFA. In most scenarios, more secure alternatives are available and recommended. This flow requires a high degree of trust in the application, and carries risks that aren't present in other flows. Use this flow only when more secure flows aren't viable. Microsoft is moving away from this high-risk authentication flow to protect users from malicious attacks. For more information, see Planning for mandatory multifactor authentication for Azure.

When a user is present at sign-in, use ActiveDirectoryInteractive or ActiveDirectoryIntegrated authentication so the audit trail attributes to the signed-in user and Conditional Access policies apply.

For unattended service-to-service scenarios, follow the Microsoft Entra service account guidance:

• If your application runs on Azure infrastructure, use ActiveDirectoryMSI (or ActiveDirectoryManagedIdentity in some drivers). Managed identities eliminate the overhead of maintaining and rotating secrets and certificates.
• If managed identity isn't available (for example, the application runs outside Azure), use ActiveDirectoryServicePrincipal. Where the driver supports it, prefer a client certificate over a client secret. With a certificate, the private key stays on the client and only a signed assertion is sent to Microsoft Entra to authenticate the client. If the key is stored in hardware (such as a TPM or HSM) or marked nonexportable, it can't be copied out as a string the way a client secret can.
• Don't use a Microsoft Entra user account as a service account.

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()}
)

Choose an authentication mode

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

Related content

• Connection strings
• Troubleshooting
• Azure SQL passwordless migration
• Microsoft Entra authentication with Azure SQL