1

I've synched my OnPremise AD DS users into Azure using Azure AD connect.

How can I enable the user of my Azure SQL Server and Azure SQL DB instance to use MFA/2FA when connecting with SSMS using the existing AD account?

Because at the moment, everyone is connecting using SQL account which is not linked with Azure AD nor OnPremise AD DS account and must be secured with 2FA/MFA.

2
  • 1
    just to make it clear, under "Azure SQL Server" you mean logical SQL Server that contains Azure SQL DB instances, not the SQL Server on Azure VM ? Jul 22 at 10:52
  • 1
    To force 2FA for an AAD user use an AAD conditional access policy. Jul 22 at 17:50
1

First you will need create a contained database user inside Azure SQL Database, run below:

create user [user.name@domain.com] from external provider

Alternatively assign AD user/group of admin(s) on Azure SQL Server level
Then use the "Active Directory - Universal with MFA support" authentication type in SSMS

From the "SQL Server Administration: Inside Out" book

Active Directory Universal Authentication

Universal Authentication uses Azure two-factor authentication, and you can use it for connecting to Azure SQL Database or SQL Data Warehouse resources. SQL Server Management Studio can use Azure Authenticator application or other two-factor methods

Currently, this feature is limited to authentication with Azure AD accounts for connecting to a database in Azure SQL Database or Data Warehouse, though further Microsoft development around two-factor authentication for server access is likely - and welcomed

This method, like two more Azure AD-based authentication methods (Active Directory Password, Active Directory Integrated), was first supported by SQL Server Management Studio as of SQL Server 2016

AD Universal with MFA support

4
  • Hi @aleksey, thank you for the update. "limited to authentication with Azure AD accounts" does it mean the OnPremise Snched AD DS account to Azure AD is not capable of authenticating against Azure SQL Server and Azure SQL Instance? Jul 27 at 1:57
  • 1
    @SeniorSystemsEngineer in 2021 this should work for Azure SQL Managed Instance and SQL Server on Azure VM as well Jul 27 at 13:11
  • Hi Aleksey, so assuming my AD accounts are all synched from AD DS to the Azure AD, then I can just create the user account with the above SQL command you've suggest? is there anything that I need to join or configure so the command works to recognize the Synched AD DS account in Azure? Jul 28 at 12:19
  • 1
    @SeniorSystemsEngineer before creating the user account with above SQL, you need to connect/log in to Azure SQL Database using your domain account. Otherwise, if you log in to Azure SQL DB using sql-authenticated account, you won't be able to create AD-authenticated users (create user..from external provider). You can set your AD account as Admin for that Azure SQL DB in Azure portal, to be able to login using your AD account. Jul 28 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.