Skip to content

authentication-azure-ad-only-authentication-tutorial.md

Latest commit

 

History

History
428 lines (281 loc) · 22.6 KB

authentication-azure-ad-only-authentication-tutorial.md

File metadata and controls

428 lines (281 loc) · 22.6 KB
title Enable Microsoft Entra-Only Authentication
titleSuffix Azure SQL Database & Azure SQL Managed Instance
description This article guides you through enabling the Microsoft Entra-only authentication feature with Azure SQL Database and Azure SQL Managed Instance
author VanMSFT
ms.author vanto
ms.reviewer wiassaf, vanto, mathoma
ms.date 06/10/2025
ms.service azure-sql
ms.subservice security
ms.topic tutorial
ms.custom
devx-track-azurepowershell
devx-track-azurecli
monikerRange =azuresql || =azuresql-db || =azuresql-mi

Tutorial: Enable Microsoft Entra-only authentication with Azure SQL

[!INCLUDEappliesto-sqldb-sqlmi]

This article guides you through enabling the Microsoft Entra-only authentication with Azure SQL feature within Azure SQL Database and Azure SQL Managed Instance. If you are looking to provision a SQL Database or SQL Managed Instance with Microsoft Entra-only authentication enabled, see Create server with Microsoft Entra-only authentication enabled in Azure SQL.

[!INCLUDE entra-id]

In this tutorial, you learn how to:

[!div class="checklist"]

  • Assign role to enable Microsoft Entra-only authentication
  • Enable Microsoft Entra-only authentication using the Azure portal, Azure CLI, or PowerShell
  • Check whether Microsoft Entra-only authentication is enabled
  • Test connecting to Azure SQL
  • Disable Microsoft Entra-only authentication using the Azure portal, Azure CLI, or PowerShell

Prerequisites

Assign role to enable Microsoft Entra-only authentication

In order to enable or disable Microsoft Entra-only authentication, selected built-in roles are required for the Microsoft Entra users executing these operations in this tutorial. We're going to assign the SQL Security Manager role to the user in this tutorial.

For more information on how to assign a role to a Microsoft Entra account, see Assign administrator and non-administrator roles to users with Microsoft Entra ID

For more information on the required permission to enable or disable Microsoft Entra-only authentication, see the Permissions section of Microsoft Entra-only authentication article.

  1. In our example, we'll assign the SQL Security Manager role to the user UserSqlSecurityManager@contoso.onmicrosoft.com. Using privileged user that can assign Microsoft Entra roles, sign into the Azure portal.

  2. Go to your SQL server resource, and select Access control (IAM) in the menu. Select the Add button and then Add role assignment in the dropdown list.

    :::image type="content" source="media/authentication-azure-ad-only-authentication-tutorial/azure-ad-only-authentication-access-control.png" alt-text="Screenshot shows the Access control page where you can add a role assignment.":::

  3. In the Add role assignment pane, select the Role SQL Security Manager, and select the user that you want to have the ability to enable or disable Microsoft Entra-only authentication.

    :::image type="content" source="media/authentication-azure-ad-only-authentication-tutorial/azure-ad-only-authentication-access-control-add-role.png" alt-text="Screenshot from the Azure portal of the Add role assignment panel.":::

  4. Select Save.

Enable Microsoft Entra-only authentication

Portal

Enable in SQL Database using Azure portal

To enable Microsoft Entra-only authentication in the Azure portal, follow these steps:

  1. Using the user with the SQL Security Manager role, go to the Azure portal.

  2. Go to your SQL server resource, and select Microsoft Entra ID under the Settings menu.

    :::image type="content" source="media/authentication-azure-ad-only-authentication-tutorial/azure-ad-only-authentication-portal.png" alt-text="Screenshot shows the option to support only Microsoft Entra authentication for the server." lightbox="media/authentication-azure-ad-only-authentication-tutorial/azure-ad-only-authentication-portal.png":::

  3. If you haven't added an Microsoft Entra admin, you'll need to set this before you can enable Microsoft Entra-only authentication.

  4. Check the box for Support only Microsoft Entra authentication for this server.

  5. The Enable Microsoft Entra-only authentication popup will show. Select Yes to enable the feature and Save the setting.

Enable in SQL Managed Instance using Azure portal

To enable Microsoft Entra-only authentication in the Azure portal, see the steps below.

  1. Using the user with the SQL Security Manager role, go to the Azure portal.

  2. Go to your SQL managed instance resource, and select Microsoft Entra admin under the Settings menu.

  3. If you haven't added an Microsoft Entra admin, you'll need to set this before you can enable Microsoft Entra-only authentication.

  4. Select the Support only Microsoft Entra authentication for this managed instance checkbox.

  5. The Enable Microsoft Entra-only authentication popup will show. Select Yes to enable the feature and Save the setting.

The Azure CLI

Enable in SQL Database using Azure CLI

To enable Microsoft Entra-only authentication in Azure SQL Database using Azure CLI, see the commands below. Install the latest version of Azure CLI. You must have Azure CLI version 2.14.2 or higher. For more information on these commands, see az sql server ad-only-auth.

For more information on managing Microsoft Entra-only authentication using APIs, see Managing Microsoft Entra-only authentication using APIs.

Note

The Microsoft Entra admin must be set for the server before enabling Microsoft Entra-only authentication. Otherwise, the Azure CLI command will fail.

For permissions and actions required of the user performing these commands to enable Microsoft Entra-only authentication, see the Microsoft Entra-only authentication article.

  1. Sign into Azure using the account with the SQL Security Manager role.

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

Enable in SQL Managed Instance using Azure CLI

To enable Microsoft Entra-only authentication in Azure SQL Managed Instance using Azure CLI, see the commands below. Install the latest version of Azure CLI.

  1. Sign into Azure using the account with the SQL Security Manager role.

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

PowerShell

Enable in SQL Database using PowerShell

To enable Microsoft Entra-only authentication in Azure SQL Database using PowerShell, see the commands below. Az.Sql 2.10.0 module or higher is required to execute these commands. For more information on these commands, see Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication.

For more information on managing Microsoft Entra-only authentication using APIs, see Managing Microsoft Entra-only authentication using APIs

Note

The Microsoft Entra admin must be set for the server before enabling Microsoft Entra-only authentication. Otherwise, the PowerShell command will fail.

For permissions and actions required of the user performing these commands to enable Microsoft Entra-only authentication, see the Microsoft Entra-only authentication article. If the user has insufficient permissions, you will get the following error:

Enable-AzSqlServerActiveDirectoryOnlyAuthentication : The client
'UserSqlServerContributor@contoso.onmicrosoft.com' with object id
'<guid>' does not have authorization to perform
action 'Microsoft.Sql/servers/azureADOnlyAuthentications/write' over scope
'/subscriptions/<guid>...'

  1. Sign into Azure using the account with the SQL Security Manager role.

    Connect-AzAccount

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

    Enable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName <myserver>  -ResourceGroupName <myresource>

Enable in SQL Managed Instance using PowerShell

To enable Microsoft Entra-only authentication in Azure SQL Managed Instance using PowerShell, see the commands below. Az.Sql 2.10.0 module or higher is required to execute these commands.

For more information on managing Microsoft Entra-only authentication using APIs, see Managing Microsoft Entra-only authentication using APIs.

  1. Sign into Azure using the account with the SQL Security Manager role.

    Connect-AzAccount

  2. Run the following command, replacing <myinstance> with your SQL Managed Instance name, and <myresource> with your Azure Resource that holds the SQL managed instance.

    Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName <myinstance> -ResourceGroupName <myresource>

Check the Microsoft Entra-only authentication status

Check whether Microsoft Entra-only authentication is enabled for your server or instance.

Portal

Check status in SQL Database

Go to your SQL server resource in the Azure portal. Select Microsoft Entra ID under the Settings menu.

Check status in SQL Managed Instance

Go to your SQL managed instance resource in the Azure portal. Select Microsoft Entra admin under the Settings menu.

The Azure CLI

These commands can be used to check whether Microsoft Entra-only authentication is enabled for your logical server for Azure SQL Database, or SQL Managed Instance. Members of the SQL Server Contributor and SQL Managed Instance Contributor roles can use these commands to check the status of Microsoft Entra-only authentication, but can't enable or disable the feature.

Check status in SQL Database

  1. Sign into Azure using the account with the SQL Security Manager role. For more information on managing Microsoft Entra-only authentication using APIs, see Managing Microsoft Entra-only authentication using APIs

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

  3. You should see the following output:

    {
 "azureAdOnlyAuthentication": true,
 "/subscriptions/<guid>/resourceGroups/mygroup/providers/Microsoft.Sql/servers/myserver/azureADOnlyAuthentications/Default",
 "name": "Default",
 "resourceGroup": "myresource",
 "type": "Microsoft.Sql/servers"
}

Check status in SQL Managed Instance

  1. Sign into Azure using the account with the SQL Security Manager role.

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

  3. You should see the following output:

    {
 "azureAdOnlyAuthentication": true,
 "id": "/subscriptions/<guid>/resourceGroups/myresource/providers/Microsoft.Sql/managedInstances/myinstance/azureADOnlyAuthentications/Default",
 "name": "Default",
 "resourceGroup": "myresource",
 "type": "Microsoft.Sql/managedInstances"
}

PowerShell

These commands can be used to check whether Microsoft Entra-only authentication is enabled for your logical server for Azure SQL Database, or SQL Managed Instance. Members of the SQL Server Contributor and SQL Managed Instance Contributor roles can use these commands to check the status of Microsoft Entra-only authentication, but can't enable or disable the feature.

The status will return True if the feature is enabled, and False if disabled.

Check status in SQL Database

  1. Sign into Azure using the account with the SQL Security Manager role. For more information on managing Microsoft Entra-only authentication using APIs, see Managing Microsoft Entra-only authentication using APIs

    Connect-AzAccount

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

    Get-AzSqlServerActiveDirectoryOnlyAuthentication  -ServerName <myserver> -ResourceGroupName <myresource>

Check status in SQL Managed Instance

  1. Sign into Azure using the account with the SQL Security Manager role.

    Connect-AzAccount

  2. Run the following command, replacing <myinstance> with your SQL Managed Instance name, and <myresource> with your Azure Resource that holds the SQL managed instance.

    Get-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName <myinstance> -ResourceGroupName <myresource>

Test SQL authentication with connection failure

After enabling Microsoft Entra-only authentication, test with SQL Server Management Studio (SSMS) to connect to your SQL Database or SQL Managed Instance. Use SQL authentication for the connection.

You should see a login failed message similar to the following output:

Cannot connect to <myserver>.database.windows.net.
Additional information:
  Login failed for user 'username'. Reason: Azure Active Directory only authentication is enabled.
  Please contact your system administrator. (Microsoft SQL Server, Error: 18456)

Disable Microsoft Entra-only authentication

By disabling the Microsoft Entra-only authentication feature, you allow both SQL authentication and Microsoft Entra authentication for Azure SQL.

Portal

Disable in SQL Database using Azure portal

  1. Using the user with the SQL Security Manager role, go to the Azure portal.
  2. Go to your SQL server resource, and select Microsoft Entra ID under the Settings menu.
  3. To disable the Microsoft Entra-only authentication feature, uncheck the Support only Microsoft Entra authentication for this server checkbox and Save the setting.

Disable in SQL Managed Instance using Azure portal

  1. Using the user with the SQL Security Manager role, go to the Azure portal.
  2. Go to your SQL managed instance resource, and select Active Directory admin under the Settings menu.
  3. To disable the Microsoft Entra-only authentication feature, uncheck the Support only Microsoft Entra authentication for this managed instance checkbox and Save the setting.

The Azure CLI

Disable in SQL Database using Azure CLI

To disable Microsoft Entra-only authentication in Azure SQL Database using Azure CLI, see the commands below.

  1. Sign into Azure using the account with the SQL Security Manager role.

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

  3. After disabling Microsoft Entra-only authentication, you should see the following output when you check the status:

    {
 "azureAdOnlyAuthentication": false,
 "/subscriptions/<guid>/resourceGroups/mygroup/providers/Microsoft.Sql/servers/myserver/azureADOnlyAuthentications/Default",
 "name": "Default",
 "resourceGroup": "myresource",
 "type": "Microsoft.Sql/servers"
}

Disable in SQL Managed Instance using Azure CLI

To disable Microsoft Entra-only authentication in Azure SQL Managed Instance using Azure CLI, see the commands below.

  1. Sign into Azure using the account with the SQL Security Manager role.

    az login

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

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

  3. After disabling Microsoft Entra-only authentication, you should see the following output when you check the status:

    {
 "azureAdOnlyAuthentication": false,
 "id": "/subscriptions/<guid>/resourceGroups/myresource/providers/Microsoft.Sql/managedInstances/myinstance/azureADOnlyAuthentications/Default",
 "name": "Default",
 "resourceGroup": "myresource",
 "type": "Microsoft.Sql/managedInstances"
}

PowerShell

Disable in SQL Database using PowerShell

To disable Microsoft Entra-only authentication in Azure SQL Database using PowerShell, see the commands below.

  1. Sign into Azure using the account with the SQL Security Manager role.

    Connect-AzAccount

  2. Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.

    Disable-AzSqlServerActiveDirectoryOnlyAuthentication -ServerName <myserver>  -ResourceGroupName <myresource>

Disable in SQL Managed Instance using PowerShell

To disable Microsoft Entra-only authentication in Azure SQL Managed Instance using PowerShell, see the commands below.

  1. Sign into Azure using the account with the SQL Security Manager role.

    Connect-AzAccount

  2. Run the following command, replacing <myinstance> with your SQL Managed Instance name, and <myresource> with your Azure Resource that holds the managed instance.

    Disable-AzSqlInstanceActiveDirectoryOnlyAuthentication -InstanceName <myinstance> -ResourceGroupName <myresource>

Test connecting to Azure SQL again

After disabling Microsoft Entra-only authentication, test connecting using a SQL authentication login. You should now be able to connect to your server or instance.

Related content