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-JoinIf the task is disabled (which can happen on some systems), enable it using PowerShell:
Enabling the Scheduled Task
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
- 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:
klistYou 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:
- Client Authentication: Windows Server 2022 authenticates to Microsoft Entra ID using hybrid join credentials
- Kerberos Ticket Request: The system requests a cloud Kerberos ticket during logon
- SQL Authentication: Applications use the cloud Kerberos ticket to authenticate to Azure SQL Managed Instance
- 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.