Thingworx running on SQLServer with ssl=required

[avillanueva]

I recently ran into upgrade issues going from 9.3.4 to 9.7 on a test instance. It failed on the step for updating the database. I did some tests in line with this article: Upgrading ThingWorx Platform using the installer fails... This is the way my connection string is formatted both in platform.settings.json and in an connector Thing inside of Thingworx:

"driverClass": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
            "jdbcUrl": "jdbc:sqlserver://<host>:<port>;ssl=required maxStatements=0;databaseName=ThingWorx;applicationName=Thingworx;",

I had to add the ssl=required to the connection string. I could not find any article in the knowledge base about this but wondering if anyone in the field has configured their system in such a way. One thought I had was to have my DBA disable this option so we can perform upgrade without any complexities. When I ran the sqlcmd tests,  I did see it complain about ssl and even with options "-N -C" it still did not like it. I suspect the upgrade scripts would not account for this and just dump out.  Running on RHEL if that matters. Again, just looking for anyone who operates on SQLServer with ssl being required to share their knowledge. I have options to work around this and looking to document my own experience. 

[avillanueva]

Just stumbled on to this article. Installation fails with ODBC Driver 18 due to certificate validation in ThingWorx Foundation This was likely the cause of my woes on upgrading. Version 18 was release in 2022! In 3 years would could not update installer to handle the changes in 18 which enables by default encryption? Tisk Tisk. Downgrading but the link to this driver in help doc page leads one directly to installing version 18. No note. No word of caution. Tisk Tisk Tisk Tisk...in a Z formation!

[pshashipreetham]

Hi,

You're right to be frustrated, and you’re not alone. 

The fact that the ODBC Driver 18 enforces encryption and strict certificate validation by default, and yet the ThingWorx installer and upgrade scripts haven’t been updated to handle it (even years after its release) is a significant oversight. When foundational components like the database driver's behaviour change, it's the platform's responsibility to stay ahead, or at least clearly warn and guide users.

Thanks,

[slangley]

Hi @avillanueva and @pshashipreetham 

I am opening an internal ticket on this and will keep you posted with updates.

Regards.

--Sharon