Configuring Azure SQL Server and SQL DB instance with MFA/2FA for On-premise AD DS synched account

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

create user [[email protected]] 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

Configuring Azure SQL Server and SQL DB instance with MFA/2FA for On-premise AD DS synched account

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply