search cancel

Automatic database connection restore fails for IAM

book

Article ID: 133552

calendar_today

Updated On:

Products

CA Application Test Service Virtualization

Issue/Introduction

Due to server maintenance, SQL Server is brought down on weekends. After the SQL server is up and running, IAM cannot restore connection to SQL Server database and it stops responding


I can see below error in IAM server.log file

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166)

        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)


Cause

Auto-reConnect on IAM  is not turned on.  It will only Reconnect when IAM Restarts.  

Environment

Release : 10.x

Component : CA Application Test

Resolution

We can make IAM to auto-reconnect to DB when it comes back to running status.

1. Stop IAM server

2. Add the following tag in the standalone.xml (<DevTest-home>/IdentityAccessManager/standalone/standalone.xml) , in the MSSQL datasource tag:

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">

                    <connection-url>jdbc:sqlserver://SV66628S3:50111;databaseName=IAM_10.x_Database</connection-url>

                    <driver>mssql</driver>

                    <security>

                        <security-domain>EncryptDBPassword</security-domain>

                    </security>

         <validation>

                      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker> 

                      <background-validation>true</background-validation> 

                     <background-validation-millis>10000</background-validation-millis>

                      <check-valid-connection-sql>select 1</check-valid-connection-sql> 

                      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/> 

          </validation> 

   </datasource>

3. Restart IAM server.