locked
Azure SQL access to blob storage RRS feed

  • Question

  • Can i get azure sql db to bulk load a blob file without setting the storage account to allow access from all networks?
    Wednesday, April 10, 2019 6:56 PM

Answers

  • So sorry. Should have slowed down and looked more closely at the file. The correct link is: https://download.microsoft.com/download/0/1/8/018E208D-54F8-44CD-AA26-CD7BC9524A8C/PublicIPs_20190410.xml

    And then the correct output is seen: head -10 PublicIPs_20190410.xml.1

    • Marked as answer by NDSQLGuy Friday, April 12, 2019 1:30 PM
    Friday, April 12, 2019 1:07 AM

All replies

  • Yes, you can still access your Storage Account without enabling the "Allow access from all networks" but this requires identifying the public IP address or allowing public access to the data file. Clearly, allowing public access to the file may not be desired but if you allow it for the period of the data load, it is super easy to set-up and you don't have to do a lot of configuration, as is the case with whitelisting source IPs. 

    The proper way to grant external access to your storage accoungt is through Access Keys, Shared Access Signatures (SAS), and Shared Access Policies. You will want to inclue the proper access key with your CREATE EXTERNAL DATA SOURCE

    Microsoft Documentation: Using shared access signatures (SAS)

    Blog Post: External Access to Azure Storage

    Create an external data source for bulk operations retrieving data from Azure Blob storage

    The below T-SQL can be used to create access to a blob storage account where SECRET is the Shared Access Signature string for the specific storage account you wish to access.

    CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices 
     WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '(REMOVE ? FROM THE BEGINNING)******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';
    
    CREATE EXTERNAL DATA SOURCE MyAzureInvoices
        WITH  (
            TYPE = BLOB_STORAGE,
            LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    		CREDENTIAL = AccessAzureInvoices
        );

    I hope this helps!

    Wednesday, April 10, 2019 10:06 PM
  • Azure SQL Database is doing a bulk load from that blob file. My problem is i don't know the ingress IP address of Azure SQL Database so i cannot include that in my shared access signature.

    is there a range of IP addresses by region for Azure SQL Database? I am thinking this is a common load scenerio perhaps there is a solution available.

    "Allow trusted Microsoft services to access this storage account" is an option on the storage account firewall but Azure SQL DB is not a trusted service. We should upon vote this. (:

    Thursday, April 11, 2019 1:40 PM
  • Correct, Azure services communicate over the Azure intranet backbone and if you wish to manually configure IP whitelists by Azure region/service, you use the private IP ranges. This download is the complete list of those IP ranges, including all SQL specific ranges. 

    Microsoft Azure Datacenter IP Ranges

    Please let us know if you have additional questions here.

    Regards,

    Mike

    Thursday, April 11, 2019 5:01 PM
  • Is there a way i can import the datacenter ip list programmatically? For example an ftp site or blob stored somewhere?
    Thursday, April 11, 2019 6:46 PM
  • You can code the following into a shell script:

    wget https://www.microsoft.com/en-us/download/confirmation.aspx?id=41653 -O ./PublicIPs_20190410.xml

    To receive a file in the directory the wget was execute with a name: PublicIPs_20190410.xml

    Then you would need to run an ARM Template to update firewall settings. You could manually add a few IP ranges and then download the ARM template for your specific deployment, and where your whitelist of IP addresses reside in the ARM file, you could effectively programmatically manage the list to make updates.

    So, any or all of this could be automated. 

    Thursday, April 11, 2019 7:52 PM
  • Sorry but that wget command just outputs the html of the download page. it does not actually download the ip list. Is there another address just to get that file?


    Thursday, April 11, 2019 9:59 PM
  • So sorry. Should have slowed down and looked more closely at the file. The correct link is: https://download.microsoft.com/download/0/1/8/018E208D-54F8-44CD-AA26-CD7BC9524A8C/PublicIPs_20190410.xml

    And then the correct output is seen: head -10 PublicIPs_20190410.xml.1

    • Marked as answer by NDSQLGuy Friday, April 12, 2019 1:30 PM
    Friday, April 12, 2019 1:07 AM