Muokkaa

Jaa

Enable Microsoft Entra authentication for SQL Server on Azure VMs

  • Artikkeli

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:

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 role admins 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 Privileged Role 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:

    Screenshot of the Roles and administrators page of the Azure portal, searching for and selecting the Directory Readers role.

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

    Screenshot of the Directory Readers page of the Azure portal.

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

    Screenshot of the add assignment page of the Azure portal, with No member selected highlighted.

  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.

    Screenshot searching for members to select in the Azure portal.

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

    Screenshot of the Add assignment page in the Azure portal, with VM2 added as an assignment.

  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.

    Screenshot of settings on the Add assignment in the Azure portal.

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

    Screenshot of the Directory Readers page of the Azure portal showing your VM assignment added to the role.

Add app role permissions

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

Note

Azure AD and MSOnline PowerShell modules are deprecated as of March 30, 2024. To learn more, read the deprecation update. After this date, support for these modules are limited to migration assistance to Microsoft Graph PowerShell SDK and security fixes. The deprecated modules will continue to function through March, 30 2025.

We recommend migrating to Microsoft Graph PowerShell to interact with Microsoft Entra ID (formerly Azure AD). For common migration questions, refer to the Migration FAQ. Note: Versions 1.0.x of MSOnline may experience disruption after June 30, 2024.

  1. Connect to Microsoft Graph

    Connect-MgGraph -Scopes "AppRoleAssignment.ReadWrite.All" -TenantId "<tenant id>"

  2. Retrieve the managed identity:

    $Graph_SP = Get-MgServicePrincipal -Filter "DisplayName eq 'Microsoft Graph'"
$MSI = Get-MgServicePrincipal -Filter "displayName eq '<your managed identity display name>'"

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

    $AAD_AppRole = $Graph_SP.AppRoles | Where-Object {$_.Value -eq "User.Read.All"}  
New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MSI.Id -BodyParameter @{principalId=$MSI.Id; resourceId=$Graph_SP.Id; appRoleId=$AAD_AppRole.Id}

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

    $AAD_AppRole = $Graph_SP.AppRoles | Where-Object {$_.Value -eq "GroupMember.Read.All"}  
New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MSI.Id -BodyParameter @{principalId=$MSI.Id; resourceId=$Graph_SP.Id; appRoleId=$AAD_AppRole.Id}

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

    $AAD_AppRole = $Graph_SP.AppRoles | Where-Object {$_.Value -eq "Application.Read.All"}  
New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MSI.Id -BodyParameter @{principalId=$MSI.Id; resourceId=$Graph_SP.Id; appRoleId=$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.

Note

After Microsoft Entra authentication is enabled, you can follow the same steps in this section to change the configuration to use a different managed identity.

To enable Microsoft Entra authentication to your SQL Server VM, follow these steps:

  1. Navigate to your SQL virtual machines resource in the Azure portal.

  2. Select Security Configuration under Security.

  3. Choose Enable under Microsoft Entra authentication.

  4. Choose the managed identity type from the drop-down, either System-assigned or User-assigned. If you choose user-assigned, then select the identity you want to use to authenticate to SQL Server on your Azure VM from the User-assigned managed identity drop-down that appears.

    Screenshot of the security configuration page for SQL VM in the Azure portal, with Microsoft Entra authentication selected.

After Microsoft Entra authentication has been enabled, you can follow the same steps to change which managed identity can authenticate to your SQL Server VM.

Note

The error The selected managed identity does not have enough permissions for Microsoft Entra authentication indicates that permissions haven't been properly assigned to the identity you've selected. Check the Grant permissions section to assign proper permissions.

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, deployed to any 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: