Microsoft Entra authentication for SQL Server on Azure VMs

Question

Microsoft Entra authentication for SQL Server on Azure VMs

Cristopher Aguilera 131

Good morning,

I have a SQL server on Azure virtual machine.

SQL version: SQL Server 2022

Virtual machine OS: Windows (Windows Server 2022 Datacenter)

I have the VM linked to my azure domain, and have user accounts that can login to the VM with their Microsoft Entra ID credentials. I now want to be able to use the same Microsoft Entra ID account to login to the SQL instance when connecting via SQL management studio.

I follow through all the steps in the documentation https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/configure-azure-ad-authentication-for-sql-vm?view=azuresql&tabs=azure-portal, however I still can't connect to the SQL instance.

Whenever I try to connect using 'Azure Active Directory - Universal with MFA' i get the attached error.

The VM is not opened to public access, could this be what's causing the issue?

Thanks,

Cris

Cristopher Aguilera 131 Reputation points

2023-12-21T10:11:38.5066667+00:00

UPDATE

I managed to login to the SQL instance using my azure ad domain login, I needed to add the user to the logins in SQL!

For example my user login is: ******@domainname.onmicrosoft.com

Group name: DOMAINNAME\MyTeam

I was expecting it to work before as I had added a azure ad domain group to the SQL logins, and I ( ******@domainname.onmicrosoft.com ) am part of the group.

My question now is, does 'Azure Active Directory - Universal with MFA' only works against users and not groups even if the user is part of the group?

I was hoping to add all our developers to the domain group and SQL to automatically pick them up rather than having to add every user individually on SQL.

Thanks,

Cris
JamesTran-MSFT 37,226 Reputation points Microsoft Employee Moderator

2023-12-21T20:54:42.0966667+00:00
@Cristopher Aguilera

Thank you for your post!

I'm not familiar with what adding the user to the logins in SQL (⁠SQL Logins) is - but from what I found, it seems like you're adding the user to the SQL server DB in order to authenticate. If so, it doesn't look like you're authenticating with MS Entra ID, since the user is being manually added to your SQL DB.

When in it comes to MFA within MS Entra ID (Azure AD), this should work against both users and groups. However, from the error message that you're receiving it seems like you might have an outbound communication issue:

Error Message:

The VM is not opened to public access.

For more info - ⁠Enable outbound communication.

When you create a Microsoft Entra login for SQL Server and when a user connects using that login, SQL Server uses a managed identity to query Microsoft Graph. When you enable Microsoft Entra authentication for your SQL Server on Azure VM, you need to provide a managed identity that SQL Server can use to communicate with Microsoft Entra ID. This managed identity needs to have permissions to query Microsoft Graph.

Additional Links:

Enable Microsoft Entra authentication for SQL Server on Azure VMs

Enable outbound communication

Create SQL Login and SQL User on your Azure SQL DB

I hope this helps!

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.
Cristopher Aguilera 131 Reputation points

2023-12-22T11:38:47.61+00:00

Hi James,

Thanks for the detailed response, it is much appreciated.

However, I'm using Microsoft Entra ID to login to SQL server (or at least i think it is), please see attachments. When login via SQL Server management studio I choose the Azure 'Active Directory - Universal MFA' option, after entering my MS Entra ID domain login the multifactor auth window pops up and I'm able to login and start browsing the DBs/Server objects. If I was using normal SQL logins the email ******@mydomain.onmicrosoft.com would no be recognised throwing an error.

The screenshot below shows the login properties. As you can see if this was a normal sql login, the 'SQL Server Authentication' would be selected instead of 'Windows authentication'

I'm confused as to why it works when adding the domain login manually, but does not recognise the login via the group, in the pic below ******@mydomain.onmicrosoft.com is part of the domainname\DW Team group. When i remove my individual login and leave the group login only - my login stops working, should my login not continue to group as ******@mydomain.onmicrosoft.com is part of domainname\DW Team which is added as a login on SQL security logins

The network security group is allowing outbound communication on ports 80 and 443 already.

Could you explain what 'The VM is not opened to public access'? Does this mean the VM has to have a public IP assigned to it / facing the Internet? We don't want to expose our VMs to the internet for security safety reasons.

Our VMs sit on a private virtual network with a site to site VPN gateway configuration pointing to our on-premises VPN which allows our team to RPD and connect to the VMs/SQL instances natively from on-premises. There is no AD sync from on-prem to Azure tenant btw.

Thanks for your help James,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:06:43.1+00:00

@Cristopher Aguilera Thank you for providing your feedback. Could you please share the error message you are facing so we can further assist you.

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:47:21.51+00:00

@Cristopher Aguilera

You mentioned above that The VM is not opened to public access.

When you choose Private for the SQL connectivity type in the portal, Azure configures most of the settings identical to Public. The one difference is that there is no network security group rule to allow outside traffic on the SQL Server port (default 1433).

Important

The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql?view=azuresql#connect-to-sql-server-within-a-virtual-network

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:50:09.23+00:00

@Cristopher Aguilera does it work if you try to connect using SQL Server authentication.?

Regards,

Oury
Cristopher Aguilera 131 Reputation points

2023-12-29T11:38:32.97+00:00

Hi Oury,

Thanks for your comments, I'll respond to your questions in chronological order.

This is the error message:

I have added the outbound rule on port 1433

TCP/IP on sql server manager was already enabled:

Adding the outbound rule did not fix the issue.

Please read comments I posted on this thread on Dec 22, 2023, 11:38 AM for more detailed info. In summary, I can connect to the SQL server instance via management studio from on-premises using 'Azure Active Directory - Universal with MFA' IF the Microsoft entra domain user is added manually to the SQL login (please see screenshots from comments on Dec 22, 2023, 11:38 AM) - my user is part of a microsoft entra domain group which I am part of , if i remove my individual login and leave the group only i can't login to the SQL instance and returns the attached error.

Thanks,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-01-04T18:01:05.0333333+00:00

@Cristopher Aguilera

Sorry for the delay in response to your previous post.

Could you please confirm if your groups are MS Entra groups or Windows groups.

If you are using Windows groups, then you would need to use the Integrated auth mode for connecting to SQL, and your on-prem AD would need to be sync'd to your AAD tenant.
Cristopher Aguilera 131 Reputation points

2024-01-05T08:45:31.4233333+00:00

Hi @Oury Ba-MSFT

Happy new year!

It is a MS Entra group.

Thanks,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-01-31T00:22:47.8066667+00:00

Cristopher Aguilera Sorry for the delay in response to the above ask. I would suggest opening a support case so we can further troubleshoot this. Please do share the support case number.
Regards, Oury

Your answer

Cristopher Aguilera 131 Reputation points

2023-12-21T10:11:38.5066667+00:00

UPDATE

I managed to login to the SQL instance using my azure ad domain login, I needed to add the user to the logins in SQL!

For example my user login is: ******@domainname.onmicrosoft.com

Group name: DOMAINNAME\MyTeam

I was expecting it to work before as I had added a azure ad domain group to the SQL logins, and I ( ******@domainname.onmicrosoft.com ) am part of the group.

My question now is, does 'Azure Active Directory - Universal with MFA' only works against users and not groups even if the user is part of the group?

I was hoping to add all our developers to the domain group and SQL to automatically pick them up rather than having to add every user individually on SQL.

Thanks,

Cris
JamesTran-MSFT 37,226 Reputation points Microsoft Employee Moderator

2023-12-21T20:54:42.0966667+00:00

@Cristopher Aguilera

Thank you for your post!

I'm not familiar with what adding the user to the logins in SQL (⁠SQL Logins) is - but from what I found, it seems like you're adding the user to the SQL server DB in order to authenticate. If so, it doesn't look like you're authenticating with MS Entra ID, since the user is being manually added to your SQL DB.

When in it comes to MFA within MS Entra ID (Azure AD), this should work against both users and groups. However, from the error message that you're receiving it seems like you might have an outbound communication issue:

Error Message:

The VM is not opened to public access.

For more info - ⁠Enable outbound communication.

When you create a Microsoft Entra login for SQL Server and when a user connects using that login, SQL Server uses a managed identity to query Microsoft Graph. When you enable Microsoft Entra authentication for your SQL Server on Azure VM, you need to provide a managed identity that SQL Server can use to communicate with Microsoft Entra ID. This managed identity needs to have permissions to query Microsoft Graph.

Additional Links:

Enable Microsoft Entra authentication for SQL Server on Azure VMs

Enable outbound communication

Create SQL Login and SQL User on your Azure SQL DB

I hope this helps!

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.
Cristopher Aguilera 131 Reputation points

2023-12-22T11:38:47.61+00:00

Hi James,

Thanks for the detailed response, it is much appreciated.

However, I'm using Microsoft Entra ID to login to SQL server (or at least i think it is), please see attachments. When login via SQL Server management studio I choose the Azure 'Active Directory - Universal MFA' option, after entering my MS Entra ID domain login the multifactor auth window pops up and I'm able to login and start browsing the DBs/Server objects. If I was using normal SQL logins the email ******@mydomain.onmicrosoft.com would no be recognised throwing an error.

The screenshot below shows the login properties. As you can see if this was a normal sql login, the 'SQL Server Authentication' would be selected instead of 'Windows authentication'

I'm confused as to why it works when adding the domain login manually, but does not recognise the login via the group, in the pic below ******@mydomain.onmicrosoft.com is part of the domainname\DW Team group. When i remove my individual login and leave the group login only - my login stops working, should my login not continue to group as ******@mydomain.onmicrosoft.com is part of domainname\DW Team which is added as a login on SQL security logins

The network security group is allowing outbound communication on ports 80 and 443 already.

Could you explain what 'The VM is not opened to public access'? Does this mean the VM has to have a public IP assigned to it / facing the Internet? We don't want to expose our VMs to the internet for security safety reasons.

Our VMs sit on a private virtual network with a site to site VPN gateway configuration pointing to our on-premises VPN which allows our team to RPD and connect to the VMs/SQL instances natively from on-premises. There is no AD sync from on-prem to Azure tenant btw.

Thanks for your help James,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:06:43.1+00:00

@Cristopher Aguilera Thank you for providing your feedback. Could you please share the error message you are facing so we can further assist you.

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:47:21.51+00:00

@Cristopher Aguilera

You mentioned above that The VM is not opened to public access.

When you choose Private for the SQL connectivity type in the portal, Azure configures most of the settings identical to Public. The one difference is that there is no network security group rule to allow outside traffic on the SQL Server port (default 1433).

Important

The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql?view=azuresql#connect-to-sql-server-within-a-virtual-network

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-12-27T23:50:09.23+00:00

@Cristopher Aguilera does it work if you try to connect using SQL Server authentication.?

Regards,

Oury
Cristopher Aguilera 131 Reputation points

2023-12-29T11:38:32.97+00:00

Hi Oury,

Thanks for your comments, I'll respond to your questions in chronological order.

This is the error message:

I have added the outbound rule on port 1433

TCP/IP on sql server manager was already enabled:

Adding the outbound rule did not fix the issue.

Please read comments I posted on this thread on Dec 22, 2023, 11:38 AM for more detailed info. In summary, I can connect to the SQL server instance via management studio from on-premises using 'Azure Active Directory - Universal with MFA' IF the Microsoft entra domain user is added manually to the SQL login (please see screenshots from comments on Dec 22, 2023, 11:38 AM) - my user is part of a microsoft entra domain group which I am part of , if i remove my individual login and leave the group only i can't login to the SQL instance and returns the attached error.

Thanks,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-01-04T18:01:05.0333333+00:00

@Cristopher Aguilera

Sorry for the delay in response to your previous post.

Could you please confirm if your groups are MS Entra groups or Windows groups.

If you are using Windows groups, then you would need to use the Integrated auth mode for connecting to SQL, and your on-prem AD would need to be sync'd to your AAD tenant.
Cristopher Aguilera 131 Reputation points

2024-01-05T08:45:31.4233333+00:00

Hi @Oury Ba-MSFT

Happy new year!

It is a MS Entra group.

Thanks,

Cris
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-01-31T00:22:47.8066667+00:00

Cristopher Aguilera Sorry for the delay in response to the above ask. I would suggest opening a support case so we can further troubleshoot this. Please do share the support case number.
Regards, Oury

Share via

Microsoft Entra authentication for SQL Server on Azure VMs

Your answer