我们可以在Azure上的T-SQL过程中创建数据库范围的凭据吗

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

我们可以在Azure SQL上的T-SQL过程中为共享访问签名创建数据库范围的凭据吗?

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

我们正在尝试从存储在Azure blob中的csv文件中批量插入数据

Below are the steps

下面的步骤

Create SAS credentials using JAVA

使用JAVA创建SAS凭据
Java will call stored procedure to create/alter external data source and pass SAS credential to this procedure as a parameter

Java将调用存储过程来创建/修改外部数据源，并将SAS凭据作为参数传递给此过程
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

我们正面临通过T-SQL过程创建数据库范围的凭据的挑战，能否有人确认这种方法是否确实可行

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

我已经看到在Microsoft站点上创建数据库范围的凭据的语法，当我们将脚本作为独立语句运行时，它可以工作，但是我们不能使用过程编译代码

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)

错误编号:102错误信息:在“1245632512344”附近的语法错误。行号:3程序名:(SQL状态:S0001 -错误代码:0)

Thanks

谢谢

1 个解决方案

#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 + ''''

#1