Cloud Services

Using PolyBase in Azure Data Factory (ADF) for loading data into Azure Synapse

Using PolyBase in Azure Data Factory (ADF) for loading data into Azure Synapse

Azure Data factory’s “Copy Activity” has an option for using PolyBase to achieve best performance for loading data into Azure Synapse (formerly Azure SQL Data Warehouse) Analytics. There is an article published here to provide implementation detail. Intent of this article is provide some guideline on handling some common errors.

Before you start reading thru common errors and resolutions, it is assumed that you have done followings:

  1. Provisioned Azure Storage Account
  2. “Firewall and Virtual networks” page for Storage account as exception set to “allow trusted Microsoft services to access this storage account” or have whitelisted ADF IP Address.
  1. ADF has been granted “Storage Blob Data Contributor” role on Storage Account
  2. Linked Service in ADF using “Managed Identity” as an Authentication Mode

Now let’s come back to first common error message reported:

{
“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=Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105096,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105096,State=1,Message=Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.,},],'”,
    “failureType”: “UserError”,
    “target”: “CopyDataUsingPolyBaseAsStage”,
    “details”: []
}

Above error message indicates that “Managed Service Identity” is not enabled for Azure Synapse. ADF does highlights it as an informational message on following screen:

Let’s look at what was missed and how to resolve.

Step 1:  Using PowerShell, Register your Azure Synapse (DWH) Server with Azure Active Directory.

Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity<br>

For an example: if ResourceGroupName is MKDemo and Azure Synapse (DWH) Server name is mksqlsyn01 then command is:

PS C:\windows\system32> Set-AzSqlServer -ResourceGroupName MKDemo -ServerName mksqlsyn01 -AssignIdentity

Step 2:  Add “Storage Blob Data Contributor” role on Azure Storage Account to Azure Synapse Server. 

 Once Server in above screen is selected and setting saved, then you can verify same on “Access Control (IAM)” page of Azure Storage Account under “Role Assignments” as shown below:

With this, it is confirmed that you have taken care of “Informational” message displayed on Setting page of “Copy Activity” component in Azure Data Factory. 

Now, lets look at second most common error message reported by customer:

{
“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=External file access failed due to internal error: ‘Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist – Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: \”This request is not authorized to perform this operation using this permission.\”, 403, HEAD, https://mkdemostorage.dfs.core.windows.net/demo//?upn=false&action=getAccessControl&timeout=90’,Source=.Net SqlClient Data Provider,SqlErrorNumber=105019,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105019,State=1,Message=External file access failed due to internal error: ‘Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist – Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: \”This request is not authorized to perform this operation using this permission.\”, 403, HEAD, https://mkdemostorage.dfs.core.windows.net/demo//?upn=false&action=getAccessControl&timeout=90’,},],'”,
“failureType”: “UserError”,
“target”: “CopyDataUsingPolyBaseAsStage”,
“details”: []
}

To resolve above error message, grant “ALTER ANY EXTERNAL DATA SOURCE” permission to Azure Data Factory in Azure Synapse Server. For an example: if Azure Data Factory name is MKADFDemo then syntax is:

grant ALTER ANY EXTERNAL DATA SOURCE TO MKADFDemo;

Moving to third common error message repoted:

{
    “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=User does not have permission to perform this action.,Source=.Net SqlClient Data Provider,SqlErrorNumber=15247,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=15247,State=1,Message=User does not have permission to perform this action.,},],'”,
    “failureType”: “UserError”,
    “target”: “CopyDataUsingPolyBaseAsStage”,
    “details”: []
}

Above error message indicates that you have not granted required database permission. Basically, to resolve this, you need to make sure ADF has db_owner permission. For an example: if ADF name is MKADFDemo then syntax is:

EXEC sp_addrolemember db_owner, MKADFDemo;

Assumption for above command is that you have created a user for Azure Data Factory. if you have not then before working on resolving error 2 and 3, create a user for Azure Data Factory. Syntax in this case:

CREATE USER MKADFDemo FROM EXTERNAL PROVIDER;

That should take care of errors and your ADF will work as expected:

Hope this helps! 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top