Enable Microsoft Entra authentication for SQL Server on Azure VMs

Applies to: SQL Server on Azure VM

This article teaches you to enable authentication with Microsoft Entra ID (formerly Azure Active Directory) for your SQL Server on Azure virtual machines (VMs).

Overview

Starting with SQL Server 2022, you can connect to SQL Server on Azure VMs using one of the following Microsoft Entra authentication methods:

• Password offers authentication with Microsoft Entra credentials
• Universal with MFA adds multifactor authentication
• Integrated uses federation providers like Active Directory Federation Services (ADFS) to enable single sign-on (SSO) experiences
• Service Principal enables authentication from Azure applications
• Managed Identity enables authentication from applications assigned Microsoft Entra identities

When you create a Microsoft Entra login for SQL Server and when a user connects using that login, SQL Server uses a managed identity to query Microsoft Graph. When you enable Microsoft Entra authentication for your SQL Server on Azure VM, you need to provide a managed identity that SQL Server can use to communicate with Microsoft Entra ID. This managed identity needs to have permissions to query Microsoft Graph.

When enabling a managed identity for a resource in Azure, the security boundary of the identity is the resource to which it's attached. For example, the security boundary for a virtual machine with managed identities for Azure resources enabled is the virtual machine. Any code running on that VM is able to call the managed identities endpoint and request tokens. When enabling a managed identity for SQL Server on Azure VMs, the identity is attached to the virtual machine, so the security boundary is the virtual machine. The experience is similar when working with other resources that support managed identities. For more information, read the Managed Identities FAQ.

The system-assigned and user-assigned managed identities used for Microsoft Entra authentication with SQL Server on Azure VMs offer the following benefits:

• System-assigned managed identity offers a simplified configuration process. Since the managed identity has the same lifetime as the virtual machine, there's no need to delete it separately when you delete the virtual machine.
• User-assigned managed identity offers scalability since it can be attached to, and used for Microsoft Entra authentication, for multiple SQL Server on Azure VMs.

To get started with managed identities, review Configure managed identities using the Azure portal.

Prerequisites

To enable Microsoft Entra authentication on your SQL Server, you need the following prerequisites:

• Use SQL Server 2022.
• Register SQL Server VM with the SQL Server Iaas Agent extension.
• Have an existing system-assigned or user-assigned managed identity in the same Microsoft Entra tenant as your SQL Server VM. Configure managed identities using the Azure portal to learn more.
• Azure CLI 2.48.0 or later if you intend to use the Azure CLI to configure Microsoft Entra authentication for your SQL Server VM.

Grant permissions

The managed identity you choose to facilitate authentication between SQL Server and Microsoft Entra ID has to have the following three Microsoft Graph application permissions (app roles): User.Read.All, GroupMember.Read.All, and Application.Read.All.

Alternatively, adding the managed identity to the Microsoft Entra Directory Readers role grants sufficient permissions. Another way to assign the Directory Readers role to a managed identity is to assign the Directory Readers role to a group in Microsoft Entra ID. The group owners can then add the Virtual Machine managed identity as a member of this group. This minimizes involving Microsoft Entra Global administrators and delegates the responsibility to the group owners.

Add managed identity to the role

This section explains how to add your managed identity to the Directory Readers role in Microsoft Entra ID. You need to have Global Administrator privileges to make changes to the Directory Readers role assignments. If you don't have sufficient permission, work with your Microsoft Entra administrator to follow these steps.

To grant your managed identity the Directory Readers role, follow these steps:

1. Open Microsoft Entra ID Roles and administrators in the Azure portal:

2. Type Directory readers in the search box, and then select the role Directory readers to open the Directory Readers | Assignments page:

   

3. On the Directory Readers | Assignments page, select + Add assignments to open the Add assignment page.

   

4. On the Add assignments page, choose No member selected under Select members to open the Select a member page.

   

5. On the Select a member page, search for the name of the managed identity you want to use with your SQL Server VM and add it to the Directory Readers role. For system-assigned managed identities, search for the name of the VM. Use Select to confirm the identity and go back to the Add assignments page.

   

6. Verify that you see your chosen identity under Select members and then select Next.

   

7. Verify that your assignment type is set to Active and the box next to Permanently assigned is checked. Enter a business justification, such as Adding Directory Reader role permissions to the system-assigned identity for VM2 and then select Assign to save your settings and go back to the Directory Readers | Assignments page.

   

8. On the Directory Readers | Assignments page, confirm you see your newly added identity under Directory Readers.

   

Add app role permissions

You can use Azure PowerShell to grant app roles to a managed identity. To do so, follow these steps:

1. Search for Microsoft Graph

   $AAD_SP = Get-AzureADServicePrincipal -Filter "DisplayName eq 'Microsoft Graph'"
   

2. Retrieve the managed identity:

   $MSI = Get-AzureADServicePrincipal -Filter "DisplayName eq '<your managed identity display name>'"
   

3. Assign the User.Read.All role to the identity:

   $AAD_AppRole = $AAD_SP.AppRoles | Where-Object {$_.Value -eq "User.Read.All"}
   New-AzureADServiceAppRoleAssignment -ObjectId $MSI.ObjectId  -PrincipalId $MSI.ObjectId  
   -ResourceId $AAD_SP.ObjectId  -Id $AAD_AppRole.Id
   

4. Assign GroupMember.Read.All role to the identity:

   $AAD_AppRole = $AAD_SP.AppRoles | Where-Object {$_.Value -eq "GroupMember.Read.All"}  
   New-AzureADServiceAppRoleAssignment -ObjectId $MSI.ObjectId  -PrincipalId $MSI.ObjectId  
   -ResourceId $AAD_SP.ObjectId  -Id $AAD_AppRole.Id 
   

5. Assign Application.Read.All role to the identity:

   $AAD_AppRole = $AAD_SP.AppRoles | Where-Object {$_.Value -eq "Application.Read.All"}  
   New-AzureADServiceAppRoleAssignment -ObjectId $MSI.ObjectId  -PrincipalId $MSI.ObjectId  
   -ResourceId $AAD_SP.ObjectId  -Id $AAD_AppRole.Id 
   

You can validate permissions were assigned to the managed identity by doing the following:

1. Go to Microsoft Entra ID in the Azure portal.
2. Choose Enterprise applications and then select All applications under Manage.
3. Filter the Application type by Managed identities.
4. Select the managed identity and then choose Permissions under Security. You should see the following permissions: User.Read.All, GroupMember.Read.All, Application.Read.All.

Enable outbound communication

For Microsoft Entra authentication to work, you need the following:

• Outbound communication from SQL Server to Microsoft Entra ID and the Microsoft Graph endpoint.
• Outbound communication from the SQL client to Microsoft Entra ID.

Default Azure VM configurations allow outbound communication to the Microsoft Graph endpoint, as well as Microsoft Entra ID, but some users choose to restrict outbound communication either by using an OS level firewall, or the Azure VNet network security group (NSG).

Firewalls on the SQL Server VM and any SQL client need to allow outbound traffic on ports 80 and 443.

The Azure VNet NSG rule for the VNet that hosts your SQL Server VM should have the following:

• A Service Tag of AzureActiveDirectory.
• Destination port ranges of: 80, 443.
• Action set to Allow.
• A high priority (which is a low number).

Enable Microsoft Entra authentication

You can enable Microsoft Entra authentication to your SQL Server VM by using the Azure portal, or the Azure CLI.

az sql vm show -n sqlvm -g myresourcegroup --expand * 

    "azureAdAuthenticationSettings": { 
      "clientId": "11111111-2222-3333-4444-555555555555" },

Limitations

Consider the following limitations:

• Microsoft Entra authentication is only supported with SQL Server 2022 running on Windows VMs registered with the SQL IaaS Agent extension and deployed to the public cloud. Only supported scenarios of the SQL IaaS Agent extension are supported, such as a default instance, or a single named instance. Failover cluster instances are not supported.
• The identity you choose to authenticate to SQL Server has to have either the Directory Readers role in Microsoft Entra ID or the following three Microsoft Graph application permissions (app roles): User.Read.All, GroupMember.Read.All, and Application.Read.All.
• Once Microsoft Entra authentication is enabled, there's no way to disable it.
• Currently, authenticating to SQL Server on Azure VMs through Microsoft Entra authentication using the FIDO2 method isn't supported.

Next steps

Review the security best practices for SQL Server.

For other articles related to running SQL Server in Azure VMs, see SQL Server on Azure Virtual Machines overview. If you have questions about SQL Server virtual machines, see the Frequently asked questions.

To learn more, see the other articles in this best practices series:

• Quick checklist
• VM size
• Storage
• HADR settings
• Collect baseline