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

DavidR 40

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?

User's image

2 answers

Amira Bedhiafi 24,531 Reputation points

2024-02-08T20:18:00.1533333+00:00
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://video2.skills-academy.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql
Please sign in to rate this answer.

1 person found this answer helpful.
DavidR 40 Reputation points

2024-02-09T10:07:10.34+00:00

Thanks for your response. On the one side, I don't want the user made any changes in the target table because this table is inmutable and the table will exist before any data migration. Do I have to set any change in my configuration to force this condition? On the other side, I don't want the user to create temporary tables, can I achieve this scenario setting some option?

Amira Bedhiafi 24,531 Reputation points

2024-02-09T10:16:48.56+00:00

Can you go back to your pipeline and tell us what tasks you are performing so we can understand the temporary tables issues?

DavidR 40 Reputation points

2024-02-09T10:27:51.72+00:00

I am not creating any temporary table in any transformation and I don't want to creating any one.

The problem is in the last transformation, which is a sink transformation, in the schema I shown you it is the last box "outmysql". Also I shown you the settings for this sink transormation.

When I run the pipeline I get the 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'

I don't know why the system is trying to create this table t_7944_7bd2ad79d4354ca5bdc998aef1f5deec, my sink table is named output_table and exists previously before any execution of the job because is an operative table.

Amira Bedhiafi 24,531 Reputation points

2024-02-09T10:44:58.25+00:00

Can you share the config of the last transformation ?

DavidR 40 Reputation points

2024-02-09T10:57:26.6633333+00:00

these are the settings for the last transformation
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
AnnuKumari-MSFT 32,906 Reputation points Microsoft Employee

2024-03-07T06:14:57.8933333+00:00

@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
Please sign in to rate this answer.
DavidR 40 Reputation points

2024-03-07T06:32:09.5233333+00:00

Finally I found out the problem, adf creates a table when data is moved, it's like a staging table...then the data is moved from this staging table to the real table...and finally this new created table is deleted. I don't like this approach from Microsoft because I have to grant permissions to create/delete tables in a productive operating scheme where there are many important tables.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

2 answers

Your answer