Complete Guide: Setting Up Azure SQL Server with Microsoft Entra ID Authentication (2026 Edition)

A comprehensive, updated guide to configuring Microsoft Entra ID (formerly Azure Active Directory) authentication for Azure SQL Database with security best practices

Introduction

If you’ve been working with Azure SQL Database, you’ve likely encountered the need to move beyond traditional SQL authentication. Microsoft Entra ID authentication (formerly Azure Active Directory) offers centralized identity management, enhanced security through Multi-Factor Authentication (MFA), and eliminates the need for managing separate database credentials.

This guide provides an updated walkthrough for 2026, reflecting the latest Azure Portal experience, naming conventions (Microsoft Entra ID replaced Azure AD in 2023), and security best practices.

Prerequisites

Before you begin, ensure you have:

• An active Azure subscription
• An existing Azure SQL Database or the ability to create one
• A Microsoft Entra ID tenant with users/groups populated
• Appropriate permissions (SQL Security Manager or higher)
• SQL Server Management Studio (SSMS) 20.x or later, or Azure Data Studio

Why Use Microsoft Entra ID Authentication?

🔐 Centralized Identity

Manage database access alongside all other Azure resources from a single identity platform.

🛡️ Enhanced Security

Support for MFA, Conditional Access policies, and passwordless authentication.

⚙️ Simplified Admin

Use groups to manage permissions, automatically reflecting org changes.

✅ Compliance Ready

Meet enterprise security requirements with comprehensive audit logging.

Part 1: Setting the Microsoft Entra Admin

The first step is configuring a Microsoft Entra administrator for your Azure SQL logical server. This admin becomes the entry point for all Microsoft Entra authentication.

Step-by-Step Configuration

1. Navigate to Your SQL Server

Open the Azure Portal and search for “SQL servers” in the search bar. Select your logical server (not the database itself).

2. Access Microsoft Entra ID Settings

In the left navigation pane under Settings, select Microsoft Entra ID.

3. Set the Administrator

Click Set admin at the top of the pane. This opens the identity picker showing all users, groups, and applications in your Microsoft Entra tenant.

4. Select Your Admin Identity

Search for the user or group you want to designate as administrator. Best practice is to use a dedicated Microsoft Entra group (e.g., “SQL-Admins”) rather than an individual user. I know, I didn’t follow that haha…

5. Save the Configuration

💡 Important Notes

• The admin display name must be unique — it cannot match any existing user in the server’s master database
• You can use a user, group, or service principal as the admin
• Only one Microsoft Entra admin can be set per logical server

Part 2: Enabling Microsoft Entra-Only Authentication

For enhanced security, consider enabling Microsoft Entra-only authentication, which disables SQL authentication entirely.

Configuration Steps

1. Access the Microsoft Entra ID Settings

Navigate to your SQL server → Settings → Microsoft Entra ID.

2. Enable the Setting

Check the box labeled Support only Microsoft Entra authentication for this server.

3. Confirm and Save

This action disables SQL authentication. Ensure you have a working Microsoft Entra admin configured before enabling.

⚠️ When NOT to Use This Option

• Legacy applications requiring SQL authentication
• Third-party tools without Microsoft Entra support
• Development environments needing flexible access

Part 3: Creating Database Users

Once the Microsoft Entra admin is configured, you can create database users for Microsoft Entra identities.

Connecting as the Microsoft Entra Admin

Use SSMS or DBeaver with one of these authentication methods:

don’t forget to allow public network access to your VM

Creating Contained Database Users

Connect to your target database and run:

-- For a Microsoft Entra user
CREATE USER [user@yourdomain.com] FROM EXTERNAL PROVIDER;

-- For a Microsoft Entra group
CREATE USER [SQL-ReadOnly-Users] FROM EXTERNAL PROVIDER;

-- For a managed identity or service principal
CREATE USER [your-app-name] FROM EXTERNAL PROVIDER;

Granting Permissions

-- Grant read access
ALTER ROLE db_datareader ADD MEMBER [user@yourdomain.com];

-- Grant read/write access
ALTER ROLE db_datawriter ADD MEMBER [user@yourdomain.com];

-- Grant owner access (use sparingly)
ALTER ROLE db_owner ADD MEMBER [SQL-Admins];

Part 4: Configuring Conditional Access and MFA

Setting Up Multi-Factor Authentication

1. Navigate to Microsoft Entra ID

Go to Azure Portal → Microsoft Entra ID → Security → Conditional Access.

I had No Microsoft Entra ID P1 or P2 license Conditional Access requires Entra ID P1 or P2. Without a license, the menu stays read only.

2. Create a New Policy

Click + New policy and configure:

• Name: “MFA for Azure SQL Access”
• Assignments → Users: Select applicable users or groups
• Cloud apps: Select “Azure SQL Database”
• Grant: Require multi-factor authentication

Part 5: Connecting from Applications

Connection String Examples

Using Microsoft Entra Default (Recommended for Azure Services)

Server=tcp:yourserver.database.windows.net,1433;
Database=yourdb;
Authentication=Active Directory Default;

Using Microsoft Entra Managed Identity

Server=tcp:yourserver.database.windows.net,1433;
Database=yourdb;
Authentication=Active Directory Managed Identity;

Code Example: C# with Microsoft.Data.SqlClient

import pyodbc

connection_string = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=tcp:yourserver.database.windows.net,1433;"
    "Database=yourdb;"
    "Authentication=ActiveDirectoryDefault;"
)

conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

cursor.execute("SELECT 1")
row = cursor.fetchone()
print(row)

cursor.close()
conn.close()

You must install ODBC Driver 18 for SQL Server.

You must be signed in with az login or use a managed identity.

Your Entra user or identity needs database access.

Security Best Practices for 2026

Identity Management

1. Use Groups, Not Individual Users — Assign permissions to Microsoft Entra groups for simplified management.
2. Implement Least Privilege — Create custom database roles with minimum required permissions.
3. Separate Admin Groups — Create distinct groups for different environments (dev, staging, production).

Authentication

1. Enable Microsoft Entra-Only Authentication — Disable SQL authentication in production.
2. Require MFA — Configure Conditional Access policies to require multi-factor authentication.
3. Use Passwordless Methods — Prefer managed identities over password-based methods.

Monitoring and Compliance

1. Enable Auditing — Configure Azure SQL auditing to track authentication events.
2. Review Access Regularly — Use Microsoft Entra access reviews to validate database access.
3. Monitor Sign-in Logs — Set up alerts for failed authentication attempts.

Network Security

1. Use Private Endpoints — Connect through Azure Private Link to eliminate public exposure.
2. Configure Firewall Rules — Restrict access to known IP ranges.

Troubleshooting Common Issues

ErrorSolution”Login failed for user”Verify user exists in database, check admin config, ensure account is active”Cannot open server requested by the login”Create database user with CREATE USER … FROM EXTERNAL PROVIDERMFA Prompts Not AppearingUse “Microsoft Entra — Universal with MFA” authentication typeToken Expiration IssuesUse managed identities which handle token refresh automatically

Migration Checklist

• Identify all users and applications using SQL authentication
• Create corresponding Microsoft Entra users/groups
• Create database users for Microsoft Entra identities
• Map existing SQL user permissions to new Microsoft Entra users
• Update application connection strings
• Test all access scenarios
• Configure Conditional Access policies
• Enable auditing for the migration period
• Disable SQL authentication (when ready)
• Remove deprecated SQL logins

Summary

Configuring Microsoft Entra ID authentication for Azure SQL Database provides significant security and management benefits. The key steps are:

1. Set a Microsoft Entra admin on your logical server
2. Create database users for Microsoft Entra identities
3. Configure Conditional Access for MFA and security policies
4. Update applications to use Microsoft Entra authentication
5. Consider Microsoft Entra-only mode for production environments

Additional Resources

Microsoft Learn: Configure Microsoft Entra authentication

Microsoft Entra-only authentication

Security best practices for Azure SQL

Conditional Access for Azure SQL

Last updated: January 2026

Originally based on content discussing Azure AD setup, updated to reflect the Microsoft Entra ID rebrand and current best practices.