ACCESSING AZURE SQL WITH SSMS USING AZURE ACTIVE DIRECTORY AND MULTI-FACTOR AUTHENTICATION

Among its many features, Azure Active Directory (AAD) allows enterprise organizations to enforce Multi-factor Authentication (MFA) when accessing Azure and O365 resources. Previously it was not possible to log in to SQL Server Management Studio (SSMS) using AAD with MFA, but with the release of 2017.2 this authentication method is now supported. This article will walk through the steps necessary to properly configure Azure SQL to accept AAD/MFA logins.

CONFIGURE AZURE SQL

Azure SQL consists of two relevant parts, the SQL Server and the SQL Database. Let’s start by creating a SQL Database with a new Server configuration:

Create SQL Resources Once the deployment succeeds, open the SQL Server in the portal. Locate the “Active Directory admin” section as highlighted below:

SQL Server blade in the Azure Portal

NOTE: You will still need to add the appropriate IP range to the Firewall Rules.

Click on the “Not Configured” link in the Active Directory admin section. Next, click the “Set admin” button:

Add Active Directory Admin The Active Directory admin can either be a user or a group defined in your Active Directory. For this example we will select a group from our Active Directory list:

Select a User or Group from the Active Directory Press the “Select” button to return to the Active Directory admin screen:

Save the selected admin The selected Active Directory group is now indicated on the screen. Press the “Save” button to apply the changes. If you return to the Overview section you will see the Active Directory admin has been updated:

Active Directory admin added

CREATING THE USER OR GROUP IN SQL SERVER

The Active Directory admin you registered above is intended for administrative purposes and will have full authority to the entire SQL Server instance. This is a function of Azure, not of SQL Server. Typically, you will have a separate user or group that will need to access the databases on the server for production purposes. This requires the user or group to be registered in the SQL Database. Since the authentication is coming from outside the SQL Server, this is specifically an EXTERNAL user. And as this is related to Active Directory, only the Active Directory admin has the ability to create such a user. You will need to log in to SSMS as the Active Directory admin user. If you specified a group, log in as a user who is a member of that group.

NOTE: In order to login using AAD/MFA, be sure you have SSMS 17.2 or later. If you do not, you will not have the option to use MFA.

Open SSMS and specify the server name for your Azure SQL Server. Under “Authentication”, select the “Active Directory – Universal with MFA support” option.

Log in as AAD Admin Enter an appropriate User name and press the “Connect” button. This will redirect you to the Active Directory login page for your organization:

Organization Active Directory login page Enter your password and press the “Sign in” button. This will trigger the user’s configured method of MFA authentication. Complete the authentication process to finish logging in to SSMS.

NOTE: O365 offers 5 different ways to verify authentication, and each user can select their own preferred method. While covering all of these is beyond the scope of this article, my preferred method is to select “Notify me through app”, which will send a notification to the Azure Authenticator app on the user’s mobile phone and prompt them to either Allow or Disallow the authentication.

NOTE 2: If you have not yet set the IP Firewall rules for your SQL Server, the authentication will fail. This is usually the point that I remember to do that!

At this point, you should be logged in to SSMS as the Active Directory admin. The next step is to create the database user and grant them access.

CREATE THE SQL DATABASE EXTERNAL USER OR GROUP

In SSMS, open a New Query window and select the appropriate database. Paste the following code snippet into the window:

CREATE USER %GROUP_NAME% FROM EXTERNAL PROVIDER;

You will need to replace %GROUP_NAME% with the desired user or group from your Active Directory. Also, the user or group MUST exist in the Active Directory in order for this command to succeed. Executing the command will insert the user into your database’s list of Users:

User or Group added to SQL Database Next you will need to grant the user permissions. The following statements can be used to grant the desired access, again replacing %GROUP_NAME% with the appropriate user or group name:

-- To grant DataReader access:
EXEC sp_addrolemember 'db_datareader', %GROUP_NAME%;
-- To grant DataWriter access:
EXEC sp_addrolemember 'db_datawriter', %GROUP_NAME%;
-- To grant DDLAdmin access (CREATE and ALTER authority):
EXEC sp_addrolemember 'db_ddladmin', %GROUP_NAME%;

Once these commands are run, the user may now log in and access the database.

USER LOGIN TO SSMS

The user login process is similar to the admin login described above with one addition: the user MUST specify a database name. Also, the “browse server” feature will not work because the user cannot connect to the database to retrieve the list. As a result, you must manually enter the database name in the field. Once a database is successfully connected, however, SSMS should remember that database name, so it should only need to be entered once per database. Open the Connect dialog and enter the appropriate information, but do NOT press “Connect” yet:

Log in as User - select 'Options' Click the “Options” button to open the “Connection Properties” tab:

Manually enter the database name NOTE: The “AD domain name or tenant ID” field is not required.

Now press the “Connect” button to connect to the database. As mentioned above, the Firewall Rules will have to be properly configured for the user’s IP address.

CONCLUSION

Adding the additional security of MFA to your Active Directory users is a recommended approach, but can add layers of complexity and frustration. Hopefully, these instructions will help you mitigate those factors and take advantage of AAD/MFA in SSMS.