Unable to connect Squirrel SQL client to UCCX 10.5 database

[jeffjengle]

I'm trying to create custom stored procedures for CUIC custom reports using CUIC Premium. I installed and setup Squirrel, no problem. When I try to connect Squirrel to the UCCX database, I get:

Caused by: java.sql.SQLException: Incorrect password or user com.informix.asf.IfxASFRemoteException: uccxhruser@10.1.199.10[jeffjengle-nb] is not known on the database server.

at com.informix.jdbc.IfxSqliConnect.<init>(IfxSqliConnect.java:1328)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.informix.jdbc.IfxDriver.connect(IfxDriver.java:237)

at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)

I'm connected via a Fortinet VPN and wondered if the Informix server might require an entry to allow access via JDBC from a remote computer? I'm pretty sure I got the username and password right (it worked when i configured the data source in CUIC).

[jacparke]

If it worked for CUIC, it should also work for Squirrel.  I'm not that familiar with Squirrel, but Informix and I are old friends.

There are multiple things you have to get right, the Server, the database and the userid.  It looks like you've got the server and user correct - how about the database?

You can check the user id with a simple CUIC report:  "select username, user type from sysusers", your userid should show in that result set.

You can check the database name with another simple CUIC report: "select name from sysmaster:sysdatabases"

You can check the server with another simple CUIC report: "select cf_name, cf_effective from sys master:sysconfig where cf_name='DBSERVERNAME'"

Regards,

Jack Parker

[jeffjengle]

Hi Jack,

Thanks for the response. I did check the user name using the CLI: show uccx dbserver user list, and I am assuming the error message would have indicated if the server or database were wrong. I will do the queries you suggested just to make sure.

Do you know if Informix checks the client IP address and client OS name? I'm suspicious that the error message reported the user as: uccxhruser@10.1.199.10[jeffjengle-nb]. I've used other databases that require an entry into a file like etc/hosts.equiv or ~/.rhosts.

[jacparke · Accepted solution]

I should have been clearer.  The third code snippet is checking $INFORMIXSERVER (the database instance or "server", not the host or physical "server").  My memory is hazy on UCCX, there might be two informix servers on the host, if Squirrel is trying to connect to the other one, the userid would probably be different.  You differentiate between the two (if there are two) with $INFORMIXSERVER and/or the port number used to connect.

Informix will use the OS for password validation, but the userid itself has to be in sysusers, the host that that userid originates from is unimportant.  So johndoe@wherever should be the same as johndoe@127.0.0.1

hosts.equiv or .rhosts can be used for trusted communication between servers at the database level - for things like replication, but should not be required for a user sign on.

Is it just me or is the autocorrect feature here terrible?

The first sniper should have been:

"select username, usertype from sysusers"

The third snippet should have been:

select cf_name, cf_effective from sysmaster:sysconfig where cf_name='DBSERVERNAME'

I should also have pointed out that the data source for these snippets is ${CUIC}.

Regards,

Jack Parker

[jeffjengle]

Hi Jack,

Thank you, based on your information I reset the password for the uccxhruser and verified it propagated properly (it took a few minutes). I then got a different error message that the INFORMIXSERVER wasn't found, located my typom, and am now connected. All set, thanks again.