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

1
votes

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 [[email protected]] FROM EXTERNAL PROVIDER;

    sp_addrolemember db_datareader, [[email protected]];

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?

1
azureazure-sql-databaseazure-active-directory
What "Reader role" under IAM did you assign? (To my knowledge, there is not one that grants access to your database.) Have you had AAD auth working on your server at all? Can you give me the error message verbatim in an edit? – Dan Rediske
@drediske - There is a role named 'Reader' under IAM which lets you view everything, but not make any changes. AAD Auth is working fine for the user I have assigned as the AAD admin of the server. I have added the snip of the error message above. – M_coder
The Anonymous Logon error occurs when you haven't specified the database you want to connect to. Simply select "options" on the SSMS login screen and type in the database name you want to connect to. This is because your user is a contained user on the database it was created in. It does not exist on Master. – Greg Grater

1 Answers

4
votes

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