CJAP - Losing reference to users in sql table - Requires restart to resync

[gohabsgo]

Hey Folks,
We're running Cognos11.0.6, SQL 2014 and we're using the JDBCSample CJAP for our user repository.  Everything works really nicely for the most part, we're able to use everything successfully with SSO.

The issue we're seeing is that every so often (without any pattern that we can discern) our users will not be able to login at all, it will fail to authenticate them.  When we go into the Admin to check on the users the namespace they belong to has no reference to them, if we check a user group that we've added those users to, it will show as unavailable.  The users do exist just fine in our SQL table however.  If we run a consistency check it will detect the users are not being present even though they are;

CM-ADM-4006 The object named 'USERID_X' at first logon, created at the time '2017-12-13T07:09:07.700Z', of class 'account', with the search path 'CAMID("NamespaceID:u:10")', no longer exists in the extenal authentication provider with the namespace name 'NamespaceID'.

Now if I cycle the cognos web service, it picks up all the users just fine and they can once again login and do their thing.  Until it happens again, necessitating a restart.  This isn't a big deal in non-production environments, however it is happening more frequently in our production environments.

Any thoughts or observations that might help us point to a root cause would be most appreciated!

-Dan

[gohabsgo]

I seem to see this note in the logs file that might be relevant;

JSM      6          1          Audit.JSM          Run       Error connecting to the database, some services may not work properly. Check the logs for further details.   The TCP/IP connection to the host XXX, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".    Failure   

And then about 12 hours later it reestablishes itself.

[CognosAdmn]

Hi Dan,

I am not an expert with Custom Java Provider namespaces, but I am throwing some ideas hoping it would help.

I've experienced similar issues where 'some' of our CJAP user's weren't able to get into Cognos until we recycled Cognos services. This happens usually, once a month on the day when SQL Server DB and Cognos servers get rebooted. In order for CJAP authentication to work the DB needs to be fully available before Cognos services starts up. It sounds like Cognos will pick up the users info from the DB when it restarts and store the information. Could it be that on the days when your users experience this issue that something went on with the DB table?

Also, do you have more than one Authentication provider? I have Active Directory and CJAP. When I only login with AD credentials, then all CJAP users and groups/role will show up as unavailable (or vice versa).

Also, are you on a multi-server environment? The intermittent login issue could be caused by hitting gateways (or application servers) that are missing a setting in one of the servers.

I hope this helps.


Regards,
Shiyam

[gohabsgo]

Hi Shiyam,
Thanks for the reply, I appreciate it.

We only have the one authentication provider and are on a single server deployment.

It's certainly something as you mention to do with the table becoming unavailable for some reason (that we're trying to determine).  I also see what you mention that it's only "some" of the users in the cjap that can't get in.  It's never all of them.

I think my best bet is to really pinpoint when it's happening so I can crossreference with the db/event/cognoslogs with that time frame.  I'm going to develop a little app that logs into Cognos every X hours and then if it fails, notify me so I can use that timestamp.

I'll followup if I can find a root cause.

-Dan

[CognosAdmn]

Hi Dan,

That's a neat trick to have your app login into Cognos at various times and notify upon failure. I hope it helped you identify the root cause.


Regards,
Shiyam