Forum Discussion
Windows Authentication for Entra ID for SQL MI
Hi Team,
I recently come across a use case where we have to use Windows Authentication for Entra ID for SQL MI. My question is based on Microsoft documentation
There are two options.
Options 1 Modern interactive flow
Options 2 Incoming trust-based flow
Proceeding with Option 2 (Incoming trust-based flow) the authentication flow works some as the following
| Step | Action | From | To | Network Connection |
| 1 | Initiate Connection | Client (Windows Server 2016) | - | - |
| 2 | Request Kerberos TGT | Client | Domain Controller (Windows 2012) | On-premises network |
| 3 | Issue TGT | Domain Controller | Client | On-premises network |
| 4 | Request Service Ticket via Kerberos Proxy | Client | Microsoft Entra ID (via proxy) | ExpressRoute (Microsoft peering) |
| 5 | Issue Service Ticket | Microsoft Entra ID | Client | ExpressRoute (Microsoft peering) |
| 6 | Submit Service Ticket | Client | Azure SQL Managed Instance | ExpressRoute (private peering) |
| 7 | Validate Ticket and Exchange for Token | Azure SQL Managed Instance | Microsoft Entra ID | Azure internal network |
| 8 | Authenticate User and Grant Access | Azure SQL Managed Instance | Client | ExpressRoute (private peering) |
If above is correct. Can anyone confirm we have to synchronize service accounts and users to Entra IS that are used by applications? Does the client (running application ot SQL management studio) require access to Entra ID or it will be requested by on-premises AD on behalf of application server
Many Thanks !
1 Reply
Hi Zahid_Yaqub,
Q: We have to synchronize service accounts and users to Entra IS that are used by applications?
A: Yes, you do need to synchronise the on-premise account from Active Directory to Entra ID.
Q: Does the client (running application to SQL management studio) require access to Entra ID...?
A: I'm unclear on what you mean by "client". Do you mean the user launching SSMS? If so, then:
- If the user wishes to log onto SQL MI using SSO based on Windows Authentication (as shown below) - or Entra ID Integrated, then yes, their account needs to be synchronised to Entra ID.
- This remains true for all on-premise accounts looking to access SQL MI - service accounts, application accounts, etc.
If you are looking to migrate databases from on-premise SQL Server to Azure SQL MI, you will need to plan for recreating/altering the existing on-premise identities to their Entra ID synchronised representations.
The reason for this is that it's not actually your Active Directory account logging onto SQL MI. Here's a loose description of what happens:
- You are logged onto your domain- or hybrid-joined computer with your Active Directory account;
- You launch SSMS, choose Windows Authentication and connect to the Azure SQL MI;
- Under the hood, Windows requests a Kerberos ticket from Entra ID, where that ticket is actually aligned to your Entra ID account (which is why your account has to be synchronised to Entra ID);
- That ticket is presented to Azure SQL MI.
Again, as I mentioned, the process is the same for any Active Directory account accessing SQL MI.
This is why:
- The account must be synchronised from Active Directory to Entra ID; and
- The synchronised Entra ID account must have access to the SQL MI instance (as a login, user or most likely both - depending on whether or not the database is contained).
Cheers,
Lain
- If the user wishes to log onto SQL MI using SSO based on Windows Authentication (as shown below) - or Entra ID Integrated, then yes, their account needs to be synchronised to Entra ID.
