Azure SQL: Adding from Blob Not Recognizing Storage

Question

I am trying to load data from a CSV file to a table in my Azure Database following the steps in https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15#f-importing-data-from-a-file-in-azure-blob-storage, using the Managed Identity option. When I run the query, I receive this error: Failed to execute query. Error: Referenced external data source "adfst" not found.

This is the name of the container I created within my storage account. I have also tried using my storage account, with the same error. Reviewing https://docs.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-access-to-data-in-azure-blob-storage?view=sql-server-ver15 does not provide any further insight as to what may be causing the issue. My storage account does not have public (anonymous) access configured.

I'm assuming that I'm missing a simple item that would resolve this issue, but I can't figure out what it is. My SQL query is below, modified to not include content that should not be required.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**************';
GO
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = '***********************';
CREATE EXTERNAL DATA SOURCE adfst
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://**********.blob.core.windows.net/adfst'
          , CREDENTIAL= msi_cred
);
BULK INSERT [dbo].[Adventures]
FROM 'Startracker_scenarios.csv'
WITH (DATA_SOURCE = 'adfst');

Did you create msi_cred according to the Shared Access Signature requirements? — AlwaysLearning
@AlwaysLearning No, because I created a Managed Identity instead. — Jason Rapp
Are you sure managed identity is supported? The documentation says you need a SAS. — Crowcoder
@Crowcoder Yes, from the first link I added: "Another way to access the storage account is via Managed Identity. To do this follow the Steps 1 thru 3 to configure SQL Database to access Storage via Managed Identity, after which you can implement code sample as below". The code I posted is based off the code sample mentioned. — Jason Rapp

Jim Xu Jim Xu · Accepted Answer · 2020-07-08T03:05:45

If you want to use Managed Identity to access Azure Blob storage when you run BULK INSERT command. You need to enable Managed Identity for the SQL server. Otherwise, you will get the error Referenced external data source "***" not found. Besides, you also need to assign Storage Blob Data Contributor to the MSI. If you do not do that, you cannot access the CVS file storing in Azure blob

For example

Enable Managed Identity for the SQL server

Connect-AzAccount

#Enable MSI for SQL Server
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity

Assign role via Azure Portal Under your storage account, navigate to Access Control (IAM), and select Add role assignment. Assign Storage Blob Data Contributor RBAC role to the server which you've registered with Azure Active Directory (AAD)

Test

a. Data

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

b. script

CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

--> Change to using Managed Identity instead of SAS key 
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Identity';
GO

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://jimtestdiag417.blob.core.windows.net/test'
          , CREDENTIAL= msi_cred 
);
GO
BULK INSERT CSVTest
FROM 'mydata.csv'
WITH (
 FIELDTERMINATOR = ',', 
  ROWTERMINATOR = '\n', 
DATA_SOURCE = 'MyAzureBlobStorage');
GO

select * from CSVTest;
GO

Azure SQL: Adding from Blob Not Recognizing Storage

1 Answers