Create server with Microsoft Entra-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 an Azure SQL Managed Instance with Microsoft Entra-only authentication enabled during provisioning. The Microsoft Entra-only authentication feature prevents users from connecting to the server or managed instance using SQL authentication, and only allows connections authenticated with Microsoft Entra ID (formerly Azure Active Directory).

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

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 Microsoft Entra-only authentication enabled. The SQL Security Manager role will be required to manage the Microsoft Entra-only authentication feature after server or instance creation.

Provision with Microsoft Entra-only authentication enabled

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

In our examples, we're enabling Microsoft Entra-only authentication during server or managed instance creation, with a system assigned server admin and password. This will prevent server admin access when Microsoft Entra-only authentication is enabled, and only allows the Microsoft Entra 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 Microsoft Entra-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 Microsoft Entra-only authentication using APIs and Configure and manage Microsoft Entra authentication with Azure SQL.

If Microsoft Entra-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 Microsoft Entra-only authentication.
    • Select Set admin, to open the Microsoft Entra ID pane and select a Microsoft Entra principal as your logical server Microsoft Entra administrator. When you're finished, use the Select button to set your admin.

    Screenshot of creating a server with Use Microsoft Entra-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 as the server identity is supported with Microsoft Entra-only authentication. To connect to the instance as the identity, assign it to an Azure Virtual Machine and run SSMS on that VM. For production environments, using a managed identity for the Microsoft Entra admin is recommended because of the enhanced, simplified security measures with password-less authentication to Azure resources.

  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 Azure SQL Managed Instance.

  5. Under Authentication, select Use Microsoft Entra-only authentication for the Authentication method.

  6. Select Set admin to open the Microsoft Entra ID pane and select a Microsoft Entra principal as your managed instance Microsoft Entra administrator. When you're finished, use the Select button to set your admin.

    Azure portal screenshot of the create SQL Managed Instance basic tab with user Microsoft Entra-only authentication selected.

  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 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 might notice that the SQL Managed Instance needs Read permissions to access Microsoft Entra ID. Read permissions can be granted by selecting on the displayed message in the Azure portal by a person with enough privileges. For more information, see Directory Readers role in Microsoft Entra ID for Azure SQL.

Screenshot of the Microsoft Entra admin menu in Azure portal showing Read permissions needed.

Limitations

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