Trouble adding Azure AD users to Azure SQL Server

Question

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!

Answer 1

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.

Answer 2

Thanks for the reply. However, the Azure AD users I've added are not guests (unless I'm not understanding). I've added Azure AD users in my default tenant and Azure AD users in the custom domain tenant (that I will be administering on behalf of my client). I'm able to add users from the default tenant to Azure SQL Server, but NOT the users from the custom domain tenant.

Also, in the setting for Active Directory Admin for the server, only users from the default tenant are available as options.

Answer 3

Thanks, but I'm not sure I follow ... "The Azure SQL database is linked to the tenant in which it is created". I can't see how a SQL database is created from within a tenant. Or any way to associate a database with a tenant. I thought SQL Server databases and AD tenants were independent resources added to the server. If I can, I don't mind creating a new database if I can possibly link it to the custom domain tenant. I'll take a closer look tomorrow.

Answer 4

Thank-you! You've been extremely helpful! I'm sure you've guessed, this is my first azure project.
This is a project for a client, I may have more. If I were to start over, how would I start this project differently?

Answer 5

It appears the solution of inviting the users as guests to my tenant will probably work, but I don't like it. It's seems bizarre that every user has to accept an invitation.

First, a quick summary of my scenario. I'm trying to create a cheap and simple solution for a client (and friend). The client has no corporate IT infrastructure and no IT budget. They are sales reps working from their homes. This will be a desktop application with a cloud-based database. They will be connecting to the database with an ODBC driver for SQL Server which contains the Microsoft Active Directory Authentication Library (ADAL.dll). ADAL will prompt them for their AAD username which will be associated with a SQL Server contained user. I'll user row level security to limit each sales rep to their respective data.

With this guest idea, will they be forced to login with a username such as "lynn_<client>.com#EXT#@<my account>.onmicrosoft.com"? If so, that's unacceptable.

So, given this scenario, how would I start again with Azure? I want to use their custom domain and avoid the overhead of adding the users as guests of my tenant? How would you start this project (in regards to Azure)?