Share via

Trouble adding Azure AD users to Azure SQL Server

Ted 41 Reputation points
2021-01-29T02:46:22.147+00:00

Within Azure, I've created a SQL server resource and a SQL Server database using my personal microsoft login. I am creating a small database for a client. I added an Azure Active Directory tenant which became the <default>. I then added a 2nd custom domain tenant for the client's domain and verified it. I set an Active Directory Admin for the server which is Ted@<my account>.onmicrosoft.com. I've added several client users to the custom domain tenant such as Lynn@<client>.com. Now I want to add the client accounts as users in my SQL Server database.

I login to SSMS with ted@<my account>.onmicrosoft.com (an AD user from the default tenant set as the AD Admin)

In SSMS I can see the master datbase and clientdb database. I can also see the user ted@<my account>.onmicrosoft.com in the list of users in the master database.

In the clientdb database, I was able to add an AD user as a contained user (from the default tenant) using the following:

CREATE USER [joe@<my account>.onmicrosoft.com] FROM EXTERNAL PROVIDER;

Now here's the problem. I can NOT add a user from the custom domain tenant:

CREATE USER [lynn@<client>.com] FROM EXTERNAL PROVIDER;

Msg 33130, Level 16, State 1, Line 8
Principal 'lynn@<client>.com' could not be found or this principal type is not supported.

I also tried (even though it's not a guest user):

CREATE USER [lynn_<client>.com#EXT#@<my account>.onmicrosoft.com] FROM EXTERNAL PROVIDER;

I've googled this for days ... please help!

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
20,630 questions
0 comments
Accepted answer
  1. Nandan Hegde 31,511 Reputation points MVP
    2021-01-29T03:24:52.87+00:00

    Hey @Ted ,
    The guest user has to accept the invitation to be added in the tenant and be visible across other applications.
    Can you check the below property :
    in Azure AD >> users and check for that user's Invitation state

    In case if it is in pending acceptance, that user cannot be used across any Azure in that tenant.

    The other way to test that out is, in the Server for the Active directory admin ; try searching for that user and adding him as the server admin.

    Based on that we can track the issue further.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ted 41 Reputation points
    2021-01-29T15:08:32.417+00:00

    I should have tagged @Nandan Hegde in my last post. Thanks!

    0 comments
  2. Ted 41 Reputation points
    2021-02-01T15:13:18.167+00:00

    Thanks again @Nandan Hegde
    As you suggested, I did end up inviting them as guests. I did the following:

    • added them as regular users to the <client>.com tenant
    • added them as guest users to <my account> tenant
    • added them as users to the SQL database where they are referenced as "name_<client>.com#EXT#@<my account>.onmicrosoft.com"
    • with the login to the desktop interface, they are able to use the format name@Evan ! .com

    I'm happy with this solution!