Configuring Windows Server 2022 for Windows Authentication to SQL Managed Instance

This comprehensive guide walks through the essential steps to enable Windows Authentication from Windows Server 2022 to Azure SQL Managed Instance using Microsoft Entra ID and the modern interactive authentication flow.
Prerequisites and System Requirements

For Windows Server 2022 to successfully connect to Azure SQL Managed Instance using Windows Authentication, several prerequisites must be met:

• Operating System: Windows Server 2022 or higher is required for the modern interactive authentication flow
• Microsoft Entra ID Integration: On-premises Active Directory must be synchronized with Microsoft Entra ID using Microsoft Entra Connect
• Device Join Status: The server must be Microsoft Entra hybrid joined or Microsoft Entra joined
• Interactive Authentication: Applications must connect via interactive sessions (supports SSMS and web applications, but not service applications)

Ensuring Microsoft Entra Hybrid Join Status

Verification Commands

To verify that your Windows Server 2022 is properly Microsoft Entra hybrid joined, use the following diagnostic command:

dsregcmd.exe /status

A properly configured hybrid-joined device should show:

AzureAdJoined: YES

DomainJoined: YES

DeviceId: A valid GUID value
Addressing Pending State Issues

If devices appear in a "pending" state in the Microsoft Entra admin center, this indicates that the hybrid join process hasn't completed successfully. Common scenarios include:

• New Domain-Joined Devices: Devices that can't complete the registration process due to connectivity or configuration issues
• Previously Registered Devices: Devices that were moved between organizational units or had their registration disrupted

Resolving Pending State with Scheduled Tasks

For machines in a pending state, the Automatic-Device-Join scheduled task is crucial for completing the hybrid join process. This task is located under:

Task Scheduler Library > Microsoft > Windows > Workplace Join > Automatic-Device-Join
Enabling the Scheduled Task

If the task is disabled (which can happen on some systems), enable it using PowerShell:

Enable-ScheduledTask -TaskPath "\Microsoft\Windows\Workplace Join\" -TaskName "Automatic-Device-Join"

Manual Task Execution

To trigger the device registration process immediately, run the task manually:

schtasks.exe /run /tn "Microsoft\Windows\Workplace Join\Automatic-Device-Join"

Important Notes:

• This command requires local administrator privileges.
• The user must be signed in for the task to complete successfully.
• The task may need to run twice: once to generate the userCertificate attribute, and again after Azure AD Connect synchronization to complete registration.

Understanding the Device Registration Process

The hybrid join process involves multiple components working together:

• Automatic-Device-Join Task: Runs at user logon and generates device certificates
• Azure AD Connect: Synchronizes device information from on-premises AD to Microsoft Entra ID
• userCertificate Attribute: Generated by the device and stored in Active Directory for Azure AD Connect to sync

The task triggers on:

• User logon events
• Event ID 4096 in the Microsoft > Windows > User Device Registration log
• Hourly intervals

Configuring Group Policy for Kerberos Authentication

Essential Group Policy Setting

To enable the modern interactive authentication flow for Windows Authentication to SQL Managed Instance, configure the following group policy setting:

Path: Administrative Templates\System\Kerberos\Allow retrieving the cloud Kerberos ticket during the logon

Configuration Steps

Open Group Policy Editor on your domain controller or management system
Navigate to the Policy Path:

• Computer Configuration
• Administrative Templates
• System
• Kerberos
• Configure the Setting:
• Select "Allow retrieving the cloud kerberos ticket during the logon"
• Set the policy to Enabled
• Click OK to save the configuration

Registry Implementation

This policy setting configures the following registry value:

Registry Hive: HKEY_LOCAL_MACHINE
Registry Path: Software\Microsoft\Windows\CurrentVersion\Policies\System\Kerberos\Parameters
Value Name: CloudKerberosTicketRetrievalEnabled
Value Type: REG_DWORD
Enabled Value: 1

After enabling the group policy, users with existing logon sessions may need to refresh their Microsoft Entra Primary Refresh Token (PRT):

dsregcmd.exe /RefreshPrt

This command should be run from an elevated command prompt to ensure the PRT is properly refreshed.

Verification and Testing

To verify that Kerberos tickets are being retrieved correctly, use the following command after user logon:

klist

You should see Kerberos tickets from kerberos.microsoftonline.com indicating that cloud Kerberos authentication is functioning.

Authentication Flow Overview

The modern interactive authentication flow enables the following process:

1. Client Authentication: Windows Server 2022 authenticates to Microsoft Entra ID using hybrid join credentials
2. Kerberos Ticket Request: The system requests a cloud Kerberos ticket during logon
3. SQL Authentication: Applications use the cloud Kerberos ticket to authenticate to Azure SQL Managed Instance
4. Seamless Access: Users experience single sign-on without requiring line-of-sight to domain controllers

This configuration eliminates the need for traditional trust relationships and enables secure authentication to cloud resources while maintaining familiar Windows Authentication experiences for applications and users.

Troubleshooting Common Issues

Device Registration Problems

Monitor the following event logs for troubleshooting device registration issues:
Event Viewer Path: Microsoft > Windows > User Device Registration > Admin
Key Events: Look for events 204, 304, and 4096
Group Policy Application

• Ensure group policy is properly applied by:
• Running gpupdate /force on target servers
• Verifying the registry value is set correctly

Checking that the policy applies to the correct organizational units

Network Connectivity

Verify that Windows Server 2022 can reach the required Microsoft endpoints for device registration and Kerberos authentication:

• https://enterpriseregistration.windows.net
• https://login.microsoftonline.com
• https://device.login.microsoftonline.com

By following these comprehensive steps, Windows Server 2022 systems will be properly configured to use Windows Authentication with Azure SQL Managed Instance through the modern interactive authentication flow, providing seamless and secure access to cloud database resources.