2

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.

2

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.

  • 1
    What does the EXT stand for here? – Jakub Holovsky Apr 12 '18 at 15:10
  • 1
    It is Microsoft's internal "tag" for external / guest accounts in the active directory. It is purely a naming convention. – Murray Foxcroft Apr 12 '18 at 15:17
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.