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. Co

Solution 1:

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/