How to securely manage load credentials with SSDT, Azure Key Vault, and Azure DevOps

Copper Contributor

May 17, 2020

First of all, Thank you for your article.
Second of all, I did almost as same as you have mentioned in above. My pipeline creates my 'DATABASE SCOPED CREDENTIAL' for the first time but for second times, when my pipeline runs, I have this error :

##[error]*** Could not deploy package.

##[error]Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: DatabaseScopeCredentialSecret DatabaseScopeCredentialIdentity ExternalDataSourceMarineTrafficLocation.

I have these files in my SSDT.

ADLSCredential.sql :

CREATE MASTER KEY;
GO

CREATE DATABASE SCOPED CREDENTIAL ADLSCredential
WITH
IDENTITY = '$(DatabaseScopeCredentialIdentity)',
SECRET = '$(DatabaseScopeCredentialSecret)'
;

AzureDataLakeStoreMarineTraffic.sql :

CREATE EXTERNAL DATA SOURCE AzureDataLakeStoreMarineTraffic
WITH (
TYPE = HADOOP,
LOCATION='$(ExternalDataSourceMarineTrafficLocation)',
CREDENTIAL = ADLSCredential
);

As you can see i am using three variables. I have created these variables in my Azure key Vault and I have configured every things related to my Azure Key Vault. When I don't have these objects in my DW, My pipeline creates them perfectly but for next run, I have this error :

This is part of my YAML file:

- task: SqlAzureDataWarehouseDacpacDeployment@1

inputs:

azureSubscription: '<My Subscription>'

AuthenticationType: 'server'

ServerName: '<MyServer Name>.database.windows.net'

DataWarehouse: '$(SynapseName)'

SqlUsername: '$(SynapseSQLUsername)'

SqlPassword: '$(SynapseSQLPassword)'

deployType: 'DacpacTask'

DeploymentAction: 'Publish'

DacpacFile: 'SQL_ASynapse\bin\Release\SQL_ASynapse.dacpac'

AdditionalArguments: '/p:IgnoreAnsiNulls=True /p:IgnoreComments=True /v:DatabaseScopeCredentialSecret=$(DatabaseScopeCredentialSecret) /v:DatabaseScopeCredentialIdentity=$(DatabaseScopeCredentialIdentity) /v:ExternalDataSourceMarineTrafficLocation=$(ExternalDataSourceMarineTrafficLocation)'

IpDetectionMethod: 'AutoDetect'

Could you please tell me What should I have done?

Blog Post

How to securely manage load credentials with SSDT, Azure Key Vault, and Azure DevOps