CODEWITHSUNDEEP
azure-managed-identityazure-synapse
GettingItDone
GettingItDone

Reputation: 603

Cannot access ADLS from Synapse Analytics

I have;

  1. A master key.
  2. A database scoped credential.
  3. An external data source.
  4. File format.
  5. A user created managed identity for Azure SQL Server.
  6. RBAC contributor permission for that managed identity on my ADLS storage account.

When I execute the code to create an external table this error is raised:

Failed to obtain the credential secret for the provided database and credential. Database Name = DWShellDb, Credential Name = devtechnicalpractice1_AdlsManagedIdentity, Exception = System.Data.SqlClient.SqlException (0x80131904): Fail to obtain or decrypt secret for credential 'devtechnicalpractice1_AdlsManagedIdentity'.

This is the code:

CREATE DATABASE SCOPED CREDENTIAL credname
WITH 
    IDENTITY = 'exact_mi_name'
;

CREATE EXTERNAL DATA SOURCE datasourcename
WITH
  ( LOCATION = 'abfss://[email protected]' ,
    CREDENTIAL = credname ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL FILE FORMAT Header_CSV1
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 1, 
          USE_TYPE_DEFAULT = True)
) ;


CREATE EXTERNAL TABLE [dbo].[tablename]
( [field1] VARCHAR(250) NULL,
  [field2] VARCHAR(250) NULL,
  [field3] VARCHAR(250) NULL )
WITH
(
    LOCATION='/Countries/' ,
    DATA_SOURCE = datasourcename,
    FILE_FORMAT = Header_CSV1 ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
) ;

I do not think the error lies within the TSQL but is security permission.

Upvotes: 0

Views: 2285

Answers (1)

Mahesh Valikar
Mahesh Valikar

Reputation: 11

I had same issue. Later I found out that I was missing secret while creating scoped credentials. It should be something like this:

CREATE DATABASE SCOPED CREDENTIAL credname
WITH
    IDENTITY = 'exact_mi_name',
    SECRET = 'adfNSkGPaTuryRdqt9sPAnjPW8xEzM7kQIYt' // data lake key
;

Upvotes: 1

Related Questions