Debugging IQX BDE Connection Failures [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Debugging IQX BDE Connection Failures ======

<panel type="primary" title="Scope">This article is primarily concerned with resolving login failures rather than disconnection problems. Disconnection problems are almost invariably caused by faulty network connections.

This article applies to older versions of IQX that use the Borland Database Engine (BDE) for connections (see Help | About, if in doubt). For current versions of IQX that buse FireDAC see [[sa26-00|Debugging IQX Connection Failures]].</panel>

===== Overview of relationship between ODBC, database and server names===== 

[[http://www.iqxusers.co.uk/iqxhelp/lib/exe/fetch.php?media=iqxshortcuts-odbc-servernames.pdf|{{ :iqxshortcutsodbcservernames.jpg?direct |Overview of IQX connections - please click to enlarge}}]]

//please click image to enlarge//

===== The IQX Login Screen =====


The login screen  takes three parameters:

{{ :iqx_login_screen.jpg?nolink |}}

===== Database, User & Password =====


**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.

===== Identifying Issues With Login Parameters =====

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 <key>Failed</key> 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.

===== Issues with the ODBC DSN or the Network =====

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. 

<note warning>On 64-bit systems care must be taken to use the 32-bit version of the ODBC Administrator - the version accessible from Control Panel | Administrative tools will be the 64-bit version. The 32-bit version is typically located at //c:\windows\syswow64\odbcad32.exe//.</note>   

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.

<note tip>Changes to the ODBC DSN are not effective until the <key>OK</key> button is pressed.</note>

**ODBC**

{{ :odbc-initial.jpg?nolink |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**

{{ :odbc-login.jpg?nolink |ODBC-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**

{{ :odbc-database.jpg?nolink |ODBC-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((set by the -n parameters in the database engine service configuration)). 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**

{{ :odbc-network.jpg?nolink |ODBC-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.((Non-standard ports are usually used when more than one SQL Anywhere database engine service is running on a server. If this is the case it is best to specify the required port in every service configuration eg -x tcpip(ServerPort=5654) otherwise confusion can arise))

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

**Advanced**

{{ :odbc-advanced.jpg?nolink |ODBC-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((a communications layer that sits between IQX and the ODBC driver)) 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:
<code>

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
</code>