Azure AD-only authentication with Azure SQL

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (dedicated SQL pools only)

Azure AD-only authentication is a feature within Azure SQL that allows the service to only support Azure AD authentication, and is supported for Azure SQL Database and Azure SQL Managed Instance.

Azure AD-only authentication is also available for dedicated SQL pools (formerly SQL DW) in standalone servers. Azure AD-only authentication can be enabled for the Azure Synapse workspace. For more information, see Azure AD-only authentication with Azure Synapse workspaces.

SQL authentication is disabled when enabling Azure AD-only authentication in the Azure SQL environment, including connections from SQL server administrators, logins, and users. Only users using Azure AD authentication are authorized to connect to the server or database.

Azure AD-only authentication can be enabled or disabled using the Azure portal, Azure CLI, PowerShell, or REST API. Azure AD-only authentication can also be configured during server creation with an Azure Resource Manager (ARM) template.

For more information on Azure SQL authentication, see Authentication and authorization.

Feature description

When enabling Azure AD-only authentication, SQL authentication is disabled at the server or managed instance level and prevents any authentication based on any SQL authentication credentials. SQL authentication users won't be able to connect to the logical server for Azure SQL Database or managed instance, including all of its databases. Although SQL authentication is disabled, new SQL authentication logins and users can still be created by Azure AD accounts with proper permissions. Newly created SQL authentication accounts won't be allowed to connect to the server. Enabling Azure AD-only authentication doesn't remove existing SQL authentication login and user accounts. The feature only prevents these accounts from connecting to the server, and any database created for this server.

You can also force servers to be created with Azure AD-only authentication enabled using Azure Policy. For more information, see Azure Policy for Azure AD-only authentication.

Permissions

Azure AD-only authentication can be enabled or disabled by Azure AD users who are members of high privileged Azure AD built-in roles, such as Azure subscription Owners, Contributors, and Global Administrators. Additionally, the role SQL Security Manager can also enable or disable the Azure AD-only authentication feature.

The SQL Server Contributor and SQL Managed Instance Contributor roles won't have permissions to enable or disable the Azure AD-only authentication feature. This is consistent with the Separation of Duties approach, where users who can create an Azure SQL server or create an Azure AD admin, can't enable or disable security features.

Actions required

The following actions are added to the SQL Security Manager role to allow management of the Azure AD-only authentication feature.

• 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

The above actions can also be added to a custom role to manage Azure AD-only authentication. For more information, see Create and assign a custom role in Azure Active Directory.

Managing Azure AD-only authentication using APIs

Important

The Azure AD admin must be set before enabling Azure AD-only authentication.

Azure CLI

You must have Azure CLI version 2.14.2 or higher.

name corresponds to the prefix of the server or instance name (for example, myserver) and resource-group corresponds to the resource the server belongs to (for example, myresource).

Azure SQL Database

For more information, see az sql server ad-only-auth.

Enable or disable in SQL Database

Enable

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

Disable

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

Check the status in SQL Database

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

Azure SQL Managed Instance

For more information, see az sql mi ad-only-auth.

Enable

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

Disable

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

Check the status in SQL Managed Instance

az sql mi ad-only-auth get --resource-group myresource --name myserver

PowerShell

Az.Sql 2.10.0 module or higher is required.

ServerName or InstanceName correspond to the prefix of the server name (for example, myserver or myinstance) and ResourceGroupName corresponds to the resource the server belongs to (for example, myresource).

Azure SQL Database

Enable or disable in SQL Database

Enable

For more information, see Enable-AzSqlServerActiveDirectoryOnlyAuthentication. You can also run get-help Enable-AzSqlServerActiveDirectoryOnlyAuthentication -full.

Enable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName myserver -ResourceGroupName myresource

You can also use the following command:

Get-AzSqlServer -ServerName myserver | Enable-AzSqlServerActiveDirectoryOnlyAuthentication

Disable

For more information, see Disable-AzSqlServerActiveDirectoryOnlyAuthentication.

Disable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName myserver -ResourceGroupName myresource

Check the status in SQL Database

Get-AzSqlServerActiveDirectoryOnlyAuthentication  -ServerName myserver -ResourceGroupName myresource

You can also use the following command:

Get-AzSqlServer -ServerName myserver | Get-AzSqlServerActiveDirectoryOnlyAuthentication

Azure SQL Managed Instance

Enable or disable in SQL Managed Instance

Enable

For more information, see Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication.

Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName myinstance -ResourceGroupName myresource

You can also use the following command:

Get-AzSqlInstance -InstanceName myinstance | Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication

For more information on these PowerShell commands, run get-help Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication -full.

Disable

For more information, see Disable-AzSqlInstanceActiveDirectoryOnlyAuthentication.

Disable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName myinstance -ResourceGroupName myresource

Check the status in SQL Managed Instance

Get-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName myinstance -ResourceGroupName myresource

You can also use the following command:

Get-AzSqlInstance -InstanceName myinstance | Get-AzSqlInstanceActiveDirectoryOnlyAuthentication

REST API

The following parameters will need to be defined:

• <subscriptionId> can be found by navigating to Subscriptions in the Azure portal.
• <myserver> correspond to the prefix of the server or instance name (for example, myserver).
• <myresource> corresponds to the resource the server belongs to (for example, myresource)

To use latest MSAL, download it from https://www.powershellgallery.com/packages/MSAL.PS.

$subscriptionId = '<subscriptionId>'
$serverName = "<myserver>"
$resourceGroupName = "<myresource>"

Azure SQL Database

For more information, see the Server Azure AD Only Authentications REST API documentation.

Enable or disable in SQL Database

Enable

$body = @{ properties = @{ azureADOnlyAuthentication = 1 } } | ConvertTo-Json
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method PUT -Headers $authHeader -Body $body -ContentType "application/json"

Disable

$body = @{ properties = @{ azureADOnlyAuthentication = 0 } } | ConvertTo-Json
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method PUT -Headers $authHeader -Body $body -ContentType "application/json"

Check the status in SQL Database

Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method GET -Headers $authHeader  | Format-List

Azure SQL Managed Instance

For more information, see the Managed Instance Azure AD Only Authentications REST API documentation.

Enable or disable in SQL Managed Instance

Enable

$body = @{   properties = @{  azureADOnlyAuthentication = 1 }  } | ConvertTo-Json 
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/managedInstances/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method PUT -Headers $authHeader -Body $body -ContentType "application/json"

Disable

$body = @{   properties = @{  azureADOnlyAuthentication = 0 }  } | ConvertTo-Json 
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/managedInstances/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method PUT -Headers $authHeader -Body $body -ContentType "application/json"

Check the status in SQL Managed Instance

Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/managedInstances/$serverName/azureADOnlyAuthentications/default?api-version=2020-02-02-preview" -Method GET -Headers $authHeader  | Format-List

ARM Template

• Input the Azure AD admin for the deployment. You will find the user Object ID by going to the Azure portal and navigating to your Azure Active Directory resource. Under Manage, select Users. Search for the user you want to set as the Azure AD admin for your Azure SQL server. Select the user, and under their Profile page, you will see the Object ID.
• The Tenant ID can be found in the Overview page of your Azure Active Directory resource.

Azure SQL Database

Enable

The below ARM Template enables Azure AD-only authentication in your Azure SQL Database. To disable Azure AD-only authentication, set the azureADOnlyAuthentication property to false.

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.1",
    "parameters": {
        "sqlServer_name": {
            "type": "String"
        },
        "aad_admin_name": {
            "type": "String"
        },
        "aad_admin_objectid": {
            "type": "String"
        },
        "aad_admin_tenantid": {
            "type": "String"
        }
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers/administrators",
            "apiVersion": "2020-02-02-preview",
            "name": "[concat(parameters('sqlServer_name'), '/ActiveDirectory')]",
            "properties": {
                "administratorType": "ActiveDirectory",
                "login": "[parameters('aad_admin_name')]",
                "sid": "[parameters('aad_admin_objectid')]",
                "tenantId": "[parameters('aad_admin_tenantId')]"
            }
        },        
        {
            "type": "Microsoft.Sql/servers/azureADOnlyAuthentications",
            "apiVersion": "2020-02-02-preview",
            "name": "[concat(parameters('sqlServer_name'), '/Default')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers/administrators', parameters('sqlServer_name'), 'ActiveDirectory')]"
            ],
            "properties": {
                "azureADOnlyAuthentication": true
            }
        }
    ]
}

For more information, see Microsoft.Sql servers/azureADOnlyAuthentications.

Azure SQL Managed Instance

Enable

The below ARM Template enables Azure AD-only authentication in your Azure SQL Managed Instance. To disable Azure AD-only authentication, set the azureADOnlyAuthentication property to false.

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.1",
    "parameters": {
        "instance": {
            "type": "String"
        },
        "aad_admin_name": {
            "type": "String"
        },
        "aad_admin_objectid": {
            "type": "String"
        },
        "aad_admin_tenantid": {
            "type": "String"
        }
    },
    "resources": [
        {
            "type": "Microsoft.Sql/managedInstances/administrators",
            "apiVersion": "2020-02-02-preview",
            "name": "[concat(parameters('instance'), '/ActiveDirectory')]",
            "properties": {
                "administratorType": "ActiveDirectory",
                "login": "[parameters('aad_admin_name')]",
                "sid": "[parameters('aad_admin_objectid')]",
                "tenantId": "[parameters('aad_admin_tenantId')]"
            }
        },
        {
            "type": "Microsoft.Sql/managedInstances/azureADOnlyAuthentications",
            "apiVersion": "2020-02-02-preview",
            "name": "[concat(parameters('instance'), '/Default')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/managedInstances/administrators', parameters('instance'), 'ActiveDirectory')]"
            ],
            "properties": {
                "azureADOnlyAuthentication": true
            }
        }
    ]
}

For more information, see Microsoft.Sql managedInstances/azureADOnlyAuthentications.

Checking Azure AD-only authentication using T-SQL

The SERVERPROPERTY IsExternalAuthenticationOnly has been added to check if Azure AD-only authentication is enabled for your server or managed instance. 1 indicates that the feature is enabled, and 0 represents the feature is disabled.

SELECT SERVERPROPERTY('IsExternalAuthenticationOnly') 

Remarks

• A SQL Server Contributor can set or remove an Azure AD admin, but can't set the Azure Active Directory authentication only setting. The SQL Security Manager can't set or remove an Azure AD admin, but can set the Azure Active Directory authentication only setting. Only accounts with higher Azure RBAC roles or custom roles that contain both permissions can set or remove an Azure AD admin and set the Azure Active Directory authentication only setting. One such role is the Contributor role.
• After enabling or disabling Azure Active Directory authentication only in the Azure portal, an Activity log entry can be seen in the SQL server menu.
• The Azure Active Directory authentication only setting can only be enabled or disabled by users with the right permissions if the Azure Active Directory admin is specified. If the Azure AD admin isn't set, the Azure Active Directory authentication only setting remains inactive and cannot be enabled or disabled. Using APIs to enable Azure AD-only authentication will also fail if the Azure AD admin hasn't been set.
• Changing an Azure AD admin when Azure AD-only authentication is enabled is supported for users with the appropriate permissions.
• Changing an Azure AD admin and enabling or disabling Azure AD-only authentication is allowed in the Azure portal for users with the appropriate permissions. Both operations can be completed with one Save in the Azure portal. The Azure AD admin must be set in order to enable Azure AD-only authentication.
• Removing an Azure AD admin when the Azure AD-only authentication feature is enabled isn't supported. Using an API to remove an Azure AD admin will fail if Azure AD-only authentication is enabled.
  • If the Azure Active Directory authentication only setting is enabled, the Remove admin button is inactive in the Azure portal.
• Removing an Azure AD admin and disabling the Azure Active Directory authentication only setting is allowed, but requires the right user permission to complete the operations. Both operations can be completed with one Save in the Azure portal.
• Azure AD users with proper permissions can impersonate existing SQL users.
  • Impersonation continues working between SQL authentication users even when the Azure AD-only authentication feature is enabled.

Limitations for Azure AD-only authentication in SQL Database

When Azure AD-only authentication is enabled for SQL Database, the following features aren't supported:

• Azure SQL Database server roles are supported for Azure AD server principals, but not if the Azure AD login is a group.
• Elastic jobs
• SQL Data Sync
• Change data capture (CDC) - If you create a database in Azure SQL Database as an Azure AD user and enable change data capture on it, a SQL user will not be able to disable or make changes to CDC artifacts. However, another Azure AD user will be able to enable or disable CDC on the same database. Similarly, if you create an Azure SQL Database as a SQL user, enabling or disabling CDC as an Azure AD user won't work
• Transactional replication - Since SQL authentication is required for connectivity between replication participants, when Azure AD-only authentication is enabled, transactional replication is not supported for SQL Database for scenarios where transactional replication is used to push changes made in an Azure SQL Managed Instance, on-premises SQL Server, or an Azure VM SQL Server instance to a database in Azure SQL Database
• SQL Insights (preview)
• EXEC AS statement for Azure AD group member accounts

Limitations for Azure AD-only authentication in Managed Instance

When Azure AD-only authentication is enabled for Managed Instance, the following features aren't supported:

• Transactional replication
• SQL Agent Jobs in Managed Instance supports Azure AD-only authentication. However, the Azure AD user who is a member of an Azure AD group that has access to the managed instance cannot own SQL Agent Jobs
• SQL Insights (preview)
• EXEC AS statement for Azure AD group member accounts

For more limitations, see T-SQL differences between SQL Server & Azure SQL Managed Instance.

Next steps

Tutorial: Enable Azure Active Directory only authentication with Azure SQL

Create server with Azure AD-only authentication enabled in Azure SQL