Using Oracle RDS SSL connections in Spring Boot applications

configure the truststore in the same way as per the documentaion https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.SSL.html , standalone application i can able to connect but in the springboot not able to connect facing the chllanges, it would more appreciate to find the solution.

steps :

1.Download the root certificate that works for specific AWS Region and put the file in the ssl_wallet directory.

Ex: C:/oracle/ssal_wallet/rds-ca-2019-root.pem

2.Convert the certificate to .der format openssl x509 -outform der -in rds-ca-2019-root.pem -out rds-ca-2019-root.der

3.Import the certificate into the keystore using keytool -import -alias rds-root -keystore clientkeystore.jks -file rds-ca-2019-root.der

4.Confirm that the key store was created successfully

keytool -list -v -keystore clientkeystore.jks
C:/oracle/ssal_wallet>keytool -list -v -keystore clientkeystore.jks
Enter keystore password: Keystore type: PKCS12 Keystore provider: SUN

Your keystore contains 1 entry

Alias name: rds-root Creation date: 12 Oct 2023 Entry type: trustedCertEntry

5. modified the application.properties with trustore path

spring.datasource.url=DB_URL=jdbc:oracle:thin:@(DESCRIPTION=(SDU=8192)(ADDRESS=(PROTOCOL=TCPS)(HOST=myrdshostIp)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=EBMBBBS))(SECURITY=(SSL_SERVER_CERT_DN="C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=myRdsHostIp")))

spring.datasource.username=myuser spring.datasource.password=mypassword #spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.datasource.hikari.data-source-properties.oracle.net.ssl_trust_manager_type=JKS spring.datasource.hikari.data-sourcessl_trust_manager_password=mypassword spring.datasource.hikari.data-source-properties.oracle.net.ssl_trust_manager_file=C:/ORACLE/ssl_wallet/clientkeystore.jks

6. pom.xml dependencies

<dependency> <groupId>com.oracle.database.jdbc</groupId> <artifactId>ojdbc8</artifactId> <version>your-driver-version</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>

7. created a service or controller in mySpring Boot application that connects to the database and perform a simple query

Expected to connect but it is throwing below error :

stack trace:

2023-10-12 17:12:39,151 [main] DEBUG [ConstructorResolver.java : createArgumentArray : 808 ] org.springframework.beans.factory.support.ConstructorResolver -Autowiring by type from bean name 'entityManagerFactory' via factory method to bean named 'entityManagerFactoryBuilder' 2023-10-12 17:12:39,178 [main] DEBUG [LocalContainerEntityManagerFactoryBean.java : createNativeEntityManagerFactory : 361 ] org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean -Building JPA container EntityManagerFactory for persistence unit 'default' 2023-10-12 17:12:43,291 [main] ERROR [AbstractEntityManagerFactoryBean.java : buildNativeEntityManagerFactory : 426 ] org.springframework.orm.jpa.AbstractEntityManagerFactoryBean -Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1156) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:910) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:731) at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) at org.springframework.boot.SpringApplication.run(SpringApplication.java:307) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1303) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1292) at com.wipro.ExcelToDbApplication.main(ExcelToDbApplication.java:78) Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:421) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ... 16 more Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:71) at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:63) at org.hibernate.tool.schema.extract.spi.ExtractionContext.getQueryResults(ExtractionContext.java:43) at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:39) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:66) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:60) at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:183) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:104) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:196) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:85) at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1498) at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ... 20 more Caused by: java.sql.SQLRecoverableException: IO Error: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 25 ms., Authentication lapse 0 ms. at oracle.jdbc.driver.T4CConnection.handleLogonIOException(T4CConnection.java:936) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:701) at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1042) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:90) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:733) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:649) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:181) at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:44) ... 35 more Caused by: java.io.IOException: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 25 ms., Authentication lapse 0 ms. at oracle.jdbc.driver.T4CConnection.handleLogonIOException(T4CConnection.java:931) ... 50 more Caused by: java.io.IOException: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target, connect lapse 25 ms. at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:202) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:350) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:2372) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:657) ... 49 more Caused by: java.io.IOException: IO Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at oracle.net.nt.SSLSocketChannel.wrap(SSLSocketChannel.java:719) at oracle.net.nt.SSLSocketChannel.wrapHandshakeMessage(SSLSocketChannel.java:594) at oracle.net.nt.SSLSocketChannel.doSSLHandshake(SSLSocketChannel.java:465) at oracle.net.nt.SSLSocketChannel.write(SSLSocketChannel.java:149) at oracle.net.ns.NIOPacket.writeToSocketChannel(NIOPacket.java:361) at oracle.net.ns.NIOConnectPacket.writeToSocketChannel(NIOConnectPacket.java:256) at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:157) ... 52 more Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:371) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:314) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:309) at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:654) at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473) at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369) at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:396) at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:480) at java.base/sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:1277) at java.base/sun.security.ssl.SSLEngineImpl$DelegatedTask$DelegatedAction.run(SSLEngineImpl.java:1264) at java.base/java.security.AccessController.doPrivileged(AccessController.java:712) at java.base/sun.security.ssl.SSLEngineImpl$DelegatedTask.run(SSLEngineImpl.java:1209) at oracle.net.nt.SSLSocketChannel.runTasks(SSLSocketChannel.java:774) at oracle.net.nt.SSLSocketChannel.doSSLHandshake(SSLSocketChannel.java:457) ... 56 more Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:439) at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:306) at java.base/sun.security.validator.Validator.validate(Validator.java:264) at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:285) at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:144) at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:632) ... 66 more Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.provider.certpath.SunCertPathBuilder.