The connection is closed.:Error: 7886, Severity: 20, State: 2.

book

Article ID: 105755

calendar_today

Updated On:

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:

2018-06-14 12:15:48,325 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 6/14/2018 12:15:48 PM
Log SQL Server (Current - 6/18/2018 7:09:00 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 --
 

Cause

Environmental

Environment

CA IDENTITY MANAGER 14.x

 

Please note Additional comments on isolation settings is relevant to all IM Builds running on MS SQL

 

Resolution

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

 

If you are unable to apply the latest Cumulative Patch 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-millis>120000</background-validation-millis>
</validation>

Also, find below a link to jboss.org that discusses this setting and the benefits of using the match on "true" setting as a pre-check of datasource connections:

https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.jboss.org%2Fjbossas%2Fdocs%2FServer_Configuration_Guide%2Fbeta500%2Fhtml%2Fch13s13.html&data=02%7C01%7Cgjmills%40kcc.com%7C70d6368c10234988ee5508d5d6bee384%7Cfee2180b69b64afe9f14ccd70bd4c737%7C0%7C0%7C636651037719358747&sdata=DIZJgj%2BKXZ%2BvUN%2FuH85xx%2FquKFBgVU653AuYQNVHFFI%3D&reserved=0
.

Additional Information

In addition to these coded settings discussed 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.

 

For more information on isolation settings, see the Microsoft documentation (link below)


https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15