Edit

Share via


Connect to Azure storage with managed identity from PolyBase

Beginning with SQL Server 2025 (17.x) Preview you can use managed identities to access:

  • Microsoft Azure Blob Storage
  • Microsoft Azure Data Lake

Prerequisites

Update the registry

Warning

Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend you back up any valued data on the computer.

Update the registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\FederatedAuthentication. Add the following entries for your data storage types.

Entry Value
AADDataLakeEndPoint datalake.azure.net
AADAzureStorageEndPoint storage.azure.com

These keys are in addition to the registry keys required as described in Managed identity (preview) for SQL Server enabled by Azure Arc.

Create database scoped credentials

Add a database scoped credential for managed identity.

  1. Allow server scoped database credentials. Run the following command.

    EXECUTE sp_configure 'allow server scoped db credentials',1;
    GO
    RECONFIGURE;
    
  2. Create a database scoped credential. The example uses the name managed_id.

    CREATE DATABASE SCOPED CREDENTIAL [managed_id]
    WITH IDENTITY = 'Managed Identity';
    

Create external data source

Create the external data source.

External data source Connector location prefix Location path Supported locations by product / service Authentication
Azure Storage Account (V2) abs abs://<container_name>@<storage_account_name>.blob.core.windows.net/
or
abs://<storage_account_name>.blob.core.windows.net/<container_name>
- SQL Server 2022 (16.x): Hierarchical Namespace supported
- SQL Server 2025 (17.x) Preview enabled by Azure Arc
Shared access signature (SAS)
or
Managed Identity
Azure Data Lake Storage adls adls://<container_name>@<storage_account_name>.dfs.core.windows.net/
or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>
- SQL Server 2022 (16.x)
- SQL Server 2025 (17.x) Preview enabled by Azure Arc
Shared access signature (SAS)
or
Managed Identity

Query a parquet file in Azure Blob Storage

SQL Server 2025 (17.x) Preview supports for Manage Identity through Azure Arc. For instructions, review Managed identity (preview) for SQL Server enabled by Azure Arc.

The following example queries a parquet file in Azure Blob Storage

EXECUTE sp_configure 'allow server scoped db credentials', 1;
RECONFIGURE;
GO

CREATE DATABASE SCOPED CREDENTIAL [managed_id]
WITH IDENTITY = 'Managed Identity';

CREATE EXTERNAL DATA SOURCE [my_external_data_source]
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = managed_id
);

Errors and solutions

SQL Server 2025 (17.x) Preview enabled by Azure Arc fails to authenticate using Managed Identity.

To use managed identity, SQL Server 2025 (17.x) Preview must be enabled by Azure Arc. For instructions on how to enable by Azure Arc, review Managed identity (preview) for SQL Server enabled by Azure Arc.

Enable sp_configure 'allow server scoped db credentials'.

If any of the following are true, the PolyBase query fails:

  • The SQL Server instance isn't properly configured for Azure Arc
  • Registry entries are missing
  • allow server scoped db credentials is disabled

The query will return one of the following errors when trying to access Azure Blob Storage or Azure Data Lake:

Msg 16562, Level 16, State 1, Line 79
External table <name> is not accessible because location does not exist or it is used by another process.

Or

Msg 16562, Level 16, State 1, Line 79
External table <name> is not accessible because location does not exist or it is used by another process.