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

Applies to: Azure SQL Database Azure SQL Managed Instance

This how-to guide outlines the steps to create a logical server for Azure SQL Database or Azure SQL Managed Instance with Azure AD-only authentication enabled during provisioning. The Azure AD-only authentication feature prevents users from connecting to the server or managed instance using SQL authentication, and only allows connection using Azure AD authentication.

Prerequisites

  • Version 2.26.1 or later is needed when using The Azure CLI. For more information on the installation and the latest version, see Install the Azure CLI.
  • Az 6.1.0 module or higher is needed when using PowerShell.
  • If you're provisioning a managed instance using the Azure CLI, PowerShell, or REST API, a virtual network and subnet needs to be created before you begin. For more information, see Create a virtual network for Azure SQL Managed Instance.

Permissions

To provision a logical server or managed instance, you'll need to have the appropriate permissions to create these resources. Azure users with higher permissions, such as subscription Owners, Contributors, Service Administrators, and Co-Administrators have the privilege to create a SQL server or managed instance. To create these resources with the least privileged Azure RBAC role, use the SQL Server Contributor role for SQL Database and SQL Managed Instance Contributor role for SQL Managed Instance.

The SQL Security Manager Azure RBAC role doesn't have enough permissions to create a server or instance with Azure AD-only authentication enabled. The SQL Security Manager role will be required to manage the Azure AD-only authentication feature after server or instance creation.

Provision with Azure AD-only authentication enabled

The following section provides you with examples and scripts on how to create a logical server or managed instance with an Azure AD admin set for the server or instance, and have Azure AD-only authentication enabled during server creation. For more information on the feature, see Azure AD-only authentication.

In our examples, we're enabling Azure AD-only authentication during server or managed instance creation, with a system assigned server admin and password. This will prevent server admin access when Azure AD-only authentication is enabled, and only allows the Azure AD admin to access the resource. It's optional to add parameters to the APIs to include your own server admin and password during server creation. However, the password can't be reset until you disable Azure AD-only authentication. An example of how to use these optional parameters to specify the server admin login name is presented in the PowerShell tab on this page.

Note

To change the existing properties after server or managed instance creation, other existing APIs should be used. For more information, see Managing Azure AD-only authentication using APIs and Configure and manage Azure AD authentication with Azure SQL.

If Azure AD-only authentication is set to false, which it is by default, a server admin and password will need to be included in all APIs during server or managed instance creation.

Azure SQL Database

  1. Browse to the Select SQL deployment option page in the Azure portal.

  2. If you aren't already signed in to Azure portal, sign in when prompted.

  3. Under SQL databases, leave Resource type set to Single database, and select Create.

  4. On the Basics tab of the Create SQL Database form, under Project details, select the desired Azure Subscription.

  5. For Resource group, select Create new, enter a name for your resource group, and select OK.

  6. For Database name, enter a name for your database.

  7. For Server, select Create new, and fill out the new server form with the following values:

    • Server name: Enter a unique server name. Server names must be globally unique for all servers in Azure, not just unique within a subscription. Enter a value, and the Azure portal will let you know if it's available or not.
    • Location: Select a location from the dropdown list
    • Authentication method: Select Use only Azure Active Directory (Azure AD) authentication.
    • Select Set admin, which brings up a menu to select an Azure AD principal as your logical server Azure AD administrator. When you're finished, use the Select button to set your admin.

    screenshot of creating a server with Azure AD-only authentication enabled

  8. Select Next: Networking at the bottom of the page.

  9. On the Networking tab, for Connectivity method, select Public endpoint.

  10. For Firewall rules, set Add current client IP address to Yes. Leave Allow Azure services and resources to access this server set to No.

  11. Leave Connection policy and Minimum TLS version settings as their default value.

  12. Select Next: Security at the bottom of the page. Configure any of the settings for Microsoft Defender for SQL, Ledger, Identity, and Transparent data encryption for your environment. You can also skip these settings.

    Note

    Using a user-assigned managed identity (UMI) is not supported with Azure AD-only authentication. Do not set the server identity in the Identity section as a UMI.

  13. Select Review + create at the bottom of the page.

  14. On the Review + create page, after reviewing, select Create.

Azure SQL Managed Instance

  1. Browse to the Select SQL deployment option page in the Azure portal.

  2. If you aren't already signed in to Azure portal, sign in when prompted.

  3. Under SQL managed instances, leave Resource type set to Single instance, and select Create.

  4. Fill out the mandatory information required on the Basics tab for Project details and Managed Instance details. This is a minimum set of information required to provision a SQL Managed Instance.

    Azure portal screenshot of the create SQL Managed Instance basic tab

    For more information on the configuration options, see Quickstart: Create an Azure SQL Managed Instance.

  5. Under Authentication, select Use only Azure Active Directory (Azure AD) authentication for the Authentication method.

  6. Select Set admin, which brings up a menu to select an Azure AD principal as your managed instance Azure AD administrator. When you're finished, use the Select button to set your admin.

    Azure portal screenshot of the create SQL Managed Instance basic tab and choosing Azure AD only authentication

  7. You can leave the rest of the settings default. For more information on the Networking, Security, or other tabs and settings, follow the guide in the article Quickstart: Create an Azure SQL Managed Instance.

  8. Once you're done with configuring your settings, select Review + create to proceed. Select Create to start provisioning the managed instance.

Grant Directory Readers permissions

Once the deployment is complete for your managed instance, you may notice that the SQL Managed Instance needs Read permissions to access Azure Active Directory. Read permissions can be granted by clicking on the displayed message in the Azure portal by a person with enough privileges. For more information, see Directory Readers role in Azure Active Directory for Azure SQL.

screenshot of the Active Directory admin menu in Azure portal showing Read permissions needed

Limitations

  • To reset the server administrator password, Azure AD-only authentication must be disabled.
  • If Azure AD-only authentication is disabled, you must create a server with a server admin and password when using all APIs.

Next steps