Occured error when taking sql server as s source and Azure synapse as a target

[Megha_More]

Hi Team,

We are facing below mentioned. We took sql server as a source and Azure synapse as a target.

RetCode: SQL_ERROR  SqlState: 42000 NativeError: 15151 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot drop the EXTERNAL TABLE 'qdiuserPool.dbo.ATTREP_EXT_06c83112_3a09_324b_8a2d_5154669fddc3_1', because it does not exist or you do not have permission. Line: 1 Column: -1
Failed (retcode -1) to execute statement: 'DROP EXTERNAL TABLE [qdiuserPool].[dbo].[ATTREP_EXT_06c83112_3a09_324b_8a2d_5154669fddc3_1]'
Failed to create external table 'qdiuserPool'.'dbo'.'ATTREP_EXT_06c83112_3a09_324b_8a2d_5154669fddc3_1'
RetCode: SQL_ERROR  SqlState: 42000 NativeError: 105019 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_Connect. Java exception message:
Invalid configuration value detected for fs.azure.account.key' Line: 1 Column: -1
Failed (retcode -1) to execute statement: 'CREATE EXTERNAL TABLE [dbo].[ATTREP_EXT_06c83112_3a09_324b_8a2d_5154669fddc3_1] (  [AGENT_CODE] varchar(6), [AGENT_NAME] varchar(40), [WORKING_AREA] varchar(35), [PHONE_NO] varchar(15), [COUNTRY] varchar(25), [Email] varchar(25), [City] varchar(30) )  WITH (DATA_SOURCE = [ATTREP_DS_G2_qdiuserPool_06c83112_3a09_324b_8a2d_5154669fddc3] ,LOCATION= '///Test/Azure Synapse/1/', FILE_FORMAT = [ATTREP_FF_G2_qdiuserPool_06c83112_3a09_324b_8a2d_5154669fddc3] );'

Thanks,

Megha

[john_wang]

Hello @Megha_More ,

Thanks for you opening the article.

Is this a random error or has it never worked for you yet? please make sure the Storage permissions & Database permissions are met. As a troubleshooting option, you may try to execute the CREATE TABLE SQL manually via ODBC DSN by using a 3rd party ODBC Test Tool.

Hope this helps.

Regards,

John.

[Megha_More]

Hi @john_wang 

Thank You!! Problem Solved !

Regards,

Megha.

[john_wang]

Hello @Megha_More ,

Glad to hear that! Could you share the resolution if possible? it's helpful for all of us.

Regards,

John.

[Megha_More · Accepted solution]

Hi @john_wang ,

Sure, Firstly we checked storage and database permission.

Then ,we executed below query in SQL pool(Synapse workspace):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

To verify it's generated or not used below command: 

select * from sys.symmetric_keys where name like '%DatabaseMasterKey%'; 

Thanks & Regards,

Megha.

[john_wang]

Thank you so much for the information! @Megha_More