Add Active Directory User For Azure-SQL-DB

by WhyAShortage   Last Updated February 25, 2017 13:06 PM

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.



Answers 2


Try force starting the the active directory sync, Start-ADSyncSyncCycle -PolicyType Initial. Users created in your AD have to be sync with tha Azure AD.

medero
medero
January 07, 2017 18:50 PM

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 [[email protected]] 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.

Murray Foxcroft
Murray Foxcroft
January 24, 2017 12:52 PM

Related Questions