Cannot access ADLS from Synapse Analytics

0

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://containername@storageaccountname.dfs.core.windows.net' ,
    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.

azure-managed-identity
azure-synapse

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0