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
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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂