Trouble granting access to AD Groups to connect to an Azure SQL Server/Database

Question

So I've created an Azure SQL server and have a couple of databases under that server. My main goal is to allow access to a couple other AD (Entra now?) groups to a database under the server. I don't really care if they have access to all databases under the server, but they really only need one.

I've created users for these groups under master and under the specific database that they need.

CREATE USER "ADGroup" FROM EXTERNAL PROVIDER;

Also provided them with read access under the database, by giving them the db_reader role.

I got word that when they tried to connect, they couldn't. So I tried

Grant connect to ADGroup;

under the master database. I was told that I didn't have GRANT permissions and was a little confused, but come to find out, server level permissions aren't a thing in Azure SQL apparently? (First paragraph on this link )

So I've granted connect under the specific database, and that executed successfully. I haven't heard back from them yet, but are they going to be able to connect? Wouldn't the server NEED to know that they're allowed to connect in the first place? If this won't work, how should I go about this?

Answer 1

Yes, you can create an Azure AD group using Azure Portal and then add members to that group. You are correct about creating a user for the group on the specific database you want the group to have access.

CREATE USER [SQLGroup] FROM EXTERNAL PROVIDER

For the above T-SQL to execute successfully you need to be connected to the database as Azure Active Directory Admin User.

Now, make sure you assigned read-only permissions to the group as following:

EXEC sp_addrolemember 'db_datareader', 'SQLGroup'

After that, any member of the group should be able to connect and query the database without any issues. You can test that using Azure Data Studio or SQL Server Management Studio.

For more information, please read this documentation.