Accessing Azure SQL with different methods

I decided to write about Azure SQL as inspired by an question from the community.

The ask was “How to do I access Azure SQL with MFA enabled user, should I remove the MFA and create a normal user?”

In this post I will cover Conditional Access, Service Principals, Universal Authentication and Azure AD only authentication.

First answer

You shouldn’t remove MFA in any circumstances, there are more viable solution to access SQL in the cloud. By best practices you should have AAD admin credentials assigned to the Azure SQL servers

Let’s see the possibilities. The option numbering isn’t in a particular order. Just wanted to highlight the ways it could be achieved.

First option – Allow login from trusted (named) locations


Keeping your Azure SQL access inside a segment that you know and do it easily with Conditional access policies.

If you have Conditional access, you can use named locations. For the whole office network, your home public address etc.

How to setup?

You can find the control plane here

From the you will IP ranges or even country if needed. Then mark it as a trusted location.

Once done, create a Conditional access policy with user, group or role you need. In my example I choose a specific user.

Choose Azure SQL as the application.

And Include Any location to trigger it from anywhere.

And exclude your Trusted location (that you just specified)

Then choose Block Access. To block access from all others than trusted locations.

When we run the What If, choose a not trusted IP and Country, it will tell us that the policies will be enforced and block will be active.

McDonald Islands got my attention from the list! (No pun intended)

But when we choose our safe place, you shall have access, who cool is that!

Second option – Access SQL with Service principals

Firstly, keep this in mind for Azure SQL

Secondly, here is the supported Data SQL-Client version

ValueDescriptionMicrosoft.Data.SqlClient version
Active Directory PasswordAuthenticate with an Azure AD identity by using a username and password1.0+
Active Directory IntegratedAuthenticate with an Azure AD identity by using integrated authentication2.0.0+1
Active Directory InteractiveAuthenticate with an Azure AD identity by using interactive authentication2.0.0+1
Active Directory Service PrincipalAuthenticate with an Azure AD identity by using the client ID and secret of a service principal identity2.0.0+
Active Directory Device Code FlowAuthenticate with an Azure AD identity by using Device Code Flow mode2.1.0+
Active Directory Managed Identity,
Active Directory MSI
Authenticate with an Azure AD identity by using system-assigned or user-assigned managed identity2.1.0+
Active Directory DefaultAuthenticate with an Azure AD identity by using password-less and non-interactive mechanisms including Managed Identities, Visual Studio Code, Visual Studio, Azure CLI, etc.3.0.0+


  • Support Azure SQL Database server roles for permission management.
  • Support multiple Azure AD users with special roles for SQL Database, such as the loginmanager and dbmanager roles.
  • Functional parity between SQL logins and Azure AD logins.
  • Increase functional improvement support, such as utilizing Azure AD-only authentication. Azure AD-only authentication allows SQL authentication to be disabled, which includes the SQL server admin, SQL logins and users.
  • Allows Azure AD principals to support geo-replicas. Azure AD principals will be able to connect to the geo-replica of a user database, with a read-only permission and deny permission to the primary server.
  • Ability to use Azure AD service principal logins with special roles to execute a full automation of user and database creation, as well as maintenance provided by Azure AD applications.
  • Closer functionality between Managed Instance and SQL Database, as Managed Instance already supports Azure AD logins in the master database.

How to setup?

First you have to create a loginsyntax to the DB. The login_name specifies the Azure AD principal, which is an Azure AD user, group, or application.

Then create a user syntax

You can disable or enable the Azure AD login in Azure SQL Database with the following

How make the connection from code?

Some notes on what is not supported

  • Overlapping Azure AD administrator logins are not supported. Any login is superseded by Azure AD admin. The login created for this user will have no impact if an Azure AD account already has access to the server in the capacity of an Azure AD admin, either directly or as a member of the admin group.
  • It is not supported to set an Azure AD login that is mapped to an Azure AD group as the database owner.
  • It takes a user reconnecting before new permissions for an Azure AD login with open connections to an Azure SQL Database take effect.
  • The SQL server administrator is unable to add users or logins to any databases using Azure AD.

Third option – Access SQL with user having MFA


Well, benefits are self-explanatory, you will have Universal Authentication for your user but there is also limitations.

What you need first?

You will need SQL server that provides a connection endpoint for database access (<serverName>

How to set it up?

Open the control plane from

Add needed addresses to Server firewall as by default, no public IP addresses are allowed.

See more on how the rules are applied.

Once done run Managed Studio and choose Authentication to Azure Active Directory – Universal with MFA. This option requires SSMS version 17.5 or later.

How to connect from code?

What the user will see?

  • A window that shows an Azure AD user name and requests the password from the user.
  • The dialog box does not show up if the user’s domain is federated with Azure AD because a password is not required.
  • A sign-in dialog box will appear if the Azure AD policy requires the user to use multi-factor authentication.
  • The system prompts the user for a mobile phone number to deliver text messages to the first time they utilize multi-factor authentication. Every message contains the verification code that needs to be entered by the user in the next dialog box.
  • A dialog box that requests the verification code for Multi-Factor Authentication, which the system has sent to a cell phone.

Universal Authentication (MFA) limitations

  • SSMS and SqlPackage.exe are the only tools currently enabled for MFA through Active Directory Universal Authentication.
  • SSMS version 17.2 supports multi-user concurrent access using Universal Authentication with MFA. For SSMS version 17.0 and 17.1, the tool restricts a login for an instance of SSMS using Universal Authentication to a single Azure Active Directory account. To sign in as another Azure AD account, you must use another instance of SSMS. This restriction is limited to Active Directory Universal Authentication; you can sign into a different server using Azure Active Directory - Password authentication, Azure Active Directory - Integrated authentication, or SQL Server Authentication.
  • SSMS supports Active Directory Universal Authentication for Object Explorer, Query Editor, and Query Store visualization.
  • SSMS version 17.2 provides DacFx Wizard support for Export/Extract/Deploy Data database. Once a specific user is authenticated through the initial authentication dialog using Universal Authentication, the DacFx Wizard functions the same way it does for all other authentication methods.
  • The SSMS Table Designer does not support Universal Authentication.
  • There are no additional software requirements for Active Directory Universal Authentication except that you must use a supported version of SSMS.
  • See the following link for the latest Microsoft Authentication Library (MSAL) version for Universal authentication: Overview of the Microsoft Authentication Library (MSAL).

Fourth option – Azure AD only

Azure AD only authentication is also an viable option security wise as it will remove SQL DB based local login completely and therefore making your SQL little bit safer.


  • When Azure AD-only authentication is enabled in the Azure SQL environment, all connections from SQL server administrators, logins, and users are blocked. The server or database can only be accessed by users who authenticate with Azure AD.
  • The Azure portal, Azure CLI, PowerShell, or REST API can all be used to set or disable Azure AD-only authentication. Azure Resource Manager (ARM) templates can be used to configure Azure AD-only authentication when setting up servers.

How to setup?

Enable it first, otherwise you cannot select use Azure AD auth only.

Then open check the “Support only Azure Active Directory authentication for this server”


  • Microsoft.Sql/servers/azureADOnlyAuthentications/*
  • Microsoft.Sql/servers/administrators/read – required only for users accessing the Azure portal Azure Active Directory menu
  • Microsoft.Sql/managedInstances/azureADOnlyAuthentications/*
  • Microsoft.Sql/managedInstances/read

You can find the role under IAM and and roles

And choosing SQL Security Manager and view

And to see the full JSON content.

AZ CLI and Transact-SQL

Read more on the limitations from here.

Defender for Databases

When you think about security, you should think about Defender also.

And more information here

It can protect you from the following:

  • Potential SQL injection attacks – including vulnerabilities detected when applications generate a faulty SQL statement in the database
  • Anomalous database access and query patterns – for example, an abnormally high number of failed sign-in attempts with different credentials (a brute force attempt)
  • Suspicious database activity – for example, a legitimate user accessing an SQL Server from a breached computer which communicated with a crypto-mining C&C server


So there we have it, secure ways to connect with Azure SQL,

There can be even more variables but didn’t figure them all out, if you have any other means to securely access than these, feel free to reach out.

Author: Harri Jaakkonen

Leave a Reply

Your email address will not be published. Required fields are marked *