[Bug]: MSSQL connection looks in connection.yaml file instead of environment variables when we run sources on prod

[jaroet]

Describe the bug

When deploying on prod (kubernetes) it looks like the MSSQL connection is still looking at the connection.yaml file for connection configuration instead of the available environment variables.
See also the slack thread related to this https://evidencedev.slack.com/archives/C023LRB9Z40/p1725526837920439

Steps to Reproduce

I added a MSSQL connection in my local dev environment and build some pages. All is working fine with the connection configuration being read from connection.yaml and connection-options.yaml.

When deploying on prod (kubernetes) it looks like the MSSQL is still looking at the connection.yaml file for connection configuration instead of environment variables. We came to this conclusion when my devops guy created an interactive run of the image we use to build our project on his local machine and we executed all commands one by one.

We got an [Processing] sd2020 [ ! ] Error connecting to datasource sd2020: Login failed for user 'username'. error, but the username and password are a 100% correct.

We checked if the environment variables where set correctly and all where showing the correct values when using the echo <variablename> command. So we changed the EVIDENCE_SOURCE__sd2020__user="username" variable to a different value (we checked with echo if it was set correctly) and did an npm run sources again but in the errormessage the original username was shown. Then we changed the username in the connection.yaml (normally only needed on dev) and the errormessage showed the changed username from the connection.yaml.

The password is stored in the connection-options.yaml file (but that file is not available in the repository) and in the connection.yaml the password is missing and as it is also not being read from the environment variable either (being export EVIDENCE_SOURCE__sd2020__password="password" it fails..

In the same project we also have 2 postgresql and 1 mysql source and for those npm run sources works correctly.

Logs

No response

System Info

We run it in the cloud (kubernetes) with a node20 image. Evidence version is 39.1.4

Severity

serious, but I can work around it

Additional Information, or Workarounds

My devops guy suggested I could inject the environment variable in the connection.yaml file when the image is run and just before the npm run sources command. I will resort to that when this can't be replicated but I prefer not to :-).

[jaroet]

Is there any progress on this?
I ask as I am now working on a production project and I run into the same issue.
The error is the same. I will sit friday with my devops guy to insert the .env variable in the connection file but that is certainly not the best solution. It might be related to other MSSQL connection issues?

[jaroet]

Just had another meeting with my devops guy to look at this and after changing to .env variables (the data was moved to another DB server) it all started to work as normal. He mentioned we had some strange setup where the DNS is only working in specific situations and dependent on the way you connect.

Personally I did not understand fully but the issue is (as a user) solved and the dashboard is working correctly just as it did locally in the dev env.