Work with secrets

A project file is text and usually lives in Git; a password in it is a password in Git. Keep secrets out of the file — with an environment variable, with the database's own authentication, or with Microsoft Entra ID.

# wrong - this password is now in Git, in every clone, forever
Data sources:
- Name: DWH
  Provider: SqlServer@2
  Connection string: Server=dwh;Database=DWH;User Id=cat;Password=Summer2024!

Environment variables — the universal way #

Works with every provider and every tool, on a laptop and in a pipeline. Put the secret into an environment variable and write the variable’s name between percent signs where the value belongs — the whole connection string, or only the secret part of it:

Data sources:
- Name: DWH
  Provider: SqlServer@2
  Connection string: "%DWH_CONNECTION_STRING%"

- Name: CRM
  Provider: Postgres@1
  Connection string: "Host=crm;Database=crm;Username=cat_reader;Password=%CRM_PASSWORD%"

CAT replaces the variable when it opens the project and behaves as if the value were in the file; the file goes into Git unchanged and runs unchanged in every pipeline, where the platform injects the secret as an environment variable of the step. Expansion works wherever the definition lives — project file, other YAML files, a database table, a worksheet. How to define a variable on each system and check that CAT sees it: Use environment variables.

Integrated authentication — no secret at all #

On-premises SQL Server, and other databases that trust the account a process runs under, need no password in the connection string:

Data sources:
- Name: DWH
  Provider: SqlServer@2
  Connection string: Server=dwh;Database=DWH;Integrated Security=true

Two accounts then need access to the data: yours, when you run tests interactively, and the account the unattended run uses — the pipeline agent’s service account, the SQL Server Agent’s, the scheduled task’s. Ask the administrator of the database for both.

Microsoft Entra ID — service principals and MFA #

For Azure SQL Database, Synapse, Fabric and the other Azure services, authenticate through Microsoft Entra ID with the SqlServer@2 provider:

• Interactively, with your own account and multi-factor authentication — Authentication=Active Directory Interactive prompts you. Fine at your desk; useless unattended, nobody answers the prompt.
• Unattended, with a service principal: an app registration with a secret, given access to the database; the application id goes to User Id, the secret to Password — and the secret is, of course, an environment variable:

Data sources:
- Name: AzureDWH
  Provider: SqlServer@2
  Connection string: "Server=tcp:dwh.database.windows.net,1433;Database=DWH;Authentication=Active Directory Service Principal;User Id=%AZURE_CLIENT_ID%;Password=%AZURE_CLIENT_SECRET%"

The exact connection strings for the Entra ID modes are on the SqlServer@2 page.

Replacing placeholders before the run #

A pipeline step can also rewrite the project file before CAT runs — replace a placeholder with the real value using PowerShell, Python or the platform’s own templating. It works, but it is a second mechanism to maintain for what environment variables do on their own; reach for it only when a platform cannot expose a secret as an environment variable.

Related #

• Use environment variables — define, refer, verify.
• Environment variables — the reference: what CAT replaces and where.
• Providers — hiding a password or a whole connection string, the rule every provider page follows.