Azure SQL database AAD user management

Question

Post reply

Azure SQL database AAD user management

tommiwan

Mr or Mrs. 500

Points: 593
More actions
March 30, 2023 at 4:48 pm

#4167208
I have an Azure SQL DB where I have set up the Azure active directory admin as an azure active directory security group. My AAD account is a member of that group. Now I am trying to authenticate to that database through SSMS as my AAD account (Azure active directory - Universal with MFA) and I get this message:
"The server principal ___ is not able to access the database "master" under the current security context. Cannot open user default database. Login failed."
If I change the active directory admin to my AAD account directly then I am able to connect to the DB without issue. The problem seems to be related to the AAD security group.
- This topic was modified 1 year, 7 months ago by tommiwan.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 2

Have you created a SQL Contained Database User?

When connecting as a contained database user, the connection string must always contain a parameter for the user database (you are not allowed to connect to master).

Hope this helps,

Andy

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 3

Thanks Andy! I do understand that a typical user would be created as a contained database user and would need to pass the database name in as part of their connection. My confusion is more around the idea of the active directory admin. As the admin I should be able to log in without providing a database name (connect to master). I can do that when I assign my specific user as the active directory admin. When I try to use an Azure active directory security group I cannot log in and connect to the master database. It appears that the group permissions are not working.

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 4

hmm, interesting! You have probably read this already, but to summarise from MS docs:

"Each server in Azure (which hosts SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the entire server. Create a second administrator account as an Azure AD account. This principal is created as a contained database user in the master database of the server. Administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user."

"Enable Azure Active Directory authentication and add an Azure Active Directory admin . One Azure Active Directory account can be configured as an administrator of the Azure SQL deployment with full administrative permissions. This account can be either an individual or security group account. An Azure Active Directory admin must be configured if you want to use Azure AD accounts to connect to SQL Database"

Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql#create-additional-logins-and-users-having-administrative-permissions

Your choice of a security group is supported; so I guess the configuration of this group is the root cause; can you validate the membership of the special roles in Azure SQL Database? Hopefully this will identify the error.

Andy

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 5

Thanks again Andy! I read through that documentation just to make sure I had everything set up properly. For whatever reason, today the AAD security group authentication is working. I configured the group on multiple Azure SQL DBs and I am able to authenticate with my AAD account and access the master database and our user database. I'm not exactly sure what changed to make it work. I appreciate your assistance!

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 6

Good to know the AAD group is working now 🙂

I read somewhere recently, that Azure SQL Database can be configured to only accept AAD authentication; all SQL Logins would be disallowed. I haven't explored this option yet...