[HOWTO] Access Azure SQL Database with Microsoft SQL Server Management Studio 18 using AAD Authentication

As announced in my initial post, here is my first technical post – published just before the end of the year.

A few weeks ago I wanted to access a Azure SQL database with a specific AAD user through Microsoft SQL Server Management Studio 18 using Azure Active Directory (AAD) authentication. To do so, I first had to enable AAD authentication on the Azure SQL server by setting an Azure Active Directory admin as follows:

• Go to the Azure Portal
• Open the Azure SQL server resource that hosts the database you want to connect to
• Navigate to Settings > Azure Active Directory
  
• Click on Set admin
• Select a group or a user from the AAD
  For operational reasons I recommend to create a group in the AAD and select this group as admin
• Click Select
• Click Save
• Open the Azure Active Directory of the current tenant
• Navigate to Manage > Roles and administrators
• Search for role Directory readers
• Assign the service principal of the Azure SQL server resource to this role
  • Add assignment
  • Search for the Azure SQL server by name
  • Select the corresponding item from the list
  • Click Add

If the user that wants to access the database got selected as Azure Active Directory admin on the Azure SQL server resource or if the user is a member of the group that got selected as Azure Active Directory admin on the Azure SQL server resource, the corresponding user is already eligible to access the Azure SQL server with Microsoft SQL Server Management Studio 18. However, if you follow the least privilege principle, most users should not have administrative permissions.

To add your user as a member for example to the fixed-database role db_owner, proceed as follows:

• Create a new AAD group that holds all the owners of the corresponding database
• Connect to the Azure SQL server with an admin user (i.e. by using Microsoft SQL Server Management Studio 18)
• Execute the following query on the database:
  BEGIN
CREATE USER [NAME_OF_THE_AAD_GROUP] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [NAME_OF_THE_AAD_GROUP]
END

Now, as your user is a member of the fixed-database role db_owner you are ready to access the database:

• Open Microsoft SQL Server Management Studio 18
• In the Connect to Server window enter the username of your user, the name of your Azure SQL Server which can be found in the Azure Portal and select Azure Active Directory – Universal with MFA for Authentication
  
• Click Options >>
• Type the name of your database into the field labelled Connect to database

If you connect the first time with your current device from the current network, you get prompted to add a rule for your clients IP address. You have to add this rule to successfully connect to the database.