Setting up AD authentication with Azure SQL Database sounds simple, it is assuming you plan carefully. I did run into issues but once rectified it felt great using AD authentication in Azure rather than just SQL logins.

If you look at the below diagram, I basically want to create an Active Directory Admin for my SQL Server which I have done and it is an AD group something that I would recommend over just a single user account.

Even though Microsoft provides a lot of documentation on the setup I thought I would share my experiences and issues encountered and hopefully one day someone may find my post useful.

I think it is important to highlight a couple of points, more specifically around the requirement of ADALSQL.DLL and proper setup of AD which I will highlight below and reference some links, please do this as it lays the foundation for you.

ADALSQL.DLL

You need ADALSQL.DLL which is part of the latest SQL Server Management Studio (SSMS) to test access. This stands for Active Directory Authentication Library for SQL Server.

If you don’t have this then you might get the error below. Basically what I am trying to say here is use SSMS 17 found here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Create and populate an Azure AD and Provision AD Admin

Along with your administrators follow this guide: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure  ( I will assume you have already).

The main goal here to set finally set an Azure Directory Admin within your SQL Server.

Let’s jump into SSMS 17.

Login in via SSMS

You cannot login to SSMS via SQL authentication and issue create commands for AD users, it will not work even though you have Active Directory Admin setup. Try it and you will get the below.

Only connections established with Active Directory accounts can create other Active Directory users.

You will need to login via AD Authentication. So I select AD Integrated Authentication which picks up my account that is within the Admin group but I the Multi-factor authentication (MFA) error.

I use MFA so I had to login via universal authentication where then I have to use my pass code to login via SSMS.

Assume now I am connected via my Azure AD Admin account, it is here where you issue create user commands such as:

CREATE USER [Bill.Gates@domain.com]
FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [Bill.Gates@domain.com]

EXEC sp_addrolemember 'db_datareader', ‘Bill.Gates@domain.com’;

So login to SSMS as the above user using AD Integrated Authentication and it should work.

You MIGHT get this error.

Why? You need to set your Default database under connection properties.

Then you will be authenticated – read only.