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.

Aleksey Vitsko
  • 4,308
  • 3
  • 21
  • 47
Senior Systems Engineer
  • 135
  • 1
  • 1
  • 7

1 Answers1

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

Aleksey Vitsko
  • 4,308
  • 3
  • 21
  • 47
  • 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? – Senior Systems Engineer Jul 27 '21 at 01:57
  • 1
    @SeniorSystemsEngineer in 2021 this should work for Azure SQL Managed Instance and SQL Server on Azure VM as well – Aleksey Vitsko Jul 27 '21 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? – Senior Systems Engineer Jul 28 '21 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. – Aleksey Vitsko Jul 28 '21 at 13:04