Upgrade In Windows
Brian O'Neill
I'm looking for some help with upgrading our ERDDAP environment. The previous person who set it up has left our organisation and I'm lacking background knowledge.
*Background*
We are using Windows server with Apache Tomcat 9. It was installed using the service deployer version. We are running ERDDAP v2.02. We have a SQL Server express running on the server.
My goal is to upgrade to Tomcat 10 with ERDDAP v2.23.
*ERROR*
I've been through an upgrade process (summarised below) and Tomcat is running fine with its SSL certificate. However, our datasets (2) are missing. I think I've copied all relevant files, so I don't know why it isn't working correctly.
The site loads, but shows only a default Dataset (1). My datasets.xml is using the driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver' so I've tried using the existing Microsoft driver, downloading a new one and also trying the sourceforge jdbc driver. However, none of them are working. I'd prefer to continue with an updated Microsoft driver, if possible, rather than introducing an old (possibly insecure) driver to the environment.
The latest errors I'm seeing are like this:
java.lang.RuntimeException: datasets.xml error on or before line #210: SQLException: No suitable driver found for jdbc:sqlserver://IP ADDRESS:1433
java.lang.RuntimeException: datasets.xml error on or before line #425: "encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:df221013-a958-453b-b6ea-d404c0fd155f
at gov.noaa.pfel.erddap.dataset.EDD.fromXml(EDD.java:486)
at gov.noaa.pfel.erddap.LoadDatasets.run(LoadDatasets.java:364)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:df221013-a958-453b-b6ea-d404c0fd155f
*Install and Upgrade Summary*
- Stop Tomcat 9 instance.
- Install Java 17 from https://adoptopenjdk.net.
- Install Tomcat 10 - service deployer version. Set some options in the Tomcat install - include Service Startup and Native options. Use port 80. Confirm location of new java install. Increase memory settings on Java tab.
- Copy files/folders from Tomcat 9 to Tomcat 10 folders - Content, Webapps\ERDDAP, Context.xml, SSLCertificate.jks.
- Put new ERDDAP.war into WebApps folder.
- Tomcat 10 server.xml file is slightly different format to Tomcat 9, so I manually put in settings to use SSL, rather than copying the old server.xml over.
- Tomcat runs OK and the website displays OK.
- Our datasets are missing.
Could anybody offer any advice on this please?
Damian Smyth
Hi Brian,
In my understanding of more recent versions of Java and the SQL server JDBC drivers, it tries to encrypt connections by default and looks for a trusted CA certificate .
SQL server has a fall back default “Server certificate” when a Certificate from an Official Certificate Authority is not installed.
The default option of the JDBC driver is not to trust the fall-back Server certificate (is this certificate not analogous to a self-signed SSL certificate?).
This is giving the error you are see:
“
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.
”
You have 2 options(based on a web search):
1. Request and Install a Server Certificate for your SQL server Instance from a recognised Certificate Authority
which should then provide a SSL/TLS connection using a certificate from a trusted Certificate Authority.
2. In your database connection attributes you add an additional “connectionProperty” element to tell java to trust the SQL Server default server certificate. e.g. under the <driverName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
Add the following line:
<connectionProperty name="TrustServerCertificate">true</connectionProperty>
The general advice is that option one is more secure, although you need to research and assess for your environment.
In your scenario It sounds like the connection from your ERDDAP instance is connecting to a local instance of SQL express so isn’t traversing a network, so option 2 might be fine, but again you need to evaluate this for your usage scenario and environment.
Best regards
Damian
From: erd...@googlegroups.com <erd...@googlegroups.com>
On Behalf Of Brian O'Neill
Sent: Monday 13 May 2024 12:01
To: ERDDAP <erd...@googlegroups.com>
Subject: [ERDDAP] Upgrade In Windows
|
You don't often get email from bonei...@gmail.com. Learn why this is important |
--
You received this message because you are subscribed to the Google Groups "ERDDAP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
erddap+un...@googlegroups.com.
To view this discussion on the web, visit
https://groups.google.com/d/msgid/erddap/bed2a6cd-b125-4d3f-8534-e5112da5d3f4n%40googlegroups.com.
Marine Institute
The information contained in this email and in any attachments is confidential and is designated solely for the attention and use of the intended recipient(s). This information may be subject to legal and professional privilege. If you are not an intended recipient of this email, you must not use, disclose, copy, distribute or retain this message or any part of it. If you have received this email in error, please notify the sender immediately and delete all copies of this email from your computer system(s). Our Privacy Policy.
Foras na Mara
Tá an t-eolas sa ríomhphost seo, agus in aon cheangaltáin leis, faoi rún agus tá sé dírithe ar an bhfaighteoir/na faighteoirí beartaithe amháin agus níor cheart ach dóibh siúd é a úsáid. D’fhéadfadh an t-eolas seo a bheith faoi réir pribhléid dhlíthiúil agus ghairmiúil. Mura tusa faighteoir beartaithe an ríomhphoist seo, níor cheart duit an teachtaireacht seo, nó aon chuid di, a úsáid, a nochtadh, a chóipeáil, a dháileadh nó a choinneáil. Má fuair tú an ríomhphost seo go hearráideach, cuir an seoltóir ar an eolas láithreach agus scrios gach cóip den ríomhphost seo ó chóra(i)s do ríomhaire, le do thoil. Ár bPolasaí Príobháideachta.
Brian O'Neill
Roy Mendelssohn - NOAA Federal
Damian thanks for your help. I don't know much about SQL Server on Windows, so this stumped me.
Thanks,
-Roy
**********************
"The contents of this message do not reflect any position of the U.S. Government or NOAA."
**********************
Roy Mendelssohn
Supervisory Operations Research Analyst
NOAA/NMFS
Environmental Research Division
Southwest Fisheries Science Center
***Note new street address***
110 McAllister Way
Santa Cruz, CA 95060
Phone: (831)-420-3666
Fax: (831) 420-3980
e-mail: Roy.Men...@noaa.gov www: https://www.pfeg.noaa.gov/
"Old age and treachery will overcome youth and skill."
"From those who have been given much, much will be expected"
"the arc of the moral universe is long, but it bends toward justice" -MLK Jr.