Error: "Connection is closed. Error: 7886, Severity: 20, State: 2"

Products

CA Identity Manager CA Identity Governance CA Identity Portal

Issue/Introduction

An error message occurs when clicking on a task in the CA Identity Manager (IM) User Console, View Submitted Tasks (VST).

Error loading persistence service: The connection is closed.

The Jboss/Wildfly log shows the following:

ERROR [ims.tmt.persistence] (Thread-1387 (HornetQ-client-global-threads-1191891472)) PersistenceProvider: JMS:ID:91139e95-6ff6-11e8-8c6b-e7c5ad84706c: getTaskSession: Exception getting task session: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) [sqljdbc.jar:]
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388) [sqljdbc.jar:]
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166) [sqljdbc.jar:]...

This error error occurs on the MS SQL database:

-- Begin Error --
Date MM/DD/YYYY HH:MM:SS PM
Log SQL Server (Current - MM/DD/YYYY HH:MM:SS AM)

Source spid90

Message
Error: 7886, Severity: 20, State: 2.

A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

-- End Error --

Event Log (Source: MSSQLSERVER, EventID: 7886):

A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

Environment

CA IDENTITY MANAGER 14.x

Note: Additional comments on isolation settings are relevant to all IM Builds running on MS SQL

Cause

Environmental

Resolution

There are some code changes in the latest Identity Manager 14.1 Cumulative Patch Set (CP10 ) that should prevent these errors.

Workaround:

If you are unable to apply the latest Cumulative path, you can add the datasource validation configuration settings (below) to your standalone*.xml for the task persistence datasource.

<validation>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
<background-validation-example>120000</background-validation-example>
</validation>

Also, 13.13. Common DataSource parameters links to jboss.org that discusses this setting and the benefits of using the match on "true" setting as a pre-check of datasource connections.

Additional Information

In addition to the coded settings above, we recommend MS SQL administrators turn on isolation. The below SQL can be used to enabled isolation settings.

ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON;

<database name> is the name of the IM database.

Note: IDM does not require any changes to the "Allow Snapshot Isolation" setting when "Read Committed Snapshot = TRUE." You can leave the default value which is "Allow Snapshot Isolation=FALSE."

For more information on isolation settings, see the Microsoft documentation Understanding isolation levels.