Symantec Identity Portal - com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'DUAL'.

Products

• CA Identity Suite
• CA Identity Portal

Issue/Introduction

Identity Portal is throwing the error "com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'DUAL'." in the logs. After a build up of time, it causes IP to crash.

Environment

Release : 14.X

Cause

Misconfiguration within the standalone.xml

Resolution

Open the standalone.xml and navigate to the database configuration section where data-source=sigma-ds. Review the below configuration:

Broken Config:
<check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql>
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>120000</background-validation-millis>

Modify the check-valid-connection-sql from SELECT 1 FROM DUAL to SELECT 1

Working Configuration:
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>120000</background-validation-millis>

For standalone environments, you can modify the standalone.xml, stop/start services.

For VAPP environments, follow the below commands:

====VAPP Environment====

add a new user to JBoss:

sudo /opt/CA/wildfly-portal/bin/add-user.sh

What type of user do you wish to add?
 a) Management User (mgmt-users.properties)
 b) Application User (application-users.properties)
(a): a
Username : YourJbossAdmin
What groups do you want this user to belong to? (Please enter a comma separated list, or leave blank for none)[  ]:
Press Enter to leave blank
About to add user 'YourJbossAdmin' for realm 'ManagementRealm'
Is this correct yes/no? yes
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' with groups  to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-groups.properties'
Added user 'YourJbossAdmin' with groups  to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-groups.properties'
Is this new user going to be used for one AS process to connect to another AS process?
e.g. for a slave host controller connecting to the master or for a Remoting connection for server to server EJB calls.
yes/no? no

Next, navigate to /opt/CA/wildfly-portal/bin/
execute ./jboss-cli.sh
type: connect
Enter credentials

Once connected run the below CLI commands:

Read the object:
/subsystem=datasources/data-source=sigma-ds/:read-attribute(name=check-valid-connection-sql)

Write the object:
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=check-valid-connection-sql,value=SELECT 1)

Read the object:
/subsystem=datasources/data-source=sigma-ds/:read-attribute(name=check-valid-connection-sql)

stop_ip

start_ip