How to add Azure AD Groups in Azure SQL Server

Question

Can someone tell me how can I add Azure Active Directory groups into the azure sql server, I am using server manager tool to do this but cant find any way to figure this out, I can add simple Azure Active Directory user though..What about groups?

Answer 1

I will assume that you are wanting to provide access for end-users to connect, not Database Administrators. For my example below, let's say that the end-users are in a group called "AZ-Users", and that your Database Administrators (including you) are in a group called "AZ-DBAs".

For Azure SQL Databases, there are key things that must be in place to get this to work:

1. There must be an "Active Directory admin" configured for your server. This can be any AAD user or an AAD group. You can check if this has been set or not by going to the Azure portal page for your server. Be careful that you are looking at the Overview page for the server, not the individual database, they are not the same thing. Detailed instructions here. In our example, we would configure this to be the AAD group called "AZ-DBAs".

2. When you are ready to create the AAD login for "AZ-Users" on your Azure SQL Database, you must yourself be logged in using AAD... meaning a member of the "AZ-DBAs" group from my example above. You can use SSMS or any other similar tool for executing TSQL commands. Note that if you try a SQL auth connection instead, it won't work for step 4 below - you'll get this error:

Msg 33159, Level 16, State 1, Line 1
Principal 'AZ-Users' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

3. Change the context to the database you want to provide access to for your end users.

4. Execute this statement:

   CREATE USER [AZ-Users] FROM EXTERNAL PROVIDER
   

Note that this will create a "contained database user", as detailed here. That's it. This process works for AAD groups and AAD users.

5. You will probably also want to grant some level of permissions as well, such as:

   EXEC sp_addrolemember 'db_datareader', 'AZ-Users'
   

Answer 2

All you need to know about how to configure and manage Azure Active Directory Authentication you can find it in this article.

Then to connect to SQL Azure using Azure Active Directory authentication please read here.