Azure AD Admin cannot access the Azure SQL database over VPN

Question

I have setup an Azure SQL database and secured behind Azure VPN. Azure VPN is already configured to use Azure AD and works fine. When connecting to the database, I can use a local IP as I have to connect to the VPN before connecting to the database. Currently access to the database uses SQL authentication and access to the database works without any issue.

I enabled Azure AD authentication recently by adding an Entra Admin through the portal. However, I was not able to connect to the database using Azure AD credentials even after providing the Entra Admin owner level access to the resource.

After checking most documentation and several suggestions online, including Chat GPT, I have come to a stand still on this issue. The current setup seems well aligned with all Microsoft documentation and expert suggestions, however, the only thing I could suspect is that when the Entra Admin is added via the portal or through Azure CLI, the entry in the master table shows the Admin as EXTERNAL GROUP and not EXTERNAL USER.

The following SQL statement returns EXTERNAL GROUP as type_desc instead of EXTERNAL USER when queried using SQL authentication (as I cannot login using AD authentication):

SELECT * FROM sys.database_principals 
WHERE type_desc = 'EXTERNAL_USER' OR type_desc = 'EXTERNAL_GROUP';

I would like to note that I am adding a user as Entra Admin for the database and not a security group and have checked this in Entra ID and confirmed.

Any help / guidance on how I can setup the Entra Admin to be added as EXTERNAL USER to the master table and access the database via Azure AD authentication is greatly appreciated.

Answer 1

Hi @Mano,

Welcome to Microsoft Q&A forum.

As I understand it, you've done a thorough job setting up your Azure SQL database and configuring Azure AD authentication. The issue you're encountering, with the Entra Admin being recognized as an EXTERNAL GROUP instead of an EXTERNAL USER, can be tricky.

Here are a few steps and considerations that might help resolve this:

1.Ensure that the user you're adding as the Entra Admin is indeed a user and not part of a group. Double-check the user details in Azure AD.

2.Sometimes, using the Azure portal might not reflect changes immediately. Try using Azure CLI or PowerShell to add the Azure AD user as an admin.

az sql server ad-admin create --display-name
                              --object-id
                              --resource-group
                              --server

3.Ensure that Azure AD authentication is properly configured on your SQL server. You can check this in the Azure portal under the SQL server settings.

4.Sometimes, there might be a delay in syncing changes from Azure AD to the SQL database. Give it some time and try again.

5.Ensure that the Azure AD user has the necessary permissions on the database. You can run the following SQL command to grant the necessary permissions:

CREATE USER [AzureADUser] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [AzureADUser];

6.Check the SQL server logs for any errors or warnings related to Azure AD authentication. This might give you more insight into what’s going wrong.

For more information, please refer the documents:

https://learn.microsoft.com/en-us/cli/azure/sql/server/ad-admin?view=azure-cli-latest#az-sql-server-ad-admin-create

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver16

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.