[SOLVED] Creating External table in Azure SQL Database using ADLS2 Datasource

Creating External table in Azure SQL Database using ADLS2 Datasource

I have a requirement where I have to create an External table from data in ADLS, in Azure SQL DB. I tried to use this configuration to create data source in SSMS:

CREATE EXTERNAL DATA SOURCE [myadlssource]
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = N'adls://<path>',
    CREDENTIAL = <credential>
);

I am getting this error message when I tried to CREATE External table: External tables are not supported with the provided data source type.

I tried using TYPE = HADOOP, but came to know it is not supported.

Is there a way to create external tables using ADLS as source? My requirement is Databricks writes to ADLS and I need to create a table on top of this ADLS which will be used by AAS and subsequently PowerBI reports. What are my options here? Earlier we were using Synapse, but we are trying to implement the solution with SQL Database.

Solution

In SQL Server it is supported (see the Select Product section and you need to specify the location in the format: "abfss://@.dfs.core.windows.net".

However, Azure SQL Database does not support creating external data sources associated with ADLS as described on this documentation.

If you are willing to switch to Azure SQL Managed Instance then creating external data sources associated with ADLS are supported as explained here. The location should be specified as "adls://@<storage_account>.dfs.core.windows.net//<file_name>".