Windows Authentication for Entra ID for SQL MI

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