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@example.com' , 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.
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 ;
User contributions licensed under CC BY-SA 3.0