Error when using Enable staging in data flows Azure data factory

Question

Error when using Enable staging in data flows Azure data factory

Damodaran, Arun 0

Error

"Operation on target Data flow1 failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink The specified schema name either does not exist or you do not have permission to use it.","Details" :"shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: The specified schema name either does not exist or you do not have permission to use it.shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1673)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)\n\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:26"}"

We are using managed identity and enabling Enable staging in data flows which caused the error in first place.

If we are using access keys or disabling staging the error goes away.

Managed Identity (MI) is part of Azure AD group and the AD group has DBO permissions in database.

MI has blob contributor access to the storage account specified for staging

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-11-28T07:29:09.14+00:00

Hi Damodaran, Arun ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful' . In case your issue is still not resolved, kindly let us know .
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-01T06:59:59.8+00:00

Hi Damodaran, Arun ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly accept the below answer in case it was helpful. Thankyou.
Damodaran, Arun 0 Reputation points

2023-12-01T14:56:06.3866667+00:00

Hi, basically it did not help. I wanted to throw out one more information we are using a the old SQL data warehouse resource not a synapse dedicated SQL pool actually. It is a name sake synapse dedicated sql pool now.

Also the error was on the schema with name <service_principal>@<tenant_id> and the data flow could not connect to it.

When we use a service principal authetification the above schema appears to get created automatically but when we use the managed identity we get the error above. This makes me think and question if managed identities are fully supported for the old SQL data warehouse resources.
Damodaran, Arun 0 Reputation points

2023-12-01T14:56:48.7166667+00:00

Hi, basically it did not help. I wanted to throw out one more information we are using a the old SQL data warehouse resource not a synapse dedicated SQL pool actually. It is a name sake synapse dedicated sql pool now.

Also the error was on the schema with name <service_principal>@<tenant_id> and the data flow could not connect to it.

When we use a service principal authetification the above schema appears to get created automatically but when we use the managed identity we get the error above. This makes me think and question if managed identities are fully supported for the old SQL data warehouse resources.

3 answers

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-11-28T07:29:09.14+00:00

Hi Damodaran, Arun ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful' . In case your issue is still not resolved, kindly let us know .
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-12-01T06:59:59.8+00:00

Hi Damodaran, Arun ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly accept the below answer in case it was helpful. Thankyou.
Damodaran, Arun 0 Reputation points

2023-12-01T14:56:06.3866667+00:00

Hi, basically it did not help. I wanted to throw out one more information we are using a the old SQL data warehouse resource not a synapse dedicated SQL pool actually. It is a name sake synapse dedicated sql pool now.

Also the error was on the schema with name <service_principal>@<tenant_id> and the data flow could not connect to it.

When we use a service principal authetification the above schema appears to get created automatically but when we use the managed identity we get the error above. This makes me think and question if managed identities are fully supported for the old SQL data warehouse resources.
Damodaran, Arun 0 Reputation points

2023-12-01T14:56:48.7166667+00:00

Hi, basically it did not help. I wanted to throw out one more information we are using a the old SQL data warehouse resource not a synapse dedicated SQL pool actually. It is a name sake synapse dedicated sql pool now.

Also the error was on the schema with name <service_principal>@<tenant_id> and the data flow could not connect to it.

When we use a service principal authetification the above schema appears to get created automatically but when we use the managed identity we get the error above. This makes me think and question if managed identities are fully supported for the old SQL data warehouse resources.

Answer 1

Amira Bedhiafi 33,071 Volunteer Moderator

The error is related to schema permissions within your SQL Server database.

Make sure that the schema you're trying to access in your SQL Server does indeed exist.

Also since the error persists when using MI but disappears when using access keys or disabling staging, I think that there might be a permissions issue related to the MI. Even if it is part of an Azure AD group with DBO permissions in the database, try to verify.

Damodaran, Arun 0 Reputation points

2023-11-21T21:39:42.1266667+00:00

The schema name as shown in the error message is a GUID. I am able to preview the data but when it runs from the pipeline it gives the error
Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2023-11-22T15:56:21.4066667+00:00

The fact that a GUID is being used as a schema name is unusual. Typically, schema names are more descriptive and human-readable. It's important to understand why and how this GUID is being used as a schema name. Is it dynamically generated? Is it part of some automated process? Understanding this will help in diagnosing the issue.
Damodaran, Arun 0 Reputation points

2023-11-22T20:21:10.06+00:00

I cannot find a schema name with GUID. All our schemas are descriptive as you said
Amira Bedhiafi 33,071 Reputation points Volunteer Moderator

2023-11-23T10:54:43.42+00:00

I am able to preview the data but when it runs from the pipeline it gives the error

This can be a permission issue

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

@Damodaran, Arun ,

Yes managed identity might not be supporting old sql DW.

Kindly Change the authentication method of the linked service to key or service principal auth.

If you have an Azure Synapse workspace that was created prior to December 7, 2020, you may run into a similar error message when authenticating using Managed Identity: com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.

Follow these steps to work around this issue by re-registering the workspace's managed identity:

Install Azure PowerShell. Refer to Install PowerShell.
Register your workspace's managed identity using PowerShell:PowerShellCopy

Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-S

Kindly refer these articles:

https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/adf-to-synapse-pool-please-enable-managed-service-identity-and/ba-p/1817785

https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest#are-there-any-known-issues-with-the-copy-statement

Share via

Error when using Enable staging in data flows Azure data factory

3 answers

Your answer