Adding a new user with specific permissions to an Azure Sql Database using Active Directory Integrated Authentication

Question

I have an Azure SQL Server with two databases for which I'm trying to use Azure Active Directory Integrated Authentication. One of these databases is critical and most of the users need to be granted only 'read' access for this database.

To add a new user with a 'Reader Role', I did the following:

• Added the user with Reader role under Access Control(IAM) from the Azure portal. The user wasn't able to connect after this step.

• Then I tried adding the user using the following commands:

  CREATE USER [name@domain.com] FROM EXTERNAL PROVIDER;

  sp_addrolemember db_datareader, [name@domain.com];

The user is still not able to connect to the server using AAD Integrated Authentication. In both the cases I get an Anonymous Logon error.

Click to see the snip of the error message

Am I missing something? If not, is there any other way I can add users with specific permissions to the database?

Answer 1

Sorry for the delay, M.

Yeah, you've confused two different levels of access control; the IAM controls that you described (Reader role assignment) allows a user to view (read) the settings in the Azure Portal. Instead, I suspect you want a user (AAD authenticated) to be able to only read the data on the server. That's done via T-SQL and has nothing to do with the Access Control defined by the portal.

Now, you're attempting to create an external user (AAD access) on the database level, and you want them to have read permissions- make sure you've covered all the steps outlined here.

At a glance, the following may be necessary steps:

1.) Ensure that you've assigned an AAD admin for the SQL Server.

2.) Ensure that you're connecting to the database you want to create the users on, not the master db as you ordinarily would on a non-azure SQL Server instance. Create the user via T-SQL using the following: CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

3.) Grant the user db_datareader permissions on that database:

ALTER ROLE db_datareader ADD MEMBER <Azure_AD_principal_name>
GO