Search code examples
t-sqlazure-sql-databaseazure-synapsepolybase

Azure polybase external data source creation error

I am trying to create an external table in synapse analytics, but I am facing error while creating the external data source.

Below is the code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='xxxxxxxxxxxx'; -- executed

CREATE DATABASE SCOPED CREDENTIAL storageCred WITH       -- executed
IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = 'xxxxxxxxxxxxxx';

CREATE EXTERNAL DATA SOURCE adls WITH                    -- execution failed
(  TYPE = HADOOP, 
   LOCATION = 'abfss://[email protected]', 
   CREDENTIAL = storageCred
)

error

Solution

  • The syntax looks right for the External Data Source, but the problem may be with the Database Scoped Credential. I spent a LOT of time on this, and the only way I could get this to work was with Account name and key:

    CREATE DATABASE SCOPED CREDENTIAL CausewayAdlsCredentials
WITH
 IDENTITY = '<storage_account_name>' ,
 SECRET = '<storage_account_key>'
;

    One word of warning: beware the documentation. There are several different locations that discuss this problem, and they have conflicting messaging or refer to old versions. This one is OK, but only section C worked for me.