Azure SQL Authentication: SQL vs Entra ID

Authentication is the process of verifying the identity of a user or application trying to connect to a database. In Azure SQL, two primary authentication methods exist: SQL Authentication (username and password stored in the database) and Microsoft Entra ID authentication (identity verified via Azure's cloud identity service). The exam expects you to know the differences, benefits, and limitations of each.

SQL Authentication uses a login and password stored in the master database of the Azure SQL server. The password hash is stored in the sys.sql_logins system view. When a client connects, it sends the username and password over the network (ideally encrypted using TLS). The server hashes the password and compares it to the stored hash. If they match, the login is authenticated and the user is granted access to databases based on user mappings.

Key characteristics:

Microsoft Entra ID authentication allows users to connect to Azure SQL using their cloud identities (user accounts, service principals, or managed identities). The authentication process uses OAuth 2.0 tokens. When a user attempts to connect, the client (e.g., SQL Server Management Studio) acquires an access token from Microsoft Entra ID. The token is then presented to Azure SQL, which validates it using the tenant's public keys. If valid, the user is authenticated and mapped to a database user (contained user) or a server-level login.

Key characteristics:

SQL Authentication: 1. During server creation, set 'Authentication method' to 'Use only Azure Active Directory authentication' or 'Use both SQL and Azure Active Directory authentication'. To use SQL Authentication, you must choose the latter. 2. After creation, create SQL logins in the master database:

CREATE LOGIN MySQLLogin WITH PASSWORD = 'StrongPassword!';

3. Map the login to a database user:

CREATE USER MyUser FOR LOGIN MySQLLogin;

Entra ID Authentication: 1. Set an Entra ID administrator for the Azure SQL server (Azure portal -> SQL server -> Active Directory admin). This user must be an Entra ID user or group. 2. Connect as that admin and create contained database users:

CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;

SQL Authentication flow: 1. Client sends a login packet containing username and password (encrypted via TLS). 2. Server receives the packet, extracts the password, hashes it with a stored salt. 3. Server compares the hash to the stored hash in sys.sql_logins. 4. If match, server checks if the login is enabled and not locked out. 5. Server then checks database user mappings and permissions. 6. If all checks pass, a session is established.

Entra ID Authentication flow: 1. Client (e.g., SSMS) initiates OAuth 2.0 authorization code flow or device code flow. 2. User authenticates to Entra ID (via password, MFA, etc.). 3. Entra ID returns an access token (JWT) with claims including user's object ID and tenant ID. 4. Client sends the token to Azure SQL in the TDS login packet. 5. Azure SQL validates the token's signature using the tenant's public keys (fetched from Entra ID). 6. Server extracts the user's object ID from the token and looks up a matching user in the database. 7. If found, the user is authenticated; permissions are then evaluated.

Enterprise Scenario 1: Large Organization with MFA Requirement

A multinational corporation with 10,000+ employees uses Azure SQL for its data warehouse. They require MFA for all database access to comply with security policies. SQL Authentication cannot meet this requirement, so they deploy Entra ID authentication. They configure conditional access policies to require MFA from outside the corporate network. The DBA sets an Entra ID admin group and creates contained users for each team. When an employee leaves, the IT department disables the user in Entra ID, and database access is instantly revoked. Performance is acceptable—token acquisition adds ~1 second to connection time. Common issues include token expiration (tokens last 1 hour by default; long-running queries may need token refresh). They mitigate by using connection pooling and setting Persist Security Info=False.

Enterprise Scenario 2: Legacy Application with SQL Authentication

A healthcare provider runs a legacy application that hardcodes a SQL login and password. The application cannot be modified to support OAuth 2.0. They must use SQL Authentication. The DBA creates a SQL login with a strong password, rotates it quarterly, and restricts the IP range via firewall. They enable auditing to track login attempts. The risk is that if the password is compromised, an attacker can connect from anywhere (unless IP restricted). They also face the challenge of password expiration—if the password changes, the application breaks until updated. This scenario is common for older apps and is a key reason Microsoft recommends migrating to Entra ID.

Enterprise Scenario 3: Microservices Using Managed Identities

A SaaS company runs microservices on Azure App Service that access Azure SQL. Each service has a system-assigned managed identity. The DBA creates a contained user for each managed identity using CREATE USER [<app-service-name>] FROM EXTERNAL PROVIDER;. The services connect without any credentials—the Azure infrastructure automatically provides tokens. This eliminates secrets management. The DBA grants least-privilege permissions per service. A common misconfiguration is assigning the wrong managed identity or forgetting to enable 'Allow Azure services' in the firewall. When misconfigured, connection errors like 'Login failed for user '' (empty)' occur because the token is not recognized.