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.
- Using SQL SERVER Management Studio connected to the User Database using the Azure Active Directory Admin User and clicking New Query.
- I create as user group of this database - CREATE USER [SQLGroup] FROM EXTERNAL PROVIDER - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
- Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database
- I executed the TSQL: EXEC sp_addrolemember 'db_datareader', 'SQLGroup' to give the permissions to this group just of read-only. - https://msdn.microsoft.com/en-us/library/ms187750.aspx
- 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
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.
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?
Just put the group name in the CREATE USER command and you should be able to add it.