PoC for Connecting Azure SQL Managed Instance using Windows Authentication for Microsoft Entra ID

7 min readAug 23, 2024

Connect your Azure SQL MI using Window Auth

Introduction

Azure SQL Managed Instance (MI) is a PaaS service in Azure. It offers broadest SQL server database engine compatibility. It allows multiple types of authentication mechanism. However, some legacy application requires windows authentication especially when they are running in on-premises environment. When connecting those applications to Azure SQL MI, it brings some challenges. In this PoC, I’ll be demonstrating how to leverage windows authentication for connecting to SQL MI.

Kerberos authentication for Microsoft Entra ID (formerly Azure Active Directory) enables Windows Authentication access to Azure SQL Managed Instance.

Architecture

The following diagram gives an overview of how Windows Authentication is implemented for a managed instance using Microsoft Entra ID and Kerberos:

Infra Setup in Azure

In this PoC, I provisioned the following:

1- Virtual Machine (Windows Server 2022)

2- Azure SQL Managed Instance

A Virtual Network (vNet) is created with two subnets. One for SQL MI which is a delegated subnet and one for VM as shown below.

Setup VM

In the following steps, a virtual machine will be deployed. This VM should be setup to allow login using Microsoft Entra ID based authentication.

To use Microsoft Entra login for a Windows VM in Azure, you must:

Enable the Microsoft Entra login option for the VM.
Configure Azure role assignments for users who are authorized to sign in to the VM.

There are two ways to enable Microsoft Entra login for your Windows VM:

The Azure portal, when you’re creating a new Windows VM.
Add “AADLoginForWindows” extension if VM is already created.

If you are creating a new VM, under Management Tab, Select Enable system assigned managed identity and Microsoft Entra ID checkbox as shown below. This action should happen automatically after you enable login with Microsoft Entra ID.

Upon creation of VM, you can verify if the extension AADLoginForWindows is installed in the VM. you can visit Settings →Entensions+applications tab as show below.

If VM is already deployed, you can add this extension by clicking +Add and look for Azure AD based Windows Login and follow the Next button.

You can also add the extension by launching cloud shell and run the following CLI command.

az vm extension set --publisher Microsoft.Azure.ActiveDirectory --name AADLoginForWindows --resource-group myResourceGroup --vm-name myVM

After the extension is installed on the VM, provisioningState shows Succeeded.

Configure role assignment for the VM

Now that you’ve created the VM, you need to assign one of the following Azure roles to determine who can sign in to the VM. To assign these roles, you must have the Virtual Machine Data Access Administrator role, or any role that includes the Microsoft.Authorization/roleAssignments/write action such as the Role Based Access Control Administrator role. However, if you use a different role than Virtual Machine Data Access Administrator, we recommend you add a condition to reduce the permission to create role assignments.

Virtual Machine Administrator Login: Users who have this role assigned can sign in to an Azure virtual machine with administrator privileges.
Virtual Machine User Login: Users who have this role assigned can sign in to an Azure virtual machine with regular user privileges.

Add the role assignment at the resource group level where VM is. VM will inherit it.

Go to the resource group of VM and select IAM tab, then click +Add and select Add role assignment as shown.

2. Select “Virtual Machine Administrator Login”.

3. Click Members Tab. with User, group, or service principal selected, click + Select members.

4. Find user that you want to give access as an administrator to this VM. Click Select. Then Click “Review + Assign”

5. After the role is assigned. Under IAM, in Role assignment tab, it will look similar to as shown below.

Remote Desktop the VM and Perform Kerberos Setup

Note: if your Entra ID account is using MFA, you will find issues with RDP. you have to disable MFA for that account.

There are two ways SQL MI can be configured for window auth as shown below.

In this PoC, we will be performing “Modern interactive flow”. Incoming trust-based flow is beyond the scope of this article.

Launch the RDP session and connect to the VM. Provide Entra ID credentials as shown below. Provide password when asked.

Lauch Terminal. Run the following command.

dsregcmd.exe /status

Make sure “AzureAdJoined” should say “YES” as shown below.

Lauch local group policy editor.

Open Search in the Toolbar and type “Run”, or select Run from your Start Menu.
Type “gpedit. msc” in the Run command and click OK.
Open the group policy editor.
Navigate to Administrative Templates\System\Kerberos\.
Select the Allow retrieving the cloud kerberos ticket during the logon setting.
Select “Enabled”. Click OK.

Once it is enabled. it should look like as shown below.

Users with existing logon sessions may need to refresh their Microsoft Entra Primary Refresh Token (PRT) if they attempt to use this feature immediately after it has been enabled. It can take up to a few hours for the PRT to refresh on its own.

To refresh PRT manually, run this command from a command prompt:

dsregcmd.exe /RefreshPrt

Provision Azure SQL Managed Instance

Provision managed instance. Make sure it is reachable from the VM created in the earlier steps. Create MI with System assigned managed identity and System assigned service principal set to “On” as shown below. if SQL Managed instance is already provisioned. Modify the identity as shown below under Identity tab of SQM MI resource.

Grant admin consent to a system assigned service principal

Open Microsoft Entra ID.
Select App registrations.
Select All applications.

3. Select the application with the display name matching your managed instance. The name will be in the format: <managedinstancename> principal.

4. Select API permissions.

5. Select Grant admin consent.

Click “Yes” on confirmation as shown below.

Once the consent is granted to the service principal, it will be shown as below.

Test and Validation

create login <user> from external provider

Then login to SQL Server using window auth as shown below.

Conclusion

Windows Authentication for Azure SQL Managed Instance principals through Microsoft Entra ID (formerly known as Azure Active Directory) allows customers to transition their existing services to the cloud seamlessly, enhancing the user experience and laying the foundation for modernizing security infrastructure. To activate Windows Authentication for Microsoft Entra principals, you need to convert your Microsoft Entra tenant into a standalone Kerberos realm and establish an incoming trust with the customer domain.