Connecting to Azure SQL DB with an Azure Active Directory Account in Azure Data Studio

Azure SQL DB AAD Authentication

This all started because I wanted to follow Tracy’s directions on setting up Ola for index maintenance in Runbooks. I couldn’t set up the automation account permissions unless I was logged in with Azure Active Directory – Univeral with MFA. When I Googled, I didn’t really find anything about this topic in particular. This is why I wrote this post to help people out there and myself in case I forget later. If you are struggling to log in to Azure SQL db with AAD authentication, hopefully, this post helps you out.

I log into a personal Azure account with my Gmail email address. Azure wouldn’t let me set that user as the Azure Active Directory Admin in Azure SQL. It said that “Microsoft Account members are not allowed”.

At this point in the project, I decided to take a break for the night. Thankfully, the next day I got the idea to add my other email address with its own Azure account. To do this I added that email address as an external user to Azure Active Directory.

create new user

Make sure to send an invite via email and accept it.

Invite external user

I accepted the invite and added that user as the AAD admin on the Azure SQL Server.

Setting AAD

At this point, I could use the MFA account to log into the Azure SQL setup in Azure Data Studio. You can use the email address associated with the external user you set up. You don’t have to use Azure’s complicated admin name.

Azure SQL DB AAD Authentication

I hope that you are now able to log into Azure SQL DB with AAD authentication. Currently, I am figuring out how to Terraform all this Runbook stuff, so that will appear in a post in the near future.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.