Can we create Database Scoped credential inside T-SQL procedure on Azure

Question

Can we create database scoped credential for shared access signature inside a T-SQL procedure on Azure SQL ?

We are trying to bulk insert data from csv file stored on Azure blob

Below are the steps

• Create SAS credentials using JAVA

• Java will call stored procedure to create/alter external data source and pass SAS credential to this procedure as a parameter

• Above procedure will internally call another procedure to do BULK INSERT

We are facing challenges in creation of database scoped credential via T-SQL procedure , can someone confirm if this approach is actually possible

It would be great help if we can get some sample code for the same .

I've seen the syntax to create database scoped credential on Microsoft site , it works when we run scripts as standalone statements but we are unable to compile the code using a procedure

We tried below code

CREATE procedure prc_create_external_data_source @SAS VARCHAR(100)
AS
BEGIN

SET nocount ON

DECLARE @command varchar(MAX);

PRINT 'Shared Access Key Received Key: '+@SAS;



SET @command = 

'CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
WITH IDENTITY = ''SHARED ACCESS SIGNATURE'',
SECRET = '+@SAS


EXECUTE (@command);

PRINT 'Database Scoped Credential Created';



END

We get below Error message after executing this procedure

EXECUTE prc_create_external_data_source '1245632512344'

Error Number : 102 Error Message : Incorrect syntax near '1245632512344'. Line Number : 3 Procedure Name : (SQL State: S0001 - Error Code: 0)

Thanks

Answer 1

Your secret probably needs to be surrounded by quotes. Also this all needs to be on one line or concatenated with +

If you printed the string it might be obvious. Try this:

SET @command =     
'CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential ' +
'WITH IDENTITY = ''SHARED ACCESS SIGNATURE''' + 
',SECRET = '''+ @SAS + ''''