Work with Passwords

Nobody wants to store passwords, tokens or any other sensitive information in plain text. That is valid also for CAT project files.

Often, the project files must be version controled e.g., in Git - and it would be simply insane to store passwords for your production servers.

This is clearly wrong!!! Never do this:* Passwords in plain text are pure evil

So, what options do we have?

Integrated Security

SQL Server and many other providers support authentication through Active Directory. For on-premise servers, this is preferable option. Contact the system administrator for details. You need access

  • for yourself for running tests interactively
  • for process that runs CAT in autonomous mode - here it depends on your setup (e.g., you might need access for Azure DevOps selfhosted agent)

The connection string might look something like this:

Data Sources:
- Provider: SqlServer@1
  Connection string: data source=OurServer;integrated security=true;initial catalog=DWH
  Name: DWH

Service Principals

This is the preferable option in MS Azure. You can use service principals defined in Microsoft Entra ID. See SqlServer@2 provider documentation to check connection strings that use service principals, MFA etc. Because the service principals use secrets that need to be present in the connection string, you also need to hide them using environmente variables. Keep reading.

Environment Variables

Another option is usage of environment variables. If you really have to use user name and password for connection to your database, don’t use them in plain text. Instead, define an environment variable with the sensitive information, something like this:

Environment variable with sensitive information

Once you have it, you can use it in the CAT project file:

Environment variable used in a CAT project file

CAT will automatically work with the environment variable value. CAT will operate as if the value was in the file. Big advantage is, that you can have the file in source control and you can use it without any changes in automation processes, such as in CI/CD pipelines.

In some cases, you might have data sources defined elsewhere, such as in a relational database, MS Excel files etc. CAT will expand the environment variables there as well. It works with any provider. But only when reading the data sources definitions, not e.g., in test names, SQL queries etc. - sensitive information are not expected there.

If you are having troubles to see the variable, chances are that you just created it a moment ago - try to restart your PowerShell session. If it does not help, restart the machine. The replacement of environment variables will not work, if you cannot list the variable in PowerShell:

# if this returns your variable, CAT will see it too
# otherwise please restart
ls Env:\YOUR_VARIABLE_NAME

Placeholders

Another approach is to replace a placeholder (%DWH_CONNECTION_STRING% in the example above) in a step before running the tests. You can use e.g., PowerShell, Python or any other means and replace it with a correct value. This might be useful in Azure DevOps or other automation tool, but using the environment variables is significantly easier and safer.

← Set RowCount
Test Power BI →