Automatic database connection restore fails for IAM

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)

Environment

Release : 10.x

Component : CA Application Test

Cause

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

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.