Overview
The stored procedure dbo.[create_extDataSource] procedure manages the creation of credentials for the management of Access Tokens in SQL
Whilst the solution imports data from the Azure Blob as json formatted files into the SQL import Tables, a connection has to be made between the SQL Database and the Blob Storage
This connection is made using a SAS token from the storage container and Encrypted Credential within SQL
This is encompassed in the stored procedure create_extDataSource
Process
Blob Storage
Location
This will be the Location of the external Data source
For the location navigate to properties in the left menu and copy the URL … https://vipdatalake.blob.core.windows.net/assetcopied
Source
This will be the source of the SAS (Access) Token
Learning… External DB credentials need SAS Token … Azure to Azure requires Access Key
… same SQL code, same credential layout
SAS Token
Navigate to the Storage container … in this example this will be vipdatalake | Containers | assetcopied
either, in the list of containers select the menu ellipsis a the end of the row then [Generate SAS]
or, select the container (assetcopied), then Share Access Tokens in the new left menu
Set the time limit for the token, recommendations are three months, (but probably longer than the day offered as default)
When you are happy with the parameters select [Generate SAS Token]
The SAS token is the whole of Blob SAS Token …. sp=r&st=2022-11-07T15:36:08Z&se=2022-11-07T23:36:08Z&spr=https&sv=2021-06-08&sr=c&sig=ifHJdhrraRwqNpkNU#################mrXwo7UgBu0I%3D
for Access Key …
Copy this as your token …
in SQL
In order to create a new external Data source
EXEC dbo.create_extDataSource
<myexternalsourceName>,
<SAS Token>,
<Https://Location>,
<Optional: DebugMe = 1>
Sample Exec:
EXEC dbo.create_extDatasource
'MyAzureBlobStorage',
'j2H8P/DR+QP/BOfprA8aXt+YHBAUxFFCZ9G0uOTltnbJxR5kw3U#################Wv0+ASt+3v/2A==',
'https://vipdatalake.blob.core.windows.net/assetcopied',
0
if myexternalsourceName already exists, then it will replace the credential
NB There is no requirement to add the word credential, this is created in the same process after the external data source is named.
Uses
The external data source is used in the procedure [import].[blob_azure]
the parameters (and defaults) are as follows …
[import].[blob_azure]
(@blobName varchar(100),
@tableName varchar(100) = 'importhex',
@DataSource varchar(100) = 'myAzureHexStorage',
@truncate int = 0,
@debug int = 0)
If you change the external data source, be sure that the default values reflect this in this procedure
The procedure [import].[blob_azure] is called by the Azure Import Controller
[cont].[AzureBlob_imp]
(@blobName varchar(100),
@tableName varchar(100) = 'assetcopied',
@DataSource varchar(100) = 'MyAzureBlobStorage',
@truncate int = 1,
@debug int = 0)