We have a version 2022.3.10828 standalone Octopus server and I created a new deployment target, with listening tentacle, which is an MSSQL 2017 CU31 running on Windows Server 2016 Datacenter edition. I’m not able to deploy to it because it fails with the following error:
Error
*** Could not connect to database server.
April 13th 2023 14:42:36
Error
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
April 13th 2023 14:42:36
Error
The certificate chain was issued by an authority that is not trusted
I use the ‘sa’ user, so it has all the permissions.
I saw earlier this issue in case of other application and there the easiest solution was to set in the connection string the Trusted_Connection=no;Encrypt=No; variables.
But in Octopus server is there any way to define that the Octopus itself how should connect to the SQL server?
You will need to set the trust up for that connection. Octopus doesn’t have a way of defining how it should connect to a SQL server, so doing this via the connection string would be the best option, as you suggested.
The only built-in part to the step you are using (which I assume is a DACPAC step) would be to set the extra arguments when executing this step, but it’s not as clean and simple as setting it in the connection string itself.
Let me know how the connection string option goes, and if you need anything else please let me know!
Kind Regards
Sean
well after 2 days we have found the solution. We had to downgrade the Management studio from version 18.12 to version 17.9. In this way the deployment is working as it expected.
Apologies, I thought that the DACPAC had the connection string parameters included in the step itself.
In this case, I would suggest using this particular step template which has the TrustServerCertificate set to true by default. This would get around having to change anything in the parameters in your current template or change anything outside of the templates itself. You should be able to search for this in the “Add Step” button in your project’s “Process” section, or you can follow the instructions on that page itself.
Sorry for the confusion again! I hope this above will help resolve the issue you’re seeing.
I just saw this from a colleague as I sent a reply! Glad that you found the issue, I would assume it would be something around the management studio itself?
based on your feedback the SQLCMD component itself inside of the management studio. Now its on version 14 and earlier it was version 15. Probably its handling in a different way the encryption
