0

I'm trying to create an external data source to access Azure Blob Storage. However, I'm having issues with creating the actual data source.

I've followed the instructions located here: Examples of bulk access to data in azure blob storage and Create external data source - transact sql. I'm using SQL Server 2016 on a VM accessing via SSMS on a client machine using Windows Authentication with no issues. Instructions say creating this external data source works for SQL Server 2016 and Azure Blob Storage.

I have created the Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>

and, the database scoped credential

CREATE DATABASE SCOPED CREDENTIAL UploadCountries  
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = <key>;

I have verified both of these exist in the database by querying sys.symmetric_keys and sys.database_scoped_credentials.

However, when I try executing the following code it says 'Incorrect syntax near 'EXTERNAL'

CREATE EXTERNAL DATA SOURCE BlobCountries
WITH  (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<somewhere>.table.core.windows.net/<somewhere>', 
    CREDENTIAL = UploadCountries  
);

Your thoughts and help are appreciated!

Steve.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
Steven Kanberg
  • 6,078
  • 2
  • 16
  • 35

1 Answers1

1

In “Examples of Bulk Access to Data in Azure Blob Storage”, we can find:

Bulk access to Azure blob storage from SQL Server, requires at least SQL Server 2017 CTP 1.1.

And in Arguments section of “CREATE EXTERNAL DATA SOURCE (Transact-SQL)”, we can find similar information:

Use BLOB_STORAGE when performing bulk operations using BULK INSERT or OPENROWSET with SQL Server 2017

You are using SQL Server 2016, so you get Incorrect syntax near 'EXTERNAL' error when you create external data source for Azure Blob storage.

Fei Han
  • 26,415
  • 1
  • 30
  • 41
  • 1
    Well, it’s wonderfully helpful that it says it applies to SQL Server 2016 but I suppose there can always be caveats. Thank you for noting those. I obviously glossed over them trusting too heavily on the opening statement. – Steven Kanberg Oct 16 '17 at 17:02
  • How do you even set up an azure sql database where you can select the version to be 2017? I've created a couple databases on Azure, but don't see the ability anywhere to change this. When I run `SELECT @@VERSION` they all return v12, but 2017 is v13. – Kris Coleman Feb 21 '18 at 18:27