Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret;

Question

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?

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