Cannot authenticate user with IAM and VS Catalog with SQL Server

Article Id: 110218
Status: Published
Updated On: 09-08-2018 10:45
Products:
CA Application Test Service Virtualization CA Continuous Application Insight (PathFinder)
Issue/Introduction:

Using admin/admin to log into VS Catalog and getting below error:

Cannot authenticate user. Please enter a valid User Name
& Password.

We had configured with LDAP previously.

Cause:

2018-08-08 09:14:14,297 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Timer-2) The connection is closed.
2018-08-08 09:14:14,297 ERROR [org.keycloak.services] (Timer-2) KC-SERVICES0089: Failed to run scheduled task ClearExpiredClientInitialAccessTokens: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:70)
        at org.keycloak.models.jpa.JpaRealmProvider.removeExpiredClientInitialAccess(JpaRealmProvider.java:675)
        at org.keycloak.models.cache.infinispan.RealmCacheSession.removeExpiredClientInitialAccess(RealmCacheSession.java:1107)
        at org.keycloak.services.scheduled.ClearExpiredClientInitialAccessTokens.run(ClearExpiredClientInitialAccessTokens.java:30)
        at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:56)
        at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner$1.call(ClusterAwareScheduledTaskRunner.java:52)
        at org.keycloak.cluster.infinispan.InfinispanClusterProvider.executeIfNotExecuted(InfinispanClusterProvider.java:78)
        at org.keycloak.services.scheduled.ClusterAwareScheduledTaskRunner.runTask(ClusterAwareScheduledTaskRunner.java:52)
        at org.keycloak.services.scheduled.ScheduledTaskRunner.run(ScheduledTaskRunner.java:45)
        at org.keycloak.timer.basic.BasicTimerProvider$1.run(BasicTimerProvider.java:51)
        at java.util.TimerThread.mainLoop(Timer.java:555)
        at java.util.TimerThread.run(Timer.java:505)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:78)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:78)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:60)
        at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:111)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:429)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:374)
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1361)
        at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:102)
        at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:405)
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:61)
        ... 11 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:710)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2953)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:87)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
        ... 21 more

Environment:

Release:
Component: ITKOVS

Resolution:

Troubleshooting the issue and found a lot SQL connectivity error’s.

Updated Database properties by launching the DatasourceUpdater executable in the Install Directory /IdentityAccessManager/bin folder and then restarted the services.

Able to login now with LDAP and admin credentials.