I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition). I believe the SSMS is simple enough. Under the "Login" tab, I have chosen "Active Directory – Universal with MFA support" and have my user name (like "john.doe@example.com").
Under "Connection Properties," I put in a specific database and my Azure AD tenant ID (guid):
When I try to log in, I get a popup from Microsoft, which I assume is the SSO login:
However, when I put in my password, I get error 18456, State 1:
I assume this is because I need to add the user on the server side. However, I have not been able to find how to do that for an on-premesis SQL Server (there are plenty of guides for Azure SQL). I am using Standard Edition 2017. What do I need to do to complete the setup?
Best Answer
"Active Directory - Universal with MFA Support" is an Azure Active Directory authentication method. SQL Server (on premises) doesn't support Azure Active Directory Authentication; it only supports SQL Auth and Windows Auth (NTLM and Kerberos).