创建数据库范围的凭据语法 [英] Create database scoped credential syntax

问题描述

在 VS2015(Azure SQL V12 项目)中处理数据库项目.我需要使用外部表引用，所以我必须创建一个凭证对象来向远程服务器进行身份验证.下面的代码和错误，不知道我错过了什么.

Working on a DB project in VS2015 (Azure SQL V12 proj). I need to use an external table reference so I have to create a credential object to authenticate with the remote server. Code and error below, not sure what I am missing.

SQL 代码

创建数据库范围的凭据 [mycredential]和IDENTITY = 'SomeIdentity',SECRET = 'SomeSecret';

CREATE DATABASE SCOPED CREDENTIAL [mycredential] WITH IDENTITY = 'SomeIdentity', SECRET = 'SomeSecret';

错误:

'[mycredential]' 附近的语法不正确.期待 '='IDENTITY"附近的语法不正确.期待 AW_CHANGE_TRACKING_CONTEXT、AW_ID、AW_XMLNAMESPACES 或 QUOTED_ID.

Incorrect syntax near '[mycredential]'. Expecting '=' Incorrect syntax near 'IDENTITY'. Expecting AW_CHANGE_TRACKING_CONTEXT, AW_ID, AW_XMLNAMESPACES, or QUOTED_ID.

推荐答案

好的，我在 VS2017 DB 项目中也遇到过这个，我的做法是使用存储过程，这样 intellisense 就不会触发错误.因为我发现代码在运行时有效.下面是我使用的存储过程:

Ok, I also encountered this in VS2017 DB project, the way I did it is to use stored procs, so that intellisense will not trigger an error. As i find the code is working when run. Below is the stored proc I used:

您在CREATE EXTERNAL TABLE"语句的YOUR_EXTERN_TABLE"中定义外部引用表(在本例中，该表设置为具有 ID 和 Name 列的架构):

you define your external reference table in "YOUR_EXTERN_TABLE" of "CREATE EXTERNAL TABLE" statement (which, in this example, is set to have schema of ID and Name columns):

CREATE PROCEDURE [dbo].[StoredProcWithExternalRefs] 
 @DatabaseName AS NVARCHAR(30), 
 @Passw AS NVARCHAR(100), 
 @SaID AS NVARCHAR(100), 
 @DataSource AS NVARCHAR(512), 
 @Catalog AS NVARCHAR(200)
AS
BEGIN

SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;


DECLARE @SQLString nvarchar(200);  

PRINT 'START'

PRINT 'CREATE DATABASE'

SET @SQLString =  N'CREATE DATABASE [' + @DatabaseName + ']'

EXECUTE sp_executesql @SQLString

SET @SQLString =  N'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''' + @Passw + ''';

CREATE DATABASE SCOPED CREDENTIAL credential_name   
WITH IDENTITY = ''' + @SaID + '''  
    , SECRET = ''' + @Passw + ''';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION=''' + @DataSource + ''',
    DATABASE_NAME=''' + @Catalog + ''',
    CREDENTIAL= credential_name
);

CREATE EXTERNAL TABLE YOUR_EXTERN_TABLE(
    [Id] [int] NOT NULL,
    [Name] [nvarchar](20) NOT NULL,
) WITH ( DATA_SOURCE = RemoteReferenceData );'
...


EXECUTE sp_executesql @SQLString

PRINT 'DONE.'




END

您可以在CREATE EXTERNAL TABLE"语句和架构中添加具有相同模式的其他外部表.

you can add additional external tables with the same pattern in the "CREATE EXTERNAL TABLE" statement and the schema.

这里有一个指导你的参考:https://sqldusty.com/2017/05/30/setting-up-cross-database-queries-in-azure-sql-database/

here is a reference to guide you: https://sqldusty.com/2017/05/30/setting-up-cross-database-queries-in-azure-sql-database/

这篇关于创建数据库范围的凭据语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！