Azure SQL DB Query Editor Microsoft Entra Authentication showing a different admin user other than the created one

Sri C 0

I have created a Azure SQLDB and under the SQL Server settings, Microsoft Entra ID, I have a created a user (say myazureentra@myazureoutlook.onmicrosoft.com) and set it as 'admin'. In the SQL Server overview, I could see this user is reflecting as Microsoft Entra admin. So far so good.

In Microsoft Azure Portal home page, When I click on Microsft Entra ID, I could see 2 users,

(1) a user whose email id is used to create Azure subscription (say myazure@myazureoutlook.onmicrosoft.com)

(2) add on user created for SQL server authentication which was created in above step (say myazureentra@myazureoutlook.onmicrosoft.com)

Now when I try to use 'Query Editor', it gives me two options to login one with SQL server authentication and other with Microsoft Entra authentication.

Under Microsoft Entra authentication, I could only see user (1), but not user (2), which I set as admin.

Please suggest the reason for this and how can I use the Entra admin user I created to login.

Am I missing some thing here?

Thanks

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-07-30T00:33:53.2233333+00:00

@Sri C Thank you for reaching out.

When creating the SQL DB using the portal functionality which option did you select?

Regards,

Oury
Sri C 0 Reputation points

2024-07-30T08:06:35.4+00:00

Hi @Oury Ba-MSFT

I have selected "use Both SQL and Microsoft Entra authentication option"

Thanks
Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-07-31T00:48:09.9766667+00:00
@Sri C

You selected to use Both "SQL and Microsoft Entra authentication option" That is why it is showing in the query editor to connect using SQL or Microsoft Entra authentication.

Connect via the query editor

There are two authentication options for query editor: SQL authentication or authentication with Microsoft Entra ID (formerly Azure Active Directory).

Authentication to Azure SQL Database

For examples, see Quickstart: Use the Azure portal query editor (preview) to query Azure SQL Database.

To use SQL Authentication to connect to an Azure SQL database via the query editor, you must have a login in the logical server's master database or a contained SQL user in the desired user database. For more information, see Logins.

Enter your username and password, then select OK.

To use Microsoft Entra authentication to connect to an Azure SQL database via the query editor, your database must be configured with Microsoft Entra authentication, and you must have a Microsoft Entra user created in the database.

Select **Continue as <user@domain>**.

https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-portal?view=azuresql#connect-to-the-query-editor
Sri C 0 Reputation points

2024-07-31T10:50:38.8833333+00:00

Hi @Oury Ba-MSFT

I think you are providing clarification on why the Query editor is showing two ways to login to SQLDB

But my question is (as stated in my first post), under the 'Microsoft Entra Authentication', why I am seeing a user (email) which is used to create my Azure subscription to login, rather than another user which I created explicitly and assigned as 'admin' using 'set admin'.

Is there any way I can login to 'Microsoft Entra Authentication' with the admin I created for this purpose (You may review my original post as I stated with some example)?

Thanks
Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-08-01T18:49:21.59+00:00

@Sri C I am reaching out to check if you have any additional queries with regards to the above.

Regards,

Oury

1 answer

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-07-31T22:53:04.4966667+00:00

@Sri C I have reviewed your comment above. I totally understand what you are saying above. Let me explain into more details.

I am also seeing the same behavior and the reason why you are seeing another user (user who created the subscription) under Microsoft Entra Id when trying to access db from query editor.

The Microsoft entra admin you are seeing in the query editor is the actual user who logged in to the azure portal.

If you are looking to login with the user Microsoft Entra ID admin for sql db you created, you will need to sign off and switch to that user. You can do that by hovering your mouse on the top right corner on the Azure portal web page.

Please let me know of you need more clarification.

This is a helpful video. Please do check it out https://www.youtube.com/watch?v=IwpmLUpvTBA

You can test it using SSMS to connect.

Regards,

Oury
Please sign in to rate this answer.
Sri C 0 Reputation points

2024-08-02T11:31:38.76+00:00

Hi

When I switch to the user which was set as Entra admin user, I am not able to see my SQL Server / SQL DB or any resources and azure is suggesting to create a new subscription. So I think, I would not be able to login to Query editor with a user id other than the subscription owner.

On the SSMS, as I am using Ubuntu, I could login to Azure data studio using the Entra admin user I set.

Thanks
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure SQL DB Query Editor Microsoft Entra Authentication showing a different admin user other than the created one

1 answer

Your answer