Active Directory Authentication with Azure SQL
I have several Azure SQL databases and I’d like to make managing authentication with them easier. For this blog post I’ve spun up a sample database using the AdventureWorks sample available.
The first step is making sure that an Azure Active Directory Admin is the SQL Database Admin
I also have a group in Active Directory that I’ve titled “DatabaseAdministrator” that I intend to grant permissions to.
Using the Azure Active Directory Admin, I’m going to login and run two commands on the database. This can either be run with SQL Server Management Studio or automated as part of database creation script with sqlcmd.
CREATE USER [DatabaseAdministrator] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember 'db_datareader', 'DatabaseAdministrator';
A database administrator should probably have more access than just “db_datareader”, but that’s good enough to start with.
The day has finally come where a new database administrator has been hired. Let’s set them up in Azure Active Directory with the group DatabaseAdministrator.
Let’s get the new hire to login.
They should automatically have access to read data.
Letting Active Directory manage authentication for users is a much better option than trying to create a selection of service accounts for each administrator. Automatically granting access to databases based upon user role and job title, is even better.