Azure SQL with AAD authentication
I though this had to be an easy task. Well, actually it is. If you find the right documentation and read it in the correct order 🙂
Basically I wanted to be able to login with my AAD (Azure Active Directory) user.
In the first step, the database needs to be configured for Azure Active Directory in order to add users in the second step.
Configure an Administrator
In the Azure portal go the the SQL server and search for “active directory” to add an Active Directory admin.
After you’ve added an admin and saved the value, you will be able to use SSMS (SQL Server Management Studio) to logon to the server. Probably SSMS will prompt you about a firewall exception.
Use SQL Management Studio to add users and grant permissions
For other users (not the administrator we configured above) to be able to logon, access has to be granted like with an on premises SQL Server.
Add a user to the master DB
Create a new query o
CREATE USER [rene.hezser@something.com] FROM EXTERNAL PROVIDER;
Next grant permissions to the user on the database itself.
Add user to database
Open another query on the database.
CREATE USER [rene.hezser@something.com] FROM EXTERNAL PROVIDER; ALTER ROLE [db_owner] ADD MEMBER [rene.hezser@something.com];
That should be it.
Some documentation I used: