Azure SQL Grant Access for AD User in SSMS [closed]

1
votes

I set an azure ad admin (global admin) for our azure sql server, now i tried to grant a ad user access to the sql database. I followed the instructions in this video https://www.youtube.com/watch?v=pEPyPsGEevw, because i didn't found a better explenation. As described in the video, i created a query in the target database context:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER

that raises this exception:

Msg 33134, Level 16, State 1, Line 1 Principal '[email protected]' could not be resolved. Error message: 'AADSTS7000219: 'client_assertion' or > 'client_secret' is required for the 'urn:ietf:params:oauth:grant-type:jwt-bearer' grant type. Trace ID: 95da6617-732c-42bf-b74b-40a4fdffe000 Correlation ID: cf4b894f-f945-492c-a77f-e2976ac72eab Timestamp: 2020-06-09 17:58:48Z

When i try the same in master db, it works.

What goes wrong? Or could you provide me a source of a step by step guide? I didn't found a real good description...

1
sql-serverazure-active-directoryazure-sql-databasessms
Which region are you creating the database in? Seems to be a reproducible issue at the moment, I'm getting the same error in Australia East. - Nick McCoy
Experiencing the same issue in West Europe on one database, while creating users from external provider is successful on other databases on the same Azure SQL instance. I've filed a support case with Azure support. - André Sørhus
Seeing the same issue in UK South - mfa
Scaling up to S1 seemed to resolve the issue for me. - mfa

1 Answers

5
votes

Experiencing exactly the same issue in the UK South region.

This isn't a fix, but it seems to resolve the issue for me (i.e. it's a work around). To fix:

  • scale the database to at least S3,
  • apply the changes
  • then scale it back to the level that you require.

The changes should be applied.

Our database was at S0 when we tried to create the AAD users/groups initially and we received exactly the same error as pointed out above.