SQL Server 2022: Enable Azure AD Authentication for SQL Server on VM

To enable the SQL Server 2022 on a virtual machine to use Azure AD, we need below things:

Managed identity and its permissions to query Microsoft Graph: When you enable the AD on the SQL Server 2022 and create the Azure AD login and that user trying to login, the managed identity will be used to Query the Microsoft Graph and that is the reason we need to give this identity the permissions to Query Microsoft Graph. The security of this managed identity is at the resource level. Here the resource is a Virtual machine.

To set up the Azure AD authentication for SQL Server on VM, we can do this by using System-assigned managed identity or User-assigned managed identity.

System-assigned managed identity is easy to manage and configure as this identity will be deleted when the VM is deleted. It will have validity until the VM has its life whereas the User-assigned managed identity will be used at scale for many SQL Servers on the virtual machines.

In this post, we are learning how to enable the AD authentication to SQL Server on VM. So, we need the SQL Server 2022 which is installed on the Azure VM and we will have to register the Windows SQL Server VM with SQL IaaS Agent extension.

Permissions:

These are the below permissions needed to connect the SQL Server to VM to the managed identity:

User.ReadALL, GroupMember.Read.All, and Application.Read.All.

The managed identity must also have Active Directory reader permissions. Search for the Microsoft Entra ID

Under the default directory, click on the roles and administrators

Make sure you have Azure AD Global administrator permissions before giving the identity the reader permissions to the Azure AD

If you scroll your bar right side, you can see the ellipses button. Click on that to see the description of the role

Under the directory readers, click on assignments and then click on add assignments

From the list, choose the VM name if you want to use the system assigned managed identity

Click on Add at the bottom of the page

You will see the below screen. Make sure the application (VM name) is mentioned under the select members. This is if you choose the system managed identity. Click next.

As you click next, you will see the below screen. Make sure the assignment type is active and click on the box for permanently assigned. At the bottom of the screen, you will see the assign button. Click on that.

Under the directory readers, you will see the vm system assigned identity is being added.

If you want to choose the user-managed identity, choose it.

Enable the Azure AD authentication in the Azure VM

Under the SQL virtual machine (Make sure your VM is turned on and the SQL virtual machine is online as well to see the enabled security configuration option in the left blade under the security tab). Enable the Azure AD authentication. Here, I already enabled it earlier and so you see it as enabled.

Remember: Once you enable the Azure AD authentication, you cannot disable it.

If you want to enable the Azure AD authentication, you will see the below screenshot. Click on enable and hit apply at the bottom of the page.

In this blog post, you have read the process of enabling the Azure AD authentication on Azure VM. Only Windows Server 2022 VM’s are supported to enable the Azure AD authentication with the SQL IaaS Agent extension.

Thank you for reading!

References:

Microsoft article – Enable Azure AD authentication for SQL Server on Azure VMs