GEONETWORK_DB_CONNECTION_PROPERTIES for SQL Server

[AbdulkadirAtAnb]

It seems that the property GEONETWORK_DB_CONNECTION_PROPERTIES is not used when using SQL server as the database. We added "encrypt=true;trustServerCertificate=true" to GEONETWORK_DB_CONNECTION_PROPERTIES but according to the logs it still tries to connect with "trustServerCertificate=false"

2024-04-04T11:41:43,255 WARN  [geonetwork.databasemigration] -   - Migration: Exception running migration for version: 4.4.3-0 subversion: SNAPSHOT. Cannot create PoolableConnectionFactory ("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:5726bea6-77a8-4256-ba88-69a1833ef109)

I'm not a Java developer by any means but shouldn't jdbc.connectionProperties also be referenced in sqlserver.xml?

[juanluisrp]

Yes, you are right! jdbc.connectionProperties is not used in sqlserver.xml and it should. We don't test GN with SQL Server too much, to be honest. If you can send a Pull Request to the GeoNetwork project it would be great. I'm not sure about the JDBC connection string syntax for SQL Server but it would probably be something like

    <bean id="jdbcURL" class="java.lang.String">
        <constructor-arg value="jdbc:sqlserver://${jdbc.host};database=${jdbc.database};integratedSecurity=false;${jdbc.connectionProperties}"/>
    </bean>

As a workaround you could create a custom image of GN overwriting the /opt/geonetwork/WEB-INF/config-db/sqlserver.xml file or mount a fixed one externally.