Debugging IQX BDE Connection Failures

please click image to enlarge

The login screen takes three parameters:

Database should be the name of an ODBC DSN defined on the local computer.

User is the IQX user Login Name held in the database. It is often but not necessarily the same as users Windows user name.

Password is the case sensitive password associated with the User in IQX.

If you have access to the IQX system through another computer or login, then the Connections view in Maintenance | Database Diagnostics | Connection can help to identify issues with the login parameters.

With Failed pressed, the Connections view will show attempted logins that were able to connect to the database but were rejected because:

• the password was incorrect - Invalid Password
• the User was not defined in the database - Invalid User Name
• the user was defined but was marked as Not in Use in Maintenance | Users - User 'Not In Use'

The relevant record in Connections can be found using IP address of the computer that is attempting to connect and the time of the attempted connection. If there is no corresponding record, then then IQX has not been able to connect to the database.

If IQX cannot connect to the database at all (see above) then the problem may lie with the Database parameter. This must correspond to an ODBC DSN on the local computer - ie the database name supplied in the Login screen and the DSN name must be exactly the same. The DSN is usually defined as a System DSN (ie one available to all users) and is normally created when IQX is installed on the computer. (System DSNs can only be created or modified by users with at least Local Administrator rights in Windows).

The settings in the DSN can be inspected or edited using the Windows ODBC Data Source Administrator.

The exact layout of the DSN entry in the ODBC Administrator varies depending on the installed version of the SQL Anywhere client, but the essentials are the same. There are four tabs: ODBC, Login, Database, Network & Advanced.

ODBC

The Data Source Name - the DSN name, must be exactly the same as the Database name entered in the IQX Login Screen - the other options should be as shown in the picture above.

Login

The fields here should be left blank - the values are supplied by the IQX application during the login process - these are database level credentials and are not the same as the IQX user's login name and password.

Database

The important values here are Server name and Database name. These should correspond exactly with the server and database names advertised by the database engine on the database server¹⁾. The person who administers the database server will know these values.

The other fields should be left blank (except for the encryption key if you use encryption). The “stop database” option defaults to ticked in some SQL Anywhere versions but is ignored for non-local databases.

Network

The TCP/IP box should be ticked and the Shared Memory box un-ticked. Depending on your network configuration you may have to supply a host setting as shown. the host can be specified by IP address or by a name that is resolved by your DNS server. If you database engine is listening on a non-standard port (the standard is 2638) then the port should be specified with a colon - as shown above.²⁾

Windows 7 & Windows 8 clients appear to always need a host setting.

Advanced

There may be a number of setting configured here and they should not be changed without specific advice. However the “Display debugging information in a log file” setting can be used to identify network connection issues. To do this, tick the box and specify a full file path eg d:\logs\sqla.txt (make sure it is a location that the user has permission write to - not c:\ etc)

Once the changes have been saved, retry connecting with IQX and then inspect the log file. The log file will give a detailed history of how the ODBC driver has tried to connect to the server. Interpretation will require a knowledge of your network layout but some pointers are:

• If the driver cannot find the server and you have specified the IP address - try pinging the server from the local computer and other checks on basic network connectivity

• If the server computer is found but not the database engine, check port settings and firewall rules

• If the driver is able to find the database engine but has its connection rejected because of incorrect password, you have probably not got the correct entries in your pears.key file - contact IQX Support for help if you are unsure what to do.

• If the driver seems to be looking at incorrect IP addresses or server host names, incorrect values may have e been cached following a change in network configuration. These usually happens only when a host IP address is not specified - find and delete the sql.ini or sasrv.ini file on the local computer - the name and location will vary depending on operating system and SQL Anywhere version.

• If the driver is able to connect successfully to the database, but is then disconnected - either your user name or password supplied in the Login screen are incorrect (see above) or you have a corrupt BDE³⁾ installation.
  
  The simplest action that may resolve this is to remove the BDE alias for the ODBC DSN - which will force its re-creation. This can be done (with Administrator rights) using the BDE Administrator utility BDEADMIN.EXE - typically found in C:\Program Files\Borland\Common Files\BDE. When this is opened a list of Database Aliases corresponding with ODBC DSNs is given. Right click on the relevant DSN name and choose Delete. Then close the BDE Administrator and re-try the connection.
  
  If this does not work, the next step is to try re-installing IQX on the local computer - this will force the re-install the BDE components.

Example of a successful connection:

Tue Aug 13 2013 11:44:52
11:44:52 Attempting to connect using:
UID=pears;PWD=********;DBN=v10Test;ENG=laptop10;CON=auser;ASTOP=YES;INT=NO;DBG=YES;LOG=d:\logs\sqla.txt;DMRF=NO;LINKS=TCPIP{};COMP=NO
11:44:52 Attempting to connect to a running server...
11:44:52 Trying to start TCPIP link ...

11:44:52 TCP using Winsock version 2.2
11:44:53 My IP address is 192.168.1.235
11:44:53     TCPIP link started successfully

11:44:53 Attempting TCPIP connection (address 192.168.1.10:2638 found in sasrv.ini cache)

11:44:53 Trying to find server at cached address 192.168.1.10:2638 without broadcasting
11:44:53 Found server, verifying server name
11:44:53 Found database server at address 192.168.1.10
11:44:53 Found database server IQXServer on TCPIP link
11:44:53 Connected using client address 192.168.1.235:19735
11:44:53 Connected to server over TCPIP at address 192.168.1.10:2638

11:44:53 Connected to SQL Anywhere Server version 10.0.1.4310
11:44:53 Application information:
11:44:53 IP=192.168.1.235;HOST=LAPTOP17;OS='Windows XP Build 2600 Service Pack 2';PID=0xab4;THREAD=0x11d0;EXE='C:\Program Files\IQX\iqx.exe';VERSION=10.0.1.4310;API=ODBC;TIMEZONEADJUSTMENT=60
11:44:53 Connected to the server, attempting to connect to a running database...
11:44:53 [    3] Connected to database successfully