Unable to obtain Jdbc connection from DataSource after applying latest Patch preventing startup.

Products

CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

After upgrading from 6.7.0.124 to 6.7.0.398 Release Automation is not available in the browser and appears to not be starting correctly.

The Center_Upgrade.log and the nolio_dm_all.log contain the following error which holds the key to this problem:
2021-02-08T09:17:32.360-05:00 [localhost-startStop-1] ERROR (com.nolio.platform.server.dataservices.services.upgrade.UpgradeServiceImpl:268) - Error calling Flyway Migrate
org.flywaydb.core.api.FlywayException: Unable to obtain Jdbc connection from DataSource
at org.flywaydb.core.internal.util.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56)
... {stack truncated} ...
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication. ClientConnectionId:3820b808-1724-4b7a-9cff-189145addca8)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2152)
at org.flywaydb.core.internal.util.jdbc.JdbcUtils.openConnection(JdbcUtils.java:50)
... 272 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:3820b808-1724-4b7a-9cff-189145addca8
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3151)
...
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2148)
... 275 more
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-8.4.1.x64 in java.library.path
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1860)

Cause

Nolio cumulative fix/build 6.7.2/b168 and 6.6.6/b10304 ships with an updated JDBC driver for MSSQL. Specifically, it uses version 8.4 (mssql-jdbc-8.4.1.jre8.jar) now. This jdbc driver requires an updated authentication DLL to successfully use integratedSecurity.

The version is called out in the error message:
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-8.4.1.x64 in java.library.path

Environment

Release : 6.6 and 6.7

Component : CA RELEASE AUTOMATION RELEASE OPERATIONS CENTER

Resolution

Note:

The updated mssql-jdbc_auth-8.4.1.x86.dll will be included in future cumulative fixes. This is true for both the management server and agent installers.

Steps to manually solve:

If you need to manually fix this issue after applying a cumulative patch that upgraded the MSSQL JDBC driver, but did not include the updated .dll needed for Windows Authentication (aka integratedSecurity) then:

Please see the steps below to solve the NAC's connection problems to the Database;
Please see the steps outlined in the "Additional Information" section for information related to actions that may be impacted by this update and how to solve those problems if applicable.

Please review the error message closely to ensure you are gathering the correct files from Microsoft:
1. Download the 8.4.1 JDBC Driver from Microsoft:
https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15#previous-releases

2. Then using your archive tool of choice unpack and locate the mssql-jdbc_auth-8.4.1.x64.dll from the sqljdbc_8.4.1.0_enu\sqljdbc_8.4\enu\auth\x64\ folder

3. Stop the Nolio Server service on the NAC.

4. Remove/Move the [nolio home]\bin\sqljdbc_auth.dll file

5. Place the mssql-jdbc_auth-8.4.1.x64.dll in the \CA\ReleaseAutomationServer\bin\ directory.

6. Restart Nolio Server service.

Additional Information

Note:

The updated mssql-jdbc_auth-8.4.1.x86.dll will be included in future cumulative fixes. This is true for both the management server and agent installers.

Replacing the old jdbc driver with the updated jdbc driver (via the cumulative patch) may also impact some actions that use Windows Authentication. These two actions are examples of actions that would be impacted:

Execute SQL Query on Microsoft(c) SQL Server
Run SQL File on Microsoft(c) SQL Server

Specifically, these actions would be impacted if these actions are used with their "useWindowsAuthentication" setting set to true - as seen here:

If you have actions that are configured this way then you will need to also update the agents to include the updated .dll. This can be done, manually, by stopping the agent, copying the new mssql-jdbc_auth-8.4.1.x86.dll to the agents bin folder and starting the agent. Or, this can be done via the Agent Upgrade feature via the ROC. To apply the file through the agent upgrade feature:

To update the jdbc_auth .dll on agent machines via Agent Upgrade feature (available in v6.6 and 6.7 when the "Use new upgrade procedure for Linux agents" is deselected):

Add the following line in the upgradeAgent/-1975/-1975/upgradeData/remove.txt file
./bin/sqljdbc_auth.dll
Copy the mssql-jdbc_auth-8.4.1.x86.dll from the JDBC driver archive into the [nolio home]\upgradeAgent\-1975\-1975\upgradeData\upgradeFiles\bin\ mssql-jdbc_auth-8.4.1.x86.dll.new

To update the jdbc_auth .dll on agent machines via Agent Upgrade feature (available in 6.7 when the "Use new upgrade procedure for Linux agents" is selected):

unzip the [nolio home]\agentBundles\agent-upgrade-bundle-linux-x86-64-unknown.zip.
untargz the nag-installed.tar.gz file.
replace the bin\sqljdbc_auth.dll file with the mssql-jdbc_auth-8.4.1.x86.dll file.
targz and zip the folders back to get the correct agent bundle.

Once the steps above have been done you will need to upgrade the agent in order for them to get the new .dll.

If you are not sure whether or not the action is being used with the useWindowsAuthentication setting set to true, you use the following query to help isolate applications/components where the action is being used:

select ap.name as ACTION_PACK_NAME, c.component_name as COMPONENT_NAME, app.app_name as APP_NAME, e.name as ACTION_NAME from executable e
inner join model_steps m on e.ID = m.id
inner join action_instances a on m.action = a.ID
inner join action_class_info aci on aci.ID = a.actionInfoId
inner join action_packs ap on ap.ID = aci.actionPackId
inner join components c on e.container_id = c.ID
inner join applications app on c.app_id = app.id
where aci.class_name like '%MSSQL';

The output of the query will include the name of the application where instances of those actions are found. If it is found in a shared component then the APP_NAME will shows "____Components Repository".

The output of the query will also include the name of the component where instances of those actions are found.

With the output of the query you can check Nolio and/or your application teams to see if those actions are configured with it's field useWindowsAuthentication = true