Data Factory Copy from another tenant - A database operation failed

Question

When I add Azure SQL Database to sink with SQL Authentication I can easily preview data, but when i try to run my debugger it fails on this error:

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object "DatahubPriceList" because it does not exist or you do not have permissions.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot find the object "DatahubPriceList" because it does not exist or you do not have permissions.,Source=.Net SqlClient Data Provider,SqlErrorNumber=1088,Class=16,ErrorCode=-2146232060,State=7,Errors=[{Class=16,Number=1088,State=7,Message=Cannot find the object "DatahubPriceList" because it does not exist or you do not have permissions.,},],

I find that very strange since I can preview the data. The issue only appears when I select AutoResolveIntegrationRuntime. If I use our on self-hosted Integration Runtime it works fine.

So maybe there is some permissions issues I dont know about regarding the AutoResolveIntegrationRuntime?

Answer 1

Can you please try the below scenarios and tell what the output is:

1. use look up activity with Self IR Azure SQL DB linked service

Select count(*) from DatahubPriceList

2) follow the same with Auto IR

and tell whether the executions are successful or not.

Since till now based on the above outputs, this is not a network issue but SQL account permission issue (it might have read access on object but not write access)
So the above 2 queries should help us narrow it down