0

I am looking for a way to sign in into Azure SQL DB with my AAD credentials, but I only want to have read-only access.

First try

  • Opened Azure SQL Server Access Control (IAM) tab.
  • Added myself a Managed Applications Reader role.
  • This doesn't allow me to connecto to the DB with the following error.
Cannot connect to abc.database.windows.net.

------------------------------
ADDITIONAL INFORMATION:

One or more errors occurred. (mscorlib)
------------------------------

AADSTS900021: Requested tenant identifier '00000000-0000-0000-0000-000000000000' is not valid.
Tenant identifiers may not be an empty GUID.
Trace ID: xxx
Correlation ID: yyy
Timestamp: 2020-04-09 08:57:20Z (System.Data)

Second try

  • Opened Azure SQL Server Active Directory Admin.
  • Added myself as an admin.
  • This allows me to connect now with AAD credentials, but I have all the permissions I am scared to have on a prod DB now.

Is this achievable?

eddyP23
  • 6,420
  • 7
  • 49
  • 87

1 Answers1

0

Yes, but you'll need to assign another user as SQL AAD admin. Login as that user, and run:

CREATE USER [your.user@domain.com] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader','your.user@domain.com';

This will create a contained user in the database for your AAD user, and assign the data reader role to them. You can also grant read access to single tables etc. with standard T-SQL.

juunas
  • 54,244
  • 13
  • 113
  • 149
  • I assume I don't need to create an AAD admin, instead I should be able to use an ordinary admin user for these queries? – eddyP23 Apr 09 '20 at 10:36
  • At least in the past I found they did not work with a regular SQL admin. If it works then of course you can do that :) – juunas Apr 09 '20 at 10:37
  • 1
    You are right, I am getting `Principal 'abc@xyz.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.` – eddyP23 Apr 09 '20 at 10:44
  • I'm guessing they query the AAD tenant behind the scenes on your behalf. This means you need to login through AAD, and with a user that has access to the users/groups/apps being added. – juunas Apr 09 '20 at 10:50
  • Yeah, makes sense, thanks. I am getting `Principal 'abc@xyz.com' could not be found or this principal type is not supported.` Any ideas why? – eddyP23 Apr 09 '20 at 10:51