Upravit

Sdílet prostřednictvím

Improvement: Microsoft Entra managed identity support for backup and restore database operations and for EKM with AKV in SQL Server on Azure VMs

  • Článek
  • Platí pro:
    SQL Server 2022 on Azure VM (Windows only)

Symptoms

The SQL Server instance might stop responding if you run a backup or restore database operation to Azure Storage or use Extensible Key Management (EKM) with Azure Key Vault (AKV) using server-level credentials with managed identities. Although this operation isn't supported for SQL Server 2022, the current T-SQL syntax allows it.

Resolution

This problem is fixed in the following cumulative update for SQL Server on Azure virtual machines (VMs):

Cumulative Update 17 for SQL Server 2022

In the SQL Server 2022 CU17 and later versions, the Microsoft Entra managed identity authentication with SQL Server on Azure Windows only VMs supports the server-level credential for database backup and restore operations to Azure Storage and for EKM with AKV.

To enable the managed identity support for backup or restore database operations for SQL Server on Azure Windows VMs, the following steps are required:

  1. Assign the primary managed identity for the SQL Server on Azure Windows VMs.
  2. Create or use Azure Storage with a blob container.
  3. Assign role-based access control (RBAC) roles for the primary managed identity to access the Azure Storage.
  4. Run the T-SQL command CREATE CREDENTIAL with the WITH IDENTITY = 'Managed Identity' clause using the Azure Storage URL as a credential name.
  5. Run the T-SQL command BACKUP DATABASE or RESTORE DATABASE using the Azure Storage URL.
-- Create credential with managed identity and credential name set to
-- URL= https://<storageaccountname>.blob.core.windows.net/<container>   
CREATE CREDENTIAL [https://<storageaccountname>.blob.core.windows.net/<container>]  
 WITH IDENTITY = 'Managed Identity'  
-- Backup the database mydb to URL 
BACKUP DATABASE mydb 
 TO URL = 'https://<storageaccount>.blob.core.windows.net/<container>/mydb.bak'  
-- Restore the database mydb1 from URL
RESTORE DATABASE mydb1  
 FROM URL ='https://<storageaccount>.blob.core.windows.net/<container>/mydb.bak' 

For more information, see Backup and restore to URL using managed identities.

To enable the managed identity support for EKM with AKV for SQL Server on Azure Windows VMs, the following steps are required:

  1. Assign the primary managed identity for the SQL Server on Azure Windows VMs.
  2. Create or use a key vault.
  3. Assign role-based access control (RBAC) roles for the primary managed identity to access the AKV.
  4. Download the latest SQL Server Connector for Microsoft Azure Key Vault (the 1.0.5.0 (November 2024) or later versions). The latest version of the SQL Server Connector is required to support the managed identity.
  5. Run the T-SQL command CREATE CREDENTIAL with the WITH IDENTITY = 'Managed Identity' clause using the AKV path as a credential name.
-- Create credential with managed identity and the credential name for the AKV called 'contoso'
-- with the AKV path = 'contoso.vault.azure.net'  
CREATE CREDENTIAL [contoso.vault.azure.net]  
 WITH IDENTITY = 'Managed Identity'  
 FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
  1. Run the rest of the T-SQL setup of EKM with AKV.

For more information, see Managed Identity Support for Extensible Key Management with Azure Key Vault.

Note

  • Trace flag 4675, disabled by default, allows troubleshooting the server-level credential and can be used to confirm the primary managed identity that is assigned to the SQL Server instance.
  • The managed identity support doesn't introduce any T-SQL syntax or system view changes, since this functionality already exists for Azure SQL Managed Instance.
  • Managed identities aren't supported for the server-level credential on SQL Server on-premises. When accidentally used with backup or restore to Azure Storage or the EKM with AKV, you see the error message "The primary managed identity is not set for the server..." and are referred to the public documentation.

About cumulative updates for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for your version of SQL Server:

Latest cumulative update for SQL Server 2022

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References