Windows Authentication in Azure SQL Managed Instance
Windows Authentication is one of the features of the Native SQL server which is a secure way of connecting to the SQL server. Windows authentication is one of the authentication methods where the legacy applications rely on, some of the features like profiler which depends on the windows authentication, all these issues were one of the reasons where the SQL server is not suitable for the migration to Azure SQL Managed instance.
Azure with its latest releases, started supporting the windows authentication in azure SQL managed Instance which went into GA recently. Azure SQL MI started supporting the windows authentication which removed the barrier for the legacy applications and the servers that are dependent on the windows authentication.
Azure SQL Managed Instance supports the windows authentication for the native windows users, which behind the scenes relies on the trust that established between onprem AD and the Entra ID. Windows authentication in the Azure SQL Managed Instance mainly depends on the Kerberos authentication where the exchange of the token happens between Azure Entra ID and the Onprem ID. All we need to make sure is that the users are synced to Entra ID from the onprem AD.
Windows authentication in Azure SQL Managed Instance depends on few parameters
Initially all the users have to be synchronized from the Onprem AD to Entra ID. Depending on the source machine from the connection is established to the Azure SQL Managed instance, we need to setup the configuration for the windows authentication.
Windows authentication supports two distinct authentication flows depending on the source operating system: "the modern interactive flow and the incoming trust-based flow".
The following diagram illustrates the flow chart to choose the configuration based on the source operating system.
Modern interactive flow
This method is used for the clients running Windows 10 21H1 and higher that are Microsoft Entra joined or Microsoft Entra hybrid joined.
In the modern interactive flow, users can access Azure SQL Managed Instance without requiring a line of sight to Domain Controllers. There is no need for a trust object to be created in the Onprem AD. To enable the modern interactive flow for the clients running Windows 10 21H1 and higher, we need to a set group policy for Kerberos authentication tickets (TGT) to be used during login.
The following diagram shows how the authentication happens using the modern interactive flow
Recommended by LinkedIn
The following tables gives the prerequisites for the configuration of the modern interactive flow
Incoming trust-based flow
This method is used for the clients running Windows 10 or Windows Server 2012 and higher. This method of authentication requires that clients be joined to AD and have a line of sight to AD
In the incoming trust-based flow, a trust object is created in the onprem AD and is registered in Microsoft Entra ID. To enable the incoming trust-based flow, we need to set up an incoming trust with Microsoft Entra ID and set up Kerberos Proxy via group policy.
The following tables gives the prerequisites for the configuration of the Incoming trust-based flow
Once the above configurations are in place, we need to configure the Azure SQL Managed Instance to support the windows authentication
For the Azure SQL managed instance to support the windows authentication we need to first enable the system assigned service principle to the Azure SQL Managed instance and provide the azure SQL Managed Instance principle the permissions to read the Entra ID
Once the permissions are set, we need to assign admin consent to the Azure SQL Managed instance service principle on the Entra ID
Once everything is in place, user can connect to the Azure SQL Managed Instance using windows authentication.