Unable to create external datasource for IAM with warning: The datasource was created successfully but the connection to the database failed

Products

• CA Identity Suite

Issue/Introduction

We are using the DB url string in the properties file: DB_URL=jdbc:sqlserver://<ip>:1433;database=<dbname>;trustServerCertificate=false

But the datasource creation failing with below warning messages

[OK] successfully configured external datasource from file "/opt/CA/VirtualAppliance/custom/IdentityManager/dataSources/mssql_azure_test.properties" on xx.xx.xx.xxx (return code: 0)
=================================================================================
[INFO] Successfully created security-domain: test_ds_azure
[INFO] Successfully created datasource: test_ds_azure
[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...

Environment

Release : 14.5
Component : CA Identity Suite Virtual Appliance

Resolution

We are able to add the data source successfully with addJBossDatasource alias.

Please follow below instructions to add the datasource:

Make sure you don't have the existing entries for datasource, security-domain for the specific datasource that we are trying to add.
Prepare the properties file with all required information,

For DB_URL, if the database is non-ssl make sure pass extra parameters like encrypt=false;trustServerCertificate=true as the latest MS-SQL driver by default supports only SSL

Example: DB_URL=jdbc:sqlserver://xx.xx.xx.xxx:1433;selectMethod=cursor;databaseName=dbName;encrypt=false;trustServerCertificate=true

If the database is SSL, 

Example: DB_URL=jdbc:sqlserver://xx.xx.xx.xxx:1433;databaseName=dbName;integratedSecurity=true;encrypt=true;trustServerCertificate=false;trustStore=storeName;trustStorePassword=storePassword

 

Restart the Identity Manager service to remove the cache entries in the application server and load the new JDBC URL into memory for use.

Make sure your Identity Manger service is up and running, as the alias will internally use jboss-cli to connect and modify the xml file.

Now run the alias addJBossDatasource mssql_azure_test.properties from the /opt/CA/VirtualAppliance/custom/IdentityManager/dataSources folder.

Additional Information

In some environments, after running the addJbossDatasource alias, the Application must be reloaded.

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.