CODEWITHSUNDEEP
sqlt-sqlvisual-studio-2015azure-sql-databasecredentials
BI Developer
BI Developer

Reputation: 51

Create database scoped credential syntax

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 code

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

Errors:

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

Upvotes: 3

Views: 5126

Answers (1)

k3nn
k3nn

Reputation: 171

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:

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

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

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

Upvotes: 2

Related Questions