azureazure-active-directoryazure-sql-database

Add Azure Active Directory User to Azure SQL Database


I have an Azure SQL Server and can SSMS into it. I also have an Azure Active Directory with a user named mytestuser@mytest.onmicrosoft.com. I want to add this user to have permissions to a database in my Azure SQL Server. The first step is trying to add it to the primary security of the Azure SQL Server.

I have tried the following on the Master Database:

CREATE USER [mytestuser@mytest.onmicrosoft.com] FROM EXTERNAL PROVIDER;
CREATE USER mytestuser;

But this generates the errors of:

Principal 'mytestuser@mytest.onmicrosoft.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

and

'mytestuser' is not a valid login or you do not have permission.

How do I add an Azure Active Account to Azure SQL? Once I have added it via the Master so it shows up in Security, I should be able to add it to any number of created databases via:

CREATE USER mytestuser FROM LOGIN mytestuser; 

Solution

  • I was able to connect and add an Active Directory User but it required the following:

    1) SQL Server Management Studio 2016 or greater to have the Active Directory Login options (I used Active Directory Password Authentication)

    2) Ensuring that the Azure SQL Server had the Azure Active Directory Admin set. You will this account to connect in Step 1