Configure connection string in Octopus Deploy per project

My connection strings are configured in Octopus Deploy through library/variables/LibraryVariableSets and all works fine based on the target environment, what I am going to do now is to create new db users for each project/app and set this UID/PWD in my connection strings in Octopus.

My question is: how can I configure this connection string per project like what I already have with the local variables for the project (app settings keys)?

P.S. Octopus version is 4.1.2

You can add variable references for the username and password to the connection string variable in Library Variable Set.

Server=myServerAddress;Database=myDataBase;User Id=#{dbuserid}; Password=#{dbpassword};

Then in each project where you are using this library variable set, create a new variables for the dbuserid and dbpassword, when the variables are evaluated for the deployment, these variable placesholders in the connection string will be updated with the values provided by the project variables.

The final value of the connection string variable during deployment:

Server=myServerAddress;Database=myDataBase;User Id=userid; Password=password;

More details:

This is what your library variable set should look like, you can add some scoping here too.

Link the Library Variable Set to the project:

Then set the user id and password in the project variables:

Then you can use the connection string in scripts or as variable replacement in your config files.

I tested it with the following powershell script

write-host $connectionstring
write-host "#{connectionstring}"

Which results in the following output:

If you need to inject the connection string into a specific position in a JSON file, you can use the JSON Configuration Variables syntax and set the value to the connection string variable reference #{connectionstring}.