2

TL;DR I am trying to point SQL to BULK INSERT from Local Azure Blob Storage

The problem:

Hi all,

I'm trying to connect my local SQL Server database instance to the blob storage emulator as an external connection, however I'm getting a "Bad or inaccessible location specified" error. Here are the steps I'm taking:

I have created the following MasterDatabaseKey and CREDENTIALS as follows:

IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') 
DROP MASTER KEY;

--Create Master Key
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD='MyStrongPassword';

and database credentials:

-- DROP DB Credentials If Exist
IF EXISTS (SELECT * FROM sys.database_credentials WHERE name = 'credentials')
DROP DATABASE SCORED CREDENTIAL credentials;

--Create scoped credentials to connect to Blob
CREATE DATABASE SCOPED CREDENTIAL credentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 
'Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=='; --local storage key
GO

then I created the following External Data Source:

CREATE EXTERNAL DATA SOURCE external_source
WITH 
(
    TYPE = BLOB_STORAGE,
    LOCATION = 'http://127.0.0.1:10000/devstoreaccount1/container/some_folder/',
    CREDENTIAL = credentials
)

But when I run the BULK INSERT command:

BULK INSERT [dbo].[StagingTable] FROM 'some_file_on_blob_storage.csv' WITH (DATA_SOURCE = 'external_source', FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

but it fails and returns

Bad or inaccessible location specified in external data source "external_source".

How can I load a file from Local Blob Storage into SQL Server?

Dylan Smyth
  • 162
  • 11
  • 1
    Just confirming that if you plug http://127.0.0.1:10000/devstoreaccount1/container/some_folder/ into your local browser, you get something that looks "blobby".. even a 401 error? – Nick.Mc Jan 19 '21 at 10:06
  • @Nick.McDermaid that is correct, 401 (Unauthorized) with an XML body. – Dylan Smyth Jan 19 '21 at 10:27
  • 1
    I notice the docs here https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated#examples-bulk-operations say _Do not add a trailing /, file name, or shared access signature parameters at the end of the LOCATION URL when configuring an external data source for bulk operations._ – Nick.Mc Jan 19 '21 at 11:34
  • the example also doesn't have a trailing `/` so try removing it. – Nick.Mc Jan 19 '21 at 11:35
  • @Nick.McDermaid I've just added a SAS to the `credential` parameters and appended the file with the secret. It seems to be reading the file, but it's locked by another process (apparently) – Dylan Smyth Jan 19 '21 at 11:41
  • @Nick.McDermaid Any updates? I did not had issue when using my hosted azure storage's location, but if I try locally with http://127.0.0.1:10000/devstoreaccount1/container/some_folder/, I'm getting similar issues as yours. – Susen Maharjan Apr 05 '22 at 09:08

1 Answers1

3

Nick.McDermaid has point out the error correctly. From your code and the error message, the error is caused by the wrong LOCATION syntax:

  • Do not add a trailing /, file name, or shared access signature parameters at the end of the LOCATION URL when configuring an external data source for bulk operations.

Ref here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated#examples-bulk-operations

Change value to LOCATION = 'http://127.0.0.1:10000/devstoreaccount1/container/some_folder', the error should be solved. I tested and all works well. enter image description here

For you another question, we can not answer you directly. I suggest you post another question with you detailed code. We're all glad to help you.

Update:

About your another question, I tested and found that we must set the Shared access signature(SAS) 'Allowed resource type' = Object, then we can access container and child folder and the files in the container.

enter image description here

Example, both the statements work well. enter image description here

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23