Troubleshooting BULK INSERT and OPENROWSET issues on Azure SQL Database

Database (single database and Managed Instance) enables you to load files from Azure Blob Storage. If you experience some issue while you are loading files from Azure blob , here you can find the actions that can help you to and mitigate the issue.

  • If you are noticing that some syntax error is returned by BULK INSERT or BULK INSERTcheck are you usingsupported syntax in this statement. As an example, if you are trying to load data via share path note that this option is not supported in Managed Instance.
  • Make sure that you are restoring a database from public blob protected withSAS credential. Private IPs for blob and service endpoints are currently not supported.
  • If you are getting the error 5 (Access Denied), make sure that you have not denied the access to your Azure account using the :Storage firewall.png
  • Verify that you have createdEXTERNAL DATA SOURCE with type BLOB_STORAGE targeting the URL of the where you placed the files that should be restored to your database.
  • Make sure that you are targeting existing file on Azure .
  • ScriptCREDENTIALto SQL Server 2017 and try to load the files. If you are troubleshooting the issue on Managed Instance make sure that SQL Server is in the subnet that is within the same VNet as the Managed Instance, 
  • Check is your SAS credential placed inSECREToption ofCREATE DATABASE SCOPED CREDENTIALstatement valid. The most common errors inSAS token parametersare:
    • ?is not removed from the beginning of the SAS token because the Azure portal generates SAS token with the leading?. Remove this character if you see it.
    • se(expiry date) property is set to some value in the past (note that this is UTC time).
    • st(start date) property is not in the past (note that this is UTC time).
    • sp(permission) property should allow reading the file on the storage account.
    • sip(ip rage) remove this parameter if it is present in SAS token..

 

This article was originally published by Clustering For Mere Mortals. You can find the original article here.