Add Azure Active Directory User to Azure SQL Database

9
votes

I have an Azure SQL Server and can SSMS into it. I also have an Azure Active Directory with a user named [email protected]. 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 [[email protected]] FROM EXTERNAL PROVIDER;
CREATE USER mytestuser;

But this generates the errors of:

Principal '[email protected]' 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;
5
azureazure-active-directoryazure-sql-database
you probably need to switch to your database instead of master – 4c74356b41
I tried that as well. I will add an AD Admin to Azure SQL and try that though – Kode
Logging in with the Azure Active Directory Admin tied to Azure SQL worked... but trying to execute this from Azure SQL did not – Kode
You need to first log in under AAD authentication. You can't log in with SQL Authentication then add an AAD user. – Nick.McDermaid
What would be the option If I need an AAD user to access multiple DBs in the Azure SQL? Do I need to create user separately to individual databases? Can't I have it created in a one single place so that I can access multiple database? Is it the reason why need to create it under Master? – hiFI

5 Answers

17
votes

After wasting 4 hours of my day trying to do this, below are the steps that worked for me:

  • as per the documentation, set your AD account as the Active Directory admin (follow the steps mentioned in the documentation here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure under: Provision an Azure Active Directory administrator for your Azure SQL Database server).
  • Install the latest version of SSMS on your machine (the 18 RC1 in my case). If you have an existing version installed, uninstall it and "try" to clean any left over registry keys, list here: Failed to parse XML blob ).
  • Connect to your server using [Active Directory Integrated]. If you get the following error message : [Failed to parse XML blob], repeat step 2, or just install the latest version of SSMS on a different VM/Machine on your network (needs to be part of the same domain).
  • Once connected, execute the following SQL (from the official documentation) CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

I cannot believe I wasted almost two working days trying to do something as simple as adding a user to db. This is beyond belief. (/rantover)

6
votes

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

3
votes

For me there was a trick where you do some steps in SSMS using Active Directory - Integrated and some steps using local SQL Authentication. Here's what worked for me:

I set the domain account to use for the "Active Directory admin" setting in the Azure Sql Server features screen. Then I was able to connect using SSMS running under this account.

Note: To simplify running SSMS as this other user I used runas: C:\Windows\System32\runas.exe /savecred /user:[email protected] "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Running as this user, I connected using the SSMS authentication option, "Active Directory - Integrated". From here I ran the following in the master db:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

Then I connected to same server in SSMS using local SQL Authentication, logging in with the account set as the "Server admin" for the Azure Sql Server instance. From here I ran alter role commands in master db:

ALTER ROLE dbmanager ADD MEMBER [[email protected]]
ALTER ROLE loginmanager ADD MEMBER [[email protected]]

Now I could go back to the to SSMS running as the AD Admin user and from there I could run CREATE USER commands as above but for other domain accounts:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

You can decide which database to run the above (e.g., master and your non-system databases).

The domain users can now log in using "Active Directory - Integrated". Note if you add a domain user that is configured for MFA, then for that user to log on using SSMS they should select the SSMS authentication option, "Azure Active Directory - Universal with MFA", and their username should be with an "@" not backslash.

2
votes

For those of you using pyodbc instead of SSMS

  1. go to Azure Portal,

  2. find server,

  3. 'Active Directory Admin',

  4. Set Admin to [email protected],

  5. Login to the db you want to grant access to:

    from pyodbc import connect,drivers

conn = connect(driver=drivers()[0],
               server='someserver.database.windows.net', 
               database='somedb',
               Authentication='ActiveDirectoryPassword',
               user='[email protected]',
               PWD='somepassword')
cursor = conn.cursor()

  6. Add a user to sql db

    cursor.execute("CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;")
conn.commit()

  7. Grant read access:

    cursor.execute("EXEC sp_addrolemember 'db_datareader', '[email protected]';")
conn.commit()
2
votes

Overview

I had the same issue and resolved it using Azure CLI and sqlcmd. I could not make the sql management studio part work as it kept complaining about my device not being approved, however, the same worked with the command line tool!

Creating Azure AD Admin using Azure CLI

# Get objectId of the user you want to be admin
objectid=$(az ad user list --filter "userPrincipalName eq '[email protected]'" --query [0].objectId -o tsv)

# Setting user as admin
az sql server ad-admin create --display-name [email protected] --object-id $(objectid) --resource-group yourresourcegroup --server sqlservername --subscription "Subscription name or id"

Adding an AD-User to Azure SQL

I used the SQL command line tool on Ubuntu, which can be installed using the documentation.

# Installing SQL command line on Ubuntu 20.04
sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools

# Logging in to Azure SQL (-G means that you use Azure AD)
/opt/mssql-tools/bin/sqlcmd -U [email protected] -P yourpassword -S sqlservername.database.windows.net -d master -G

# Creating a user (If using SSMS, you may experience problems)
1>CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
2>GO