Lesson Learned #23: How to add AAD Security Groups to Azure SQL Database?


In these days, we are receiving multiples cases where our customer needs to add Azure SQL Database groups from Azure Active Directory.

This is an example to how to archive it.

  • Using the Portal in Azure:
    • I created a user called SQLMember.
    • I created a group called SQLGroup, adding the member SQLMember.

 

 

  • Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database

 

 

  • Finally, I disconnected from SQL Server Management Studio and I connect again using the user SQLMember@mydomain.com to the database and I was able to connect without issue, applying the read-only permissions for the database
Comments (3)

  1. One thing that it not documented and well worth explaining is that there somehow is a connection between the Azure SQL AD administrators and that user’s home Azure AD, and which users/groups you can add FROM EXTERNAL PROVIDER.

    Recently I was administrating another azure tenant, where I have been granted access using Azure B2B. I set myself as Azure SQL AD Administrator using the portal. My email is something like simon@mytenant.com, and the Azure Subscription that I’m working in have users with emails like something@theirtenant.com. With this setup, I was able to log into SQL Management Studio using my Azure AD credentials, but when I tried adding users from theirtenant.com it wouldn’t find them. I was able to add users when I changed the admin to a user from the theirtenant.com Azure AD, and obviously I had to use an @theirtenant.com account to login and do so.

  2. AK says:

    It looks like your tip shows creation of a SQL user form Azure AD group only. I can create users that were synced up from on-prem AD this way; how do I create users from synced up on-prem AD groups?

    1. Just put the group name in the CREATE USER command and you should be able to add it.

Skip to main content