Редагувати

Поділитися через

Troubleshoot Windows Authentication for Microsoft Entra principals on Azure SQL Managed Instance

  • Стаття

This article contains troubleshooting steps for use when implementing Windows Authentication principals in Microsoft Entra ID (formerly Azure Active Directory).

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Verify tickets are getting cached

Use the klist command to display a list of currently cached Kerberos tickets.

The klist get krbtgt command should return a ticket from the on-premises Active Directory realm.

klist get krbtgt/kerberos.microsoftonline.com

The klist get MSSQLSvc command should return a ticket from the kerberos.microsoftonline.com realm with a Service Principal Name (SPN) to MSSQLSvc/<miname>.<dnszone>.database.windows.net:1433.

klist get MSSQLSvc/<miname>.<dnszone>.database.windows.net:1433

The following are some well-known error codes:

  • 0x6fb: SQL SPN not found - Check that you've entered a valid SPN. If you've implemented the incoming trust-based authentication flow, revisit steps to create and configure the Microsoft Entra Kerberos Trusted Domain Object to validate that you've performed all the configuration steps.

  • 0x51f - This error is likely related to a conflict with the Fiddler tool. To mitigate the issue, follow these steps:

    1. Run netsh winhttp reset autoproxy
    2. Run netsh winhttp reset proxy
    3. In the Windows registry, find Computer\HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\iphlpsvc\Parameters\ProxyMgr and delete any subentry that has a configuration with a port :8888
    4. Restart the machine and try again using Windows Authentication

  • 0x52f - Indicates that a referenced user name and authentication information are valid, but some user account restriction has prevented successful authentication. This can happen if you have a Microsoft Entra Conditional Access policy configured. To mitigate the issue, you must exclude the Azure SQL Managed Instance Service Principal (named <instance name> principal) application in the Conditional Access rules.

Investigate message flow failures

Use Wireshark, or the network traffic analyzer of your choice, to monitor traffic between the client and on-premises Kerberos Key Distribution Center (KDC).

When using Wireshark the following is expected:

  • AS-REQ: Client => on-premises KDC => returns on-premises TGT.
  • TGS-REQ: Client => on-premises KDC => returns referral to kerberos.microsoftonline.com.

Connection pooling

When connection pooling is enabled, the driver manages SQL connections by keeping them open in a pool for reuse, rather than closing them. This can lead to a scenario where a connection is reused after a security cache invalidation, causing the Kerberos ticket to be revalidated. If the connection has been in the pool for more than five minutes, the ticket is treated as expired, resulting in a connection failure. To prevent this, set the connection lifetime to less than five minutes in the connection string. This change ensures that connections older than the specified lifetime are not reused from the pool.

Related content

Learn more about implementing Windows Authentication for Microsoft Entra principals on Azure SQL Managed Instance: