AZ-500 Microsoft Azure Security Technologies Exam

Integrate Azure SQL with Microsoft Entra ID

Integrating Azure SQL with Microsoft Entra ID lets you manage database access through your central identity system. This approach replaces traditional SQL logins with Azure AD authentication, improving security and simplifying credential management. Benefits include:

• Centralized password policies, such as password rotation in one place
• Strong multifactor authentication controls
• Seamless use of managed identities for passwordless connections

Configuring the Server-Level Azure AD Administrator

The first step is to set a server-level Azure AD administrator on your logical SQL server or managed instance. You can assign one Entra user or Entra group as the admin in the Azure portal, Azure CLI, or PowerShell. Only one administrator object can be active at a time, but using an Entra group lets multiple team members inherit admin rights without updating the server configuration each time roles change. This underlines the importance of planning your admin assignments before you start.

Granting Rights to Service Principals and Managed Identities

For Azure SQL to validate external identities, your service principals or managed identities must have permissions in Microsoft Entra ID. Assigning the Directory Readers role is a common choice. You might also grant specific Graph API scopes such as:

• User.Read.All
• GroupMember.Read.All
• Application.Read.All
  These permissions let the SQL instance perform group lookups and identity validation, ensuring that database connections by external principals succeed smoothly.

Creating Contained Database Users from External Identities

Once the server admin and permissions are in place, you create contained database users mapped to Entra identities with simple T-SQL commands. For example:

• CREATE USER [alice@contoso.com] FROM EXTERNAL PROVIDER;
• ALTER ROLE db_datareader ADD MEMBER [MyAppSP];
• ALTER ROLE db_owner ADD MEMBER [DataTeamGroup];
  These statements let Entra users, groups, and applications connect under external provider principals, with the correct database roles assigned. This method avoids managing separate SQL credentials, keeping everything under your Entra ID umbrella.

Validating Authentication by Testing Token-Based Logins

Finally, you should validate authentication by testing token-based connections using the Azure CLI or SQL client tools. In CLI, you can run:

az account get-access-token --resource https://database.windows.net/

and then use the returned token in your connection string. In tools like SSMS or Azure Data Studio, select Active Directory Integrated or Active Directory Managed Identity authentication. Run a simple query like SELECT CURRENT_USER; to confirm that your Entra identity successfully connects and has the expected permissions.

Conclusion

Enabling Microsoft Entra database authentication for Azure SQL centralizes identity management, enhances security, and streamlines credential handling. By configuring a server-level Azure AD admin, granting necessary Microsoft Graph permissions to service principals or managed identities, and creating contained database users, you bring all database access under one identity framework. Testing these configurations with token-based logins ensures your setup works as intended.

This approach eliminates SQL-specific passwords and leverages features like multifactor authentication and passwordless connections. Using Entra groups for admin roles and assigning Graph scopes thoughtfully helps you maintain a scalable, secure environment. With these steps in place, your Azure SQL databases are ready to benefit from modern, enterprise-grade authentication and simplified access management.