Hi
As a DBA I would like to be able to view all the connection strings used in configs, to validate that they adhere to naming conventions for different environments dev/test/prod etc.
Where in the Octopus database backend do I find the connection strings used in deployments to do such a validation ?
–ketillg.
Hi Ketillg,
Thanks for getting in touch! Octopus dose not store any of your deployments connection strings in the database unless they are input and saved as variables in a project or as a variable set.
If the connections strings are a part of a package being deployed, generally in a config file, we will have no recording of the value. Octopus will only transform configuration files and evaluate any variables.
If these values have been manually input into Octopus as variables, then you could use the API to collect this information.
Let me know if I have missed anything here or if you have any further questions. 
Best regards,
Hi and thanks for a quick reply.
I checked with our admin team that is using the Octopus interface. They do enter all connection strings as variables. So if I understand you correctly they are somewhere in the database, probably stored as a part of a string. If you tell me where, I could easily query those fields and parse out the conn string info. But it I go the API way I need to invoke resources from other teams, wich I prefer not to.
–ketillg.
Hi Ketillg,
Thanks for getting back. There are two places we could store these variables in the Octopus database.
The first is per-project: [DatabaseName].[dbo].[VariableSet]:
| Id | OwnerId | Version | IssFrozen | JSON |
|---|---|---|---|---|
| variableset-Projects-1 | Projects-1 | 3 | 0 | {see JSON below} |
The second are library variable sets: [DatabaseName].[dbo].[LibraryVariableSet]
The variables for both variable types are stored as JSON in the Database, an example from my VM is below. This is the JSON result from querying my Octopus server’s database under dbo.VariableSet. The OwenerId is used to identify which project you are looking at in the DB. In this case it is the project with the ID of Projects-1.
{"Variables":[{"Id":"fcfc746b-eebd-595f-be46-6f33323e6cdd","Name":"VARIABLE1","Description":null,"Type":"String","Value":"VALUE1"},{"Id":"8e27ad3e-56d5-6b73-a3a3-338c858f82c5","Name":"VARIABLE2","Description":null,"Type":"String","Value":"VALUE2"}]}
My project has two variables: VARIABLE1 & VARIABLE2 with their values VALUE1 & VALUE2 respectively.
I will note that we do not support using the database to make any data changes. This can break Octopus in unexpected ways.
Let me know if this information helps or if you have further questions here.
Best regards,
Daniel