How to create and check AAD Login in Azure SQL Database

How to create and check AAD Login in Azure SQL Database

If you want to create AAD login for Azure SQL Database firstly be aware that you have to create AAD users with Azure Active Directory Admin, That means you’re not able to create AAD Logins for your database when you use a SQL Login.

USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

Check are you part of Azure Active Directory Admin?

Simple step, you need to go to the Portal then jump to SQL Server page and from Setting choose Azure Active Directory and see if your account is part of Azure Active Directory Admin. See the following screenshot:

Check your AAD account is admin in Azure SQL Database

Create Login in master database

Then you need go login to SSMS with AAD admin and run the following command in master to create the login.

 
Use master
CREATE LOGIN [xxx@microsoft.com] FROM EXTERNAL PROVIDER
GO

Check the user has been created?

One of the troubleshooting steps is run the following query in master to see if the login has been created or not, obviously you can add condition to see the specific user.

 
SELECT name, type_desc, type, is_disabled 
FROM sys.server_principals
WHERE type_desc like 'external%'

That’s not relevant to this topic but for SQL Login you can use the following query:

 
Select  name,type,type_desc,create_date,modify_date  FROM sys.database_principals
WHERE  type_desc = 'SQL_USER'

Create the user account

Next step you need to run the following command to create the user account

 
CREATE USER [xxx@microsoft.com] FROM LOGIN [xxx@microsoft.com]

Then you only need to give the user access to the object or anything you wish the following example I added to DBReader group and user can read objects from that database.

 
ALTER ROLE [db_datareader] ADD MEMBER [xxx@microsoft.com]

As I also explained here you might need to change the database name from the option as you do not have access to Master database.

Two following links also be useful for creating AAD Login and SQL Login

Create and utilize Azure Active Directory server logins

Create Login in SQL Server