Azure Data Factory: denied permission when trying to insert data in Azure MySql8

Question

I have a pipeline to introduce data in Azure Database for MySql from Snowflake, all seems to be ok when I go to the preview data. But when I run the Data Flow activity, the system shows this error:
Job failed due to reason: at Sink 'outmysql': CREATE command denied to user 'my_user'@'20.126.1.73' for table 't_7944_7bd2ad79d4354ca5bdc998aef1f5deec' It seems that the system is trying to create a temporary table 't_7944_7bd2ad79d4354ca5bdc998aef1f5deec', the actual name of the table is not that weird name. Could you help please?

Answer 1

This error occurs when the MySQL user has insufficient privileges in the WHMCS database to perform the schema changes that the update process requires. You need to grant permissions to the table in question, you can follow these links :

GRANT CREATE ON database_name.* TO 'my_user'@'20.126.1.73';
FLUSH PRIVILEGES;

ADF attempts to create a temporary table (t_7944_7bd2ad79d4354ca5bdc998aef1f5deec) as part of its process https://stackoverflow.com/questions/9887364/create-command-denied-to-user https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql

Answer 2

@DavidR

Could you please make sure that the IP address of the Azure Data Factory instance is added to the firewall settings for your Azure Database for MySQL instance.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou