8

I have a SSDT project that deploys to Azure SQL DB. Recently I added an external table. This required an external data source as follows:

CREATE EXTERNAL DATA SOURCE [data_warehouse]
    WITH (
    TYPE = RDBMS,
    LOCATION = N'mydb.database.windows.net',
    DATABASE_NAME = N'MainDW',
    CREDENTIAL = [dw_reader]
    );

When I import this into my .sqlproj, it complains the credential isn't there, and then when I add that it complains the master key isn't there. The problems is I don't want to store those secrets in the SSDT project. How do I store dummys, or defaults that can be overridden and retrieved from the azure keyvault or other method of storing secrets in Azure Decops?

1
  • How do you deploy database changes? You can reference a SQL Cmd variable and pass that into the publish from your deployment pipeline. Though that still needs management about ensuring the publish script is not stored in any form without secrets redacted. – Martin Smith Apr 19 '19 at 12:41
1

After experimenting, it seems like any kind of secret in SSDT only gets used when the object is created, not updated.

Therefore if a sqlproj contains CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password A';, and no master key exists, then the master key will be created with that password. However, if one later executes ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; on the database, and then redeploys the sqlproj to the database, it will not regenerate the master key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.