Data Factory Error trying to use Staging Blob Storage to pull data from Azure SQL to Azure SQL Data Warehouse

1
votes

I am trying to use the Staging Blob Storage to get data to Azure SQL Data Warehouse. It is using Managed Identity. The first step is working fine when getting the data from Azure SQL DB and writing the files under the Blob Storage "Staging" Account. But when trying to add the rows in the SQL DW it is giving an error pointing to the Data Factory Managed Identity Application ID that is 98421a8f-73a4-4ef1-aa79-8a852e452b77 as described below.

The SQL DB and DW DB are not in the same SQL Server.

I have also added the Data Factory App to the DW Server by the role assignment under Access Control (IAM)...

Thanks any help/guess!

Error message:

{ "errorCode": "2200", "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The specified schema name \"98421a8f-73a4-4ef1-aa79-8a852e452b77@68a5e094-6fa7-4c99-bc18-154866e518f0\" either does not exist or you do not have permission to use it.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2760,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=2760,State=1,Message=The specified schema name \"98421a8f-73a4-4ef1-aa79-8a852e452b77@68a5e094-6fa7-4c99-bc18-154866e518f0\" either does not exist or you do not have permission to use it.,},],'", "failureType": "UserError", "target": "Copy_8jp", "details": [] }

1
azure-data-factoryazure-sql-data-warehouse

1 Answers

0
votes

Found the solution creating a new user for the Data Factory App and adding it as a owner

CREATE USER [XXXDW-DataFactory] for EXTERNAL PROVIDER

EXEC sp_addrolemember db_owner, [XXXDW-DataFactory];

GRANT CONTROL ON DATABASE::[DW] to [XXXDW-DataFactory]