Add Active Directory User For Azure-SQL-DB

active-directoryazureazure-sql-data-warehouseazure-sql-databaseSecurity

I have an active directory user LDomain\LUser and I want that user to be able to connect to Azure-Sql-DB. The syntax MS uses is throwing an error.

T-SQL:

CREATE USER [LDomain\LUser] FROM EXTERNAL PROVIDER

Error:

Principal 'LDomain\LUser' could not be found or this principal type is
not supported.

I'm only looking for the script to add an AD user – no interface. I know that the AD user exists in Azure and have confirmed, but the Azure-Sql-DB isn't recognizing it, or this T-SQL is invalid – though this is from their documentation.

Best Answer

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address.

i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated domain members".

So, instead of: 

CREATE USER [your.user@example.com] FROM EXTERNAL PROVIDER

One needs to use the following convention. This is Microsoft's way of storing a guest / federated user from another Azure active Directory.

CREATE USER [your.user_example.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com] FROM EXTERNAL PROVIDER

Alternatively, using groups makes this far more intuitive and manageable.

  1. Create a group (say SqlUsersFromExternalDirectory) in the Azure subscription's default Azure Active Directory.
  2. Add the external users you want to access the SQL Warehouse or DB to to the group.
  3. Add the group as an external user in the target database

CREATE USER [SqlUsersFromExternalDirectory] FROM EXTERNAL PROVIDER

This works fine, the external users can then sign in, admins can GRANT permissions etc, etc.

Related Question