How to find SQL servers allowing SQL authentication in Azure

Before you start

Use an Azure account with read access to the subscriptions that hold the servers, such as one with the built-in Reader role. Members of the SQL Server Contributor role can also check the setting, though they can't change it. The script needs the Az.Sql module, version 2.10.0 or later. Microsoft lists no separate licence for reading the setting.

Know what the check covers: logical servers for Azure SQL Database. Azure SQL Managed Instance has the same setting on each instance, read with its own cmdlet, and SQL Server on a virtual machine is managed inside the machine. The examples read and export, and change nothing. The script's finally block disconnects the account and clears its saved Azure contexts.

Option 1

Find SQL servers allowing SQL authentication with PowerShell

Install the modules once. The script lists the SQL servers in each enabled subscription with their Microsoft Entra admin details, which carry the Entra-only setting, and saves every server with the ones allowing SQL sign-in first.

Install-Module -Name Az.Accounts, Az.Sql -Repository PSGallery -Scope CurrentUser

$ErrorActionPreference = 'Stop'
Connect-AzAccount -ErrorAction Stop
try {
    $subscriptions = @(Get-AzSubscription | Where-Object State -eq 'Enabled')
    if ($subscriptions.Count -eq 0) {
        Write-Output 'No enabled subscriptions are available to this account.'
        return
    }
    $rows = @(foreach ($sub in $subscriptions) {
        Set-AzContext -Subscription $sub.Id -Tenant $sub.TenantId -Scope Process | Out-Null
        $servers = @(Get-AzSqlServer -ExpandActiveDirectoryAdministrator)
        foreach ($server in $servers) {
            $admin = $server.Administrators
            [pscustomobject]@{
                Server              = $server.FullyQualifiedDomainName
                Subscription        = $sub.Name
                ResourceGroup       = $server.ResourceGroupName
                EntraOnly           = [bool]$admin.AzureADOnlyAuthentication
                EntraAdmin          = if ($admin.Login) { $admin.Login } else { 'None' }
                SqlAdminLogin       = $server.SqlAdministratorLogin
                PublicNetworkAccess = $server.PublicNetworkAccess
            }
        }
    })
    if ($rows.Count -eq 0) {
        Write-Output 'No SQL servers were found in these subscriptions.'
        return
    }
    $rows | Sort-Object EntraOnly, Server |
        Export-Csv -Path './sql-authentication.csv' -NoTypeInformation -Encoding utf8
    $allowed = @($rows | Where-Object { -not $_.EntraOnly })
    Write-Output "$($rows.Count) servers saved to sql-authentication.csv."
    Write-Output "$($allowed.Count) still allow SQL authentication."
} finally {
    Disconnect-AzAccount | Out-Null
}

EntraOnly is False for a server that accepts SQL logins, and those servers come first. EntraAdmin says None where no Microsoft Entra admin is set: such a server can't turn Entra-only on until one is. PublicNetworkAccess says whether each can be reached over its public endpoint at all. To narrow the check, filter $subscriptions.

Check a single server

Reconnect, choose the server's subscription with Set-AzContext, then read its setting.

# Run while connected to Azure. Replace the example names.
$server = @{ ServerName = 'sql-contoso'; ResourceGroupName = 'rg-contoso' }
Get-AzSqlServerActiveDirectoryOnlyAuthentication @server

AzureADOnlyAuthentication is True when only Microsoft Entra sign-in is accepted.

Option 2

Check SQL authentication in the Azure portal

1. Sign in to the Azure portal with an account that can read the server.
2. Search for SQL servers and select the server.
3. Under Settings, select Microsoft Entra ID.
4. Read Support only Microsoft Entra authentication for this server. Ticked means SQL authentication is off; clear means SQL logins can still connect. Leave the page without saving.
5. To see every server at once, search for Policy, open Compliance and look for the built-in policy Azure SQL Database should have Microsoft Entra-only authentication enabled, if it's assigned.

The policy reports only where someone has assigned it. The portal suits a few servers; for every server in a file, use PowerShell or Altivane.

Common questions

Why does the portal show a server admin login on an Entra-only server?

Creating a server still needs SQL administrator values, so a server created with Entra-only on gets a generated name starting CloudSA and a random password. No SQL account can connect while the setting is on, the generated one included.

Does turning on Entra-only delete the SQL logins?

No. Existing SQL logins and users stay, but they can't connect. Some features don't work with it on, including elastic jobs and SQL Data Sync, so check what uses SQL logins before anyone changes it.

Where else should I look for SQL servers at risk?

A server that allows SQL sign-in and accepts every address on the internet is the one to fix first. See how to find SQL servers open to the internet in Azure.