Edit

Share via


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.

The following ARM template sets up a Microsoft Entra admin using an existing Azure Key Vault certificate and Microsoft Entra application.

The following input parameters are used for the ARM template:

  • <machineName> - Machine name of your SQL Server host
  • <Location> - Location of your SQL Server – Azure Arc resource group, such as West US, or Central US
  • <tenantId> - The tenant ID can be found by going to the Azure portal, and going to your Microsoft Entra ID resource. In the Overview pane, you should see your Tenant ID
  • <instanceName> - SQL Server instance name. The default instance name of SQL Server is MSSQLSERVER
  • <certSubjectName> - Certificate name that you created
  • <subscriptionId> - Subscription ID. Your subscription ID can be found in the Azure portal
  • <resourceGroupName> - Resource group name that contains your key vault. The full azureKeyVaultResourceUID value can be found by going to your Key vault resource, selecting Properties and copying Resource ID
  • <keyVaultName> - Your key vault name
  • <certIdentifier> - The Certificate Identifier for your Azure Key Vault certificate. To obtain the Certificate Identifier, go to your Key vault resource, and select Certificates under Settings. Select the current version of the certificate that you created, and copy the Certificate Identifier value. For more information, see Add a certificate to Key Vault
  • <certSecret> - The Secret Identifier of your certificate, and can be found in the same menu as the Certificate Identifier
  • <applicationName> - The name of your created Microsoft Entra application
  • <appID> - The Application (client) ID of your Microsoft Entra application can be found on the Overview menu of the application
  • <adminAccountName> - Microsoft Entra admin account that you want to set for your SQL Server
  • <adminID> - The Object ID of the Microsoft Entra user or group, or the Application (client) ID of the application if you're using another application as the Microsoft Entra admin account. For more information, see Tutorial: Create Microsoft Entra users using Microsoft Entra applications
  • <adminType> - Use 0 for Microsoft Entra users and applications, and 1 for Microsoft Entra groups

Use a Custom deployment in the Azure portal, and Build your own template in the editor. Next, Save the configuration once you pasted in the example.

Note

For SQL Server on Linux host machines, replace WindowsAgent.SqlServer with LinuxAgent.SqlServer in the script.

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "resources": [
    {
      "type": "Microsoft.HybridCompute/machines/extensions",
      "apiVersion": "2022-03-10",
      "name": "<machineName>/WindowsAgent.SqlServer",
      "location": "<Location>",
      "properties": {
        "publisher": "Microsoft.AzureData",
        "type": "WindowsAgent.SqlServer",
        "settings": {
          "AzureAD": [
            {
              "tenantId": "<tenantId>", 
              "instanceName": "<instanceName>",             
              "managedCertSetting": "CUSTOMER MANAGED CERT",
              "aadCertSubjectName": "<certSubjectName>",
              "azureKeyVaultResourceUID": "/subscriptions/<subscriptionId>/resourceGroups/<resourceGroupName>/providers/Microsoft.KeyVault/vaults/<keyVaultName>",
              "azureCertUri": "<certIdentifier>",
              "azureCertSecretId": "<certSecret>",
              "managedAppSetting": "CUSTOMER MANAGED APP",
              "appRegistrationName": "<applicationName>",
              "appRegistrationSid": "<appID>",
              "adminLoginName": "<adminAccountName>",
              "adminLoginSid" : "<adminID>",
              "adminLoginType": 0
            }
          ]
        }
      }
    }
  ]
}

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