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.
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 get this 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.
I am in as my main AD account now.
I did find something “buggy”, if you edit top 200 rows you will be prompted with a pop box which prevents me from doing anything.
Rather strange! I hope Microsoft fixes this.
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, just to prove this I try to create a table which will fail.
I log back in as my main Azure admin account and issue the following query to show permissions.
SELECT p.name, prm.permission_name, prm.class_desc, prm.state_desc, p2.name as 'Database role', p3.name as 'Additional database role' FROM sys.database_principals p JOIN sys.database_permissions prm ON p.principal_id = prm.grantee_principal_id LEFT JOIN sys.database_principals p2 ON prm.major_id = p2.principal_id LEFT JOIN sys.database_role_members r ON p.principal_id = r.member_principal_id LEFT JOIN sys.database_principals p3 ON r.role_principal_id = p3.principal_id WHERE p.name <> 'public'