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:
Once the deployment succeeds, open the SQL Server in the portal. Locate the “Active Directory admin” section as highlighted below:
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:
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:
Press the “Select” button to return to the Active Directory admin screen:
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:
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.
Enter an appropriate User name and press the “Connect” button. This will redirect you to the Active Directory login page for your organization:
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:
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:
Click the “Options” button to open the “Connection Properties” tab:
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.
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.