Tutorial: Enable Azure Active Directory only authentication with Azure SQL

APPLIES TO: Azure SQL Database Azure SQL Managed Instance

Note

The Azure AD-only authentication feature discussed in this article is in public preview.

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

In this tutorial, you learn how to:

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

Prerequisites

Assign role to enable Azure AD-only authentication

In order to enable or disable Azure AD-only authentication, selected built-in roles are required for the Azure AD 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 an Azure AD account, see Assign administrator and non-administrator roles to users with Azure Active Directory

For more information on the required permission to enable or disable Azure AD-only authentication, see the Permissions section of Azure AD-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 Azure AD 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 drop-down menu.

    Access control pane in the Azure portal

  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 Azure AD-only authentication.

    Add role assignment pane in the Azure portal

  4. Click Save.

Enable Azure AD-only authentication

Enable in SQL Database using Azure portal

To enable Azure AD-only authentication auth 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 server resource, and select Azure Active Directory under the Settings menu.

    Enable Azure AD only auth menu

  3. If you haven't added an Azure Active Directory admin, you'll need to set this before you can enable Azure AD-only authentication.

  4. Select the Support only Azure Active Directory authentication for this server checkbox.

  5. The Enable Azure AD authentication only popup will show. Click Yes to enable the feature and Save the setting.

Azure SQL Managed Instance

Managing Azure AD-only authentication for SQL Managed Instance in the portal is currently not supported.

Check the Azure AD-only authentication status

Check whether Azure AD-only authentication is enabled for your server or instance.

Go to your SQL server resource in the Azure portal. Select Azure Active Directory under the Settings menu. Portal support for Azure AD-only authentication is only available for Azure SQL Database.

Test SQL authentication with connection failure

After enabling Azure AD-only authentication, test with SQL Server Management Studio (SSMS) to connect to your SQL Database or 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 Azure AD-only authentication

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

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

Managing Azure AD-only authentication for SQL Managed Instance in the portal is currently not supported.

Test connecting to Azure SQL again

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

Next steps