Azure SQL Managed Instance authentication with Windows AD users

Question

I am looking to migrate from Azure SQL VMs to Azure SQL Managed Instance and I had a few questions related to integrating this with an on-premises Windows Active Directory domain.

We have some applications running on separate Windows VMs. These VMs are joined to a Windows AD domain.

Applications are setup to run as a service in Windows and we use the Log on as option for the service so that we can supply credentials for an AD account that the service will run as. The application will need to authenticate to a SQL database using the same AD account that the application is running as. We do not want to provide credentials in the SQL connection string so we currently use Trusted_Connection = true.

When migrating to SQL MI we do not have access to the underlying host so we can't join the host to our AD domain. Based on some documentation I've come across from Microsoft we can setup a trust-based flow so that we can still use Windows AD accounts to authenticate to SQL MI.

1. In this document it says that "you will turn your Microsoft Entra tenant into an independent Kerberos realm and create an incoming trust in the customer domain.". Is our Entra tenant going to be converted into a Kerberos realm, and if so is it reversible?
2. In this document there are different SQL authentication metadata modes. If I use Paired will I still need to sync the Windows AD user to Entra ID?
3. Is the benefit of the Paired mode that I can have independent users/groups in Entra ID, that are not found in Windows AD, and allow for both Entra ID/Windows AD users/groups to login to SQL MI?
4. In this document I will need to setup a GPO for a KDC proxy. Am I able to set this up for specific VMs or will it apply to all domain joined VMs?
5. When my application is running and attempts to authenticate to SQL MI how does the authentication flow work exactly? Does the application attempt to authenticate to the SQL MI resource and then since I'm using the Paired SQL Authentication metadata mode does SQL MI know to find the trust relationship that I setup previously? I'm a bit confused on how exactly that whole authentication flow works.
6. Do I need to run a CREATE LOGIN [DOMAIN\USER] FROM WINDOWS on SQL MI? Is this also what helps in the authentication flow to determine if the user is from Entra ID vs Windows AD in my previous question?

Answer 1

Hi @testacc9877,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand, you are migrating from Azure SQL VMs to Azure SQL Managed Instance (SQL MI) while integrating with an on-premises Windows Active Directory (AD).

Kerberos Realm: Your Microsoft Entra tenant will act as a Kerberos realm for cross-realm authentication with your on-premises AD. This setup is reversible.

Paired Mode: You need to sync Windows AD users to Entra ID. Paired mode allows both Entra ID and Windows AD users/groups to authenticate to SQL MI.

Benefits of Paired Mode: The primary benefit of Paired mode is the ability to manage independent users and groups in Entra ID that are not present in Windows AD. This setup allows both Entra ID and Windows AD users/groups to authenticate to SQL MI, providing a more flexible and integrated identity management solution.

GPO for KDC Proxy: You can apply the GPO for the KDC proxy to specific VMs.

Authentication Flow:

When your application attempts to authenticate to SQL MI, the following flow occurs:

• The application uses the AD account it is running as to request a Kerberos ticket.
• The Kerberos ticket is used to authenticate to SQL MI.
• SQL MI, using the Paired SQL Authentication metadata mode, recognizes the trust relationship set up previously and validates the Kerberos ticket against the Entra ID or Windows AD, depending on where the user is authenticated.
• This flow ensures secure and seamless authentication without needing to provide credentials in the connection string.

CREATE LOGIN Command: Yes, you need to run the CREATE LOGIN [DOMAIN\USER] FROM WINDOWS command on SQL MI. This step is crucial as it creates the login for the specified domain user in SQL MI, enabling the authentication flow to determine if the user is from Entra ID or Windows AD.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hello testacc9877,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to migrate Azure SQL VMs to Azure SQL Managed Instance and you have questions to affirm your authentication with Windows AD users' processes.

Migrating from Azure SQL VMs to Azure SQL Managed Instance (MI) while integrating with an on-premises Windows Active Directory (AD) domain involves several steps and considerations. Regarding each of your questions:

In this document it says that "you will turn your Microsoft Entra tenant into an independent Kerberos realm and create an incoming trust in the customer domain.". Is our Entra tenant going to be converted into a Kerberos realm, and if so is it reversible?

When you set up the trust-based flow, your Microsoft Entra tenant (formerly Azure AD) is indeed turned into an independent Kerberos realm. This setup allows for the creation of an incoming trust in your on-premises AD domain. This process is reversible, meaning you can remove the trust and revert the configuration if needed - https://www.youtube.com/watch?v=p1mKqxWcbUo2

In this document there are different SQL authentication metadata modes. If I use Paired will I still need to sync the Windows AD user to Entra ID?

If you use the Paired mode, you will still need to sync your Windows AD users to Microsoft Entra ID. The Paired mode allows for the coexistence of users and groups from both Windows AD and Entra ID, enabling authentication for both - https://www.youtube.com/watch?v=VM0eiOmE35I

Is the benefit of the Paired mode that I can have independent users/groups in Entra ID, that are not found in Windows AD, and allow for both Entra ID/Windows AD users/groups to login to SQL MI?

The primary benefit of the Paired mode is that it allows you to have independent users and groups in Entra ID that are not present in Windows AD. This flexibility means both Entra ID and Windows AD users/groups can authenticate to SQL MI - https://www.youtube.com/watch?v=VM0eiOmE35I

In this document I will need to setup a GPO for a KDC proxy. Am I able to set this up for specific VMs or will it apply to all domain joined VMs?

You can configure the Group Policy Object (GPO) for the Kerberos Key Distribution Center (KDC) proxy to apply to specific VMs. This setup allows you to target only the necessary domain-joined VMs rather than applying it universally - https://www.youtube.com/watch?v=VM0eiOmE35I

When my application is running and attempts to authenticate to SQL MI how does the authentication flow work exactly? Does the application attempt to authenticate to the SQL MI resource and then since I'm using the Paired SQL Authentication metadata mode does SQL MI know to find the trust relationship that I setup previously? I'm a bit confused on how exactly that whole authentication flow works.

When your application attempts to authenticate to SQL MI, the authentication flow works as follows:

The application uses the AD account it is running as to request a Kerberos ticket. The Kerberos ticket is then used to authenticate to SQL MI. Since you are using the Paired SQL Authentication metadata mode, SQL MI will recognize the trust relationship you previously set up and authenticate the user accordingly - https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/migrate-sql-server-users-to-instance-transact-sql-tsql-tutorial?view=azuresql

Do I need to run a CREATE LOGIN [DOMAIN\USER] FROM WINDOWS on SQL MI? Is this also what helps in the authentication flow to determine if the user is from Entra ID vs Windows AD in my previous question?

Yes, you will need to run CREATE LOGIN [DOMAIN\USER] FROM WINDOWS on SQL MI. This command helps SQL MI recognize and authenticate the user based on whether they are from Entra ID or Windows AD - https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/winauth-azuread-setup?view=azuresql

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.