Azure AD Authentication For Azure SQL Server Managed Databases

In order to set up the Azure Active Directory Authentication, we need to configure the Active Directory Admin for the SQL Server we have deployed under the Azure Resource group.

Configure the Azure Active Directory Admin

Open SQL Server and in the left side menu under the settings, we will find the option as Active Directory Admin. Click on that and it will take you to the next page to set up the admin configuration. Click on the Set Admin and search for the admin email to whom you want to be added as the admin and once you select and save the admin details it will be shown the Active Directory page, see the below image.

Now we have set up the admin who can centrally access the SQL server and also have the privilege to grant access to others as well. Open the SQL Server management studio to check whether we are able to access it with admin credentials

Azure Active Directory Authentication using SSMS

Open the SSMS (SQL Server Management Studio), add the server name and choose the Azure Active Directory - Universal with MFA (Multi-Factor Authentication). Add the admin email Id to access the server and once we click on the connect button it will take us through the Microsoft Authentication in order to access the Database. We will use the admin credentials for Microsoft Authentication, see the attached image below

It will authenticate via Azure Active Directory from Microsoft and then it will ask to set up the firewall against the SQL server to grant permission for this system IP address. Once you click on Ok you will have an access to the Database which we have created before.

Note
No need to change any settings in the Network Firewall it is automatically taken care of by the Azure itself.

Grant access for other users

After logging in with admin credentials, you will find multiple options for granting access to the SQL server and the SQL database, which will be covered in this article.

• SQL Server Basic Authentication
• Azure Active Directory Authentication

SQL Server Basic Authentication

To Grant access for others, we need to create the user under the server level. Then we will add the roles for that specific user to access the database to grant permission for the reading and write operations. I have added the base username and password for the demo purpose you can choose as per your requirements.

Step 1

-- switch to master
-- create a new sql login and give them a password
create login Appuser with password = 'admin@123';

Step 2

-- connect to Database
-- create a new SQL user for that login
create user Appuser for login Appuser;

Step 3

-- untill you run the below two commands, Appuser has no access to read or write data.
alter role db_datareader add member Appuser;
alter role db_datawriter add member Appuser;

We have successfully created a new user to access the database. See the below image for accessing the SQL server with newly created credentials.

Azure Active Directory Authentication

To grant access to a particular tenant inside your organization here is the easiest way that we can grant access for fellow members as individual contributors. This can only work when the tenant should be matched with the same organization.

Step 1

/*switch to master */
create user "[email protected]" from external provider

Step 2

/*Switch to database and execute the for reader or writer access - Azure AD*/
exec sp_addrolemember 'db_datareader', '[email protected]'
exec sp_addrolemember 'db_datawriter', '[email protected]'
Go 

Step 3

grant execute on schema :: dbo To "[email protected]"

Conclusion

The above article demonstrates the Azure Active Directory Setup with the SQL Server Basic Auth for Azure SQL Server managed Databases

Thank you for reading, please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

Keep Learning ...!