Error when using Enable staging in data flows Azure data factory

Damodaran, Arun 0 Reputation points
2023-11-21T16:01:46.48+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,659 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,087 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Amira Bedhiafi 19,451 Reputation points
    2023-11-21T17:02:07.7533333+00:00

    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.

    1 person found this answer helpful.

  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

  3. AnnuKumari-MSFT 32,001 Reputation points Microsoft Employee
    2024-01-04T09:33:19.54+00:00

    @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:

    1. Install Azure PowerShell. Refer to Install PowerShell.
    2. 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

    0 comments No comments