Azure SQL Server – How to Authenticate using Azure Active Directory

Azure SQL Server provides multiple ways to connect and fetch the data. The most common authentication is based on username and password, however, in the world of the cloud, the most secure authentication method is based on the Azure Active directory using MFA.

Supported features

  • By default, this feature is not enabled, but once enable then username/password-based authentication is disabled
  • Azure AD authentication gets applied to all the databases on the server
  • This feature can be enabled using Azure Policies
  • Secure database access organization
  • Can be disabled at any point in time
  • Database password sharing is no longer needed

* Steps to create Azure SQL instance is not covered

Enable authentication

By default, this feature is disabled. We need to explicitly enable the feature at the Azure SQL Server instance.

Navigate to SQL Server Instance > Settings > Azure Active Directory and perform the below steps to enable Azure AD authentication:-

  1. Add an Active Directory user as an Admin.
  2. Check the Azure Active Directory authentication only option
  3. Click on Save
1. AD Set Admin

Connect using SSMS or Azure Data Studio

Open SQL Server Management Studio (SSMS) on the machine, then

Provide Azure SQL Server Instance Name (URL), choose Azure AD – Universal with MFA, User name, and click on Connect.

An authentication window will open, then provide credentials and follow the instructions to complete the authentication.

2. Connect Azure SQL Server

Note : Set server firewall > Add client IP and save, if needed

3. Connect Database

SQL Server Authentication is no more valid

SQL Server Authentication is no more valid. Navigate to SQL Server Instance > Query editor > enter username and password and click on OK.

4. Attempt SQL Authentication

So, we have seen that enhancing the security of SQL Server by enabling Azure AD authentication. As long as someone is part of AD, one should access the databases on the Azure SQL instance where the feature is enabled.

If you have any suggestions/feedback, please put them in the comment box.

Happy Learning đŸ™‚

Leave a Reply

Up ↑

%d bloggers like this: