Επεξεργασία

Κοινή χρήση μέσω


Tutorial: Using automation to set up the Microsoft Entra admin for SQL Server

Applies to: SQL Server 2022 (16.x)

Note

This feature is available in SQL Server 2022 (16.x) or later versions, and is only supported for SQL Server on-premises, for Windows and Linux hosts and SQL Server 2022 on Windows Azure VMs.

In this article, we'll go over how to set up the Microsoft Entra admin to allow authentication with Microsoft Entra ID (formerly Azure Active Directory) for SQL Server using the Azure portal, and APIs such as:

  • PowerShell
  • The Azure CLI
  • ARM Template

We'll also go over the updated functionality to set up a Microsoft Entra admin for SQL Server in the Azure portal that would allow for automated certificate creation and application registration. Previously, setting up Microsoft Entra authentication for SQL Server required manual setup of Microsoft Entra admin with an Azure certificate and application registration.

Note

While Microsoft Entra ID is the new name for Azure Active Directory (Azure AD), to prevent disrupting existing environments, Azure AD still remains in some hardcoded elements such as UI fields, connection providers, error codes, and cmdlets. In this article, the two names are interchangeable.

Prerequisites

  • SQL Server 2022 (16.x) or later is installed.
  • SQL Server is connected to Azure cloud. For more information, see Connect your SQL Server to Azure Arc.
  • Microsoft Entra ID is configured for authentication in the same tenant as the Azure Arc instance.
  • An Azure Key Vault is required.

Preparation before setting the Microsoft Entra admin

The following permissions are necessary to set up Microsoft Entra admin in the SQL Server – Azure Arc and Key vault resources.

Configure permissions for Azure Arc

Follow the guide to make sure your SQL Server is connected to Azure Arc. The user setting up Microsoft Entra admin for the SQL Server – Azure Arc resource should have the Contributor role for the server.

  1. Go to the Azure portal
  2. Select SQL Server – Azure Arc, and select the instance for your SQL Server host.
  3. Select Access control (IAM).
  4. Select Add > Add role assignment to add the Contributor role to the user setting up the Microsoft Entra admin.

Configure permissions for Azure Key Vault

Create an Azure Key Vault if you don't already have one. The user setting up Microsoft Entra admin should have the Contributor role for your Azure Key Vault. To add a role to a user in Azure Key Vault:

  1. Go to the Azure portal
  2. Go to your Key vault resource.
  3. Select the Access control (IAM).
  4. Select Add > Add role assignment to add the Contributor role to the user setting up the Microsoft Entra admin.

Set access policies for the SQL Server host

  1. In the Azure portal, navigate to your Azure Key Vault instance, and select Access policies.

  2. Select Add Access Policy.

  3. For Key permissions, use Sign.

  4. For Secret permissions, select Get and List.

  5. For Certificate permissions, select Get and List.

  6. Select Next.

  7. On the Principal page, search for the name of your Machine - Azure Arc instance, which is the hostname of the SQL Server host.

    Screenshot of Azure Arc server resource in portal.

  8. Skip the Application (optional) page by selecting Next twice, or selecting Review + create.

    Screenshot of Azure portal to review and create access policy.

    Verify that the "Object ID" of the Principal matches the Principal ID of the managed identity assigned to the instance.

    Screenshot of portal control of JSON view of machine definition.

    To confirm, go to the resource page and select JSON View in the top right of the Essentials box on the Overview page. Under identity you'll find the principalId.

  9. Select Create.

You must select Create to ensure that the permissions are applied. To ensure permissions have been stored, refresh the browser window, and check that the row for your Azure Arc instance is still present.

Set access policies for Microsoft Entra users

  1. In the Azure portal, navigate to your Azure Key Vault instance, and select Access policies.
  2. Select Add Access Policy.
  3. For Key permissions, select Get, List, and Create.
  4. For Secret permissions, select Get, List, and Set.
  5. For Certificate permissions, select Get, List, and Create.
  6. For Select principal, add the Microsoft Entra user you want to use to connect to SQL Server.
  7. Select Add and then select Save.

Setting up the Microsoft Entra admin for SQL Server

New APIs and portal functionality allows users to set up a Microsoft Entra admin for SQL Server without having to separately create an Azure certificate and Microsoft Entra application. Select a tab to learn how to set up a Microsoft Entra admin for your SQL Server connected to Azure Arc with automatic certificate and application creation.

Note

The ARM template still requires the creation of an Azure Key Vault certificate and Microsoft Entra application before setting up a Microsoft Entra admin. For more information on this process, see Tutorial: Set up Microsoft Entra authentication for SQL Server.

Use the Azure portal to set up a Microsoft Entra admin, create an Azure Key Vault certificate and Microsoft Entra application in the same process. This is necessary to use Microsoft Entra authentication with SQL Server.

Note

Previously, before setting up a Microsoft Entra admin, an Azure Key Vault certificate and Microsoft Entra application registration was needed. This is no longer necessary but users can still choose to provide their own certificate and application to set up the Microsoft Entra admin.

Setting up Microsoft Entra admin using the Azure portal

  1. Go to the Azure portal, and select SQL Server – Azure Arc. Select the instance for your SQL Server host.

  2. Check the status of your SQL Server - Azure Arc resource and see if it's connected by going to the Properties menu. For more information, see Validate your Arc-enabled SQL Server resources.

  3. Select Microsoft Entra ID and Purview under Settings from the resource menu.

  4. Select Set Admin to open the Microsoft Entra ID pane, and choose an account that will be added as an admin login to SQL Server.

  5. Select Service-managed cert.

  6. Select Change key vault and select your existing Azure Key vault resource.

  7. Select Service-managed app registration.

  8. Select Save. This sends a request to the Arc server agent, which configures Microsoft Entra authentication for that SQL Server instance. The operation can take several minutes to complete; wait until the save process is confirmed with Saved successfully before attempting a Microsoft Entra login.

    The service-managed app registration does the following for you:

    • Creates a certificate in your key vault with a name in the form <hostname>-<instanceName><uniqueNumber>.
    • Creates a Microsoft Entra application with a name like <hostname>-<instanceName><uniqueNumber>, and assigns the necessary permissions to that application. For more information, see Grant application permissions
    • Assigns the new certificate in the Azure Key Vault to the application.
    • Saves these settings to Azure Arc.

    Screenshot of setting Microsoft Entra authentication with automatic certificate and application generation in the Azure portal.

Note

The certificates created for Microsoft Entra are not rotated automatically. Customers can choose to provide their own certificate and application for the Microsoft Entra admin setup. For more information, see Tutorial: Set up Microsoft Entra authentication for SQL Server.

Once the Microsoft Entra admin has been set up, using the Microsoft Entra admin credentials allows you to connect to SQL Server. However, any further database activities involving creating new Microsoft Entra logins and users will fail until admin consent is granted to the Microsoft Entra application.

Note

To grant Admin consent for the application, the account granting consent requires a role of Microsoft Entra ID Global Administrator or Privileged Role Administrator. These roles are necessary to grant admin consent for the application, but is not necessary to set up Microsoft Entra admin.

  1. In the Azure portal, select Microsoft Entra ID > App registrations, select the newly created application. The application should have a name like <hostname>-<instanceName><uniqueNumber>.

  2. Select the API permissions menu.

  3. Select Grant admin consent.

    Screenshot of application permissions in the Azure portal.

Without granting admin consent to the application, creating a Microsoft Entra login or user in SQL Server will result in the following error:

Msg 37455, Level 16, State 1, Line 2
Server identity does not have permissions to access MS Graph.

Using Microsoft Entra authentication to connect to SQL Server

Microsoft Entra authentication is now set up for your SQL Server that is connected to Azure Arc. Follow the sections after setting up Microsoft Entra admin in the article, Tutorial: Set up Microsoft Entra authentication for SQL Server to connect to SQL Server using Microsoft Entra authentication.

See also