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.

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

• 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

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)

Active Directory Integrated Authentication requires an on-premises Active Directory instance that is federated with Microsoft Entra ID—typically using Active Directory Federation Services (AD FS). When you're signed in to a domain-joined machine, this mode enables seamless access to Azure SQL data sources without prompting for credentials.

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)

• Windows Operating System
• Domain-Joined Machine
• Network Connectivity
• Kerberos Configuration

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)

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.

1. Register an application in Microsoft Entra ID and generate a client secret.
2. Grant the service principal the necessary permissions (e.g., db_datareader, db_datawriter, or admin access) on the Azure SQL resource.
3. Use the client ID, client secret, and tenant ID in the connection string to authenticate.

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; Authority Id=your-tenant-id; Encrypt=yes; """ conn = mssql_python.connect(connection_string)

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.

• 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 timeout for Active Directory Device Code Flow defaults to the connection's Connect Timeout setting.
Make sure to specify a Connect Timeout that provides enough time to go through the authentication process.

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)

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.

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

Steps:

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

Steps:

1. Assign a system-assigned or user-assigned managed identity to your Azure resource (e.g., Virtual Machine, App Service, Azure Function).
2. 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.
3. No code or configuration changes are needed if using DefaultAzureCredential.
4. 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.

Steps:

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

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

Steps:

1. Run the following command to authenticate using the Azure CLI:

   az account clear az login

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

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.

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.

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.