WFLYJCA0047: Connection is not valid when creating a datasource and testing the connection
Article ID: 279555
Updated On:
Products
Issue/Introduction
When creating a data source using addJBossDatasource, it generates a warning:
[WARN] The datasource was created successfully but the connection to the database failed: [{ "outcome" => "failed" "failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid", "rolled-back" => true, "response-headers" => {"process-state" => "reload-required"}}][WARN] The datasource was created successfully but the connection to the database failed: Connection is not valid - Aborting...
When testing the connection:
cd /opt/CA/wildfly-portal/bin
./jboss-cli.sh --connect controller=127.0.0.1:9991 --commands="/subsystem=datasources/data-source=<database connection name>:test-connection-in-pool", it gives an error:
{ "outcome" => "failed", "failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid", "rolled-back" => true, "response-headers" => {"process-state" => "reload-required"}}
Also, the IP log contains the following message:
com.microsoft.sqlserver.jdbc.SQLServerException: 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".
Environment
Virtual Appliance 14.5
Cause
The SQL Server certificate is not trusted by WildFly server running IP.
Resolution
Change DB_URL parameter in the file supplied for addJBossDatasource command by adding trustServerCertificate=true in the end.
For example:
Line which gives the problem:
DB_URL=jdbc:sqlserver://<SQL Server host name or IP address>:1433;DatabaseName=<database name>
Updated line which solves the problem:
DB_URL=jdbc:sqlserver://<SQL Server host name or IP address>:1433;DatabaseName=<database name>;trustServerCertificate=true
In some environments the Wildfly Application must be reloaded for the datasource to be loaded into memory for usage.
See KB 39417 Add users to wildfly management console to configure datasources/drivers for IDM for details on adding a user to Wildfly
Then once logged into the Wildfly Console on port 9990 issue the 'reload' command.
Additional Information
Feedback
