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

Benefits

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 https://entra.microsoft.com/#view/Microsoft_AAD_ConditionalAccess/ConditionalAccessBlade/~/NamedLocations

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

Value	Description	Microsoft.Data.SqlClient version
Active Directory Password	Authenticate with an Azure AD identity by using a username and password	1.0+
Active Directory Integrated	Authenticate with an Azure AD identity by using integrated authentication	2.0.0+¹
Active Directory Interactive	Authenticate with an Azure AD identity by using interactive authentication	2.0.0+¹
Active Directory Service Principal	Authenticate with an Azure AD identity by using the client ID and secret of a service principal identity	2.0.0+
Active Directory Device Code Flow	Authenticate with an Azure AD identity by using Device Code Flow mode	2.1.0+
Active Directory Managed Identity, Active Directory MSI	Authenticate with an Azure AD identity by using system-assigned or user-assigned managed identity	2.1.0+
Active Directory Default	Authenticate 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+

Benefits

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.

CREATE LOGIN Login_Name { FROM EXTERNAL PROVIDER | WITH <option_list> [,..] }  

<option_list> ::=      
    PASSWORD = {'password'}   
    | , SID = sid, ]

CREATE LOGIN Login_Name { FROM EXTERNAL PROVIDER | WITH <option_list> [,..] }  

<option_list> ::=     

    PASSWORD = {'password'}  

    | , SID = sid, ]

Then create a user syntax

CREATE USER user_name FROM LOGIN login_name

1	CREATE USER user_name FROM LOGIN login_name

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

ALTER LOGIN login_name DISABLE

1	ALTER LOGIN login_name DISABLE

How make the connection from code?

// Use your own server, database, app ID, and secret.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Service Principal; Encrypt=True; Database=testdb; User Id=AppId; Password=secret";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}

// Use your own server, database, app ID, and secret.

string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Service Principal; Encrypt=True; Database=testdb; User Id=AppId; Password=secret";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {

conn.Open();

}

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

Benefits

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>.database.windows.net)

How to set it up?

Open the control plane from https://portal.azure.com/#view/HubsExtension/BrowseResource/resourceType/Microsoft.Sql%2Fservers

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

See more on how the rules are applied.

IP firewall rules – Azure SQL Database and Azure Synapse Analytics

Configure server-level IP firewall rules for a database in Azure SQL Database or Azure Synapse Analytics firewall. Manage access and configure database-level IP firewall rules for SQL Database.

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?

// Use your own server, database, and user ID.
// User ID is optional.
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb; User Id=user@domain.com";

using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
    conn.Open();
}

// User ID is not provided.
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb";

using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
    conn.Open();
}

// Use your own server, database, and user ID.

// User ID is optional.

string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb; User Id=user@domain.com";

using (SqlConnection conn = new SqlConnection(ConnectionString1)) {

conn.Open();

}

// User ID is not provided.

string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb";

using (SqlConnection conn = new SqlConnection(ConnectionString2)) {

conn.Open();

}

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.

Benefits

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”

Permissions

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

#Enable with Azure CLI
az sql server ad-only-auth enable --resource-group myresource --name myserver

#Disable with Azure CLI
az sql server ad-only-auth disable --resource-group myresource --name myserver

#check if enabled with T-SQL
SELECT SERVERPROPERTY('IsExternalAuthenticationOnly')

#Enable with Azure CLI

az sql server ad-only-auth enable --resource-group myresource --name myserver

#Disable with Azure CLI

az sql server ad-only-auth disable --resource-group myresource --name myserver

#check if enabled with T-SQL

SELECT SERVERPROPERTY('IsExternalAuthenticationOnly')

Defender for Databases

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

Enable database protection for your subscription

Learn how to enable Microsoft Defender for Cloud for all of your database types for your entire subscription.

And more information here

Microsoft Defender for Azure SQL – the benefits and features

Learn how Microsoft Defender for Azure SQL protects your Azure SQL databases.

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

Closure

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.

Accessing Azure SQL with different methods

First answer

First option – Allow login from trusted (named) locations

Benefits

How to setup?

Second option – Access SQL with Service principals

Benefits

How to setup?

How make the connection from code?

Some notes on what is not supported

Third option – Access SQL with user having MFA

Benefits

What you need first?

How to set it up?

How to connect from code?

What the user will see?

Universal Authentication (MFA) limitations

Fourth option – Azure AD only

Benefits

How to setup?

AZ CLI and Transact-SQL

Defender for Databases

Closure

Related

Leave a Reply Cancel reply

Subscribe with RSS

Twitter

Archives

First answer

First option – Allow login from trusted (named) locations

Benefits

How to setup?

Second option – Access SQL with Service principals

Benefits

How to setup?

How make the connection from code?

Some notes on what is not supported

Third option – Access SQL with user having MFA

Benefits

What you need first?

How to set it up?

How to connect from code?

What the user will see?

Universal Authentication (MFA) limitations

Fourth option – Azure AD only

Benefits

How to setup?

AZ CLI and Transact-SQL

Defender for Databases

Closure

Share this:

Related

Leave a Reply Cancel reply

Subscribe with RSS

Categories

Twitter

Archives

Discover more from Set-AzWebApp -name "Anything Microsoft and other stuff on the side"