Easysoft ODBC-ODBC Bridge

How do I use oobping to test the connection to a remote ODBC DSN?

Article:
00821
Last Reviewed:
28th October 2008
Revision:
2

You should first use oobping to test the connection to the OOB Server and to test authentication -- see other oobping FAQs. Once you have completed that, you should have the name of a server where an OOB Server is running and the port it is listening on. You should also have a valid operating system username and password and be enabled in the OOB Server access control rules. You can now check connectivity to a specified remote data source (DSN).

There are two ways to check the connection to a remote DSN with oobping. The simplest way is to add a local DSN to your odbc.ini file and then specify the DSN in the -d argument as "DSN=dsnname". This method tests you have defined the local OOB client DSN correctly and put the definition in the right file. An alternative method is to specify all the connection attributes in the -d argument, not just the DSN.

For example, suppose as a result of checking the connection to the OOB Server and OOB Server authentication you have the following information:

Server = myserver
Port = 8888
LogonUser = me
LogonAuth = mypassword

Now you add to that the name of the target DSN on myserver (this is the OOB client attribute TargetDSN). Note that the TargetDSN must be a remote SYSTEM data source, you cannot access USER data sources. If the database also needs login information the database username and password are specified using the OOB attributes TargetUser and TargetAuth.

If you want to test a DSN in your odbc.ini file then it would look something like:

[mydsn]
ServerPort = myserver:8888
LogonUser = me
LogonAuth = mypassword
TargetDSN = mysystemdsn
TargetUser = dbusername
TargetAuth = dnpassword

Note Later when using the unixODBC driver manager you will need to add "Driver=OOB" to the above DSN but this is not needed when using oobping.

You can run oobping as follows:

oobping -d "DSN=mydsn;"
Using Connection string :
DSN=mydsn;
Connected OK

01000:1:5701:[NetConn: 032bc620][Microsoft][ODBC SQL Server Driver]
[SQL Server]Changed database context to 'pubs'.
01000:2:5703:[NetConn: 032bc620][Microsoft][ODBC SQL Server Driver]
[SQL Server]Changed language setting to us_english.
OutConnectionString:
DSN=mydsn;UID=dbusername;PWD=dbpassword;
TARGETDSN=mysystemdsn;LOGONUSER=me;LOGONAUTH=mypassword;

Connected to database:  pubs
DBMS Name:  Microsoft SQL Server
Driver Name:  esoobclient
Driver Version:  01.00.0043
Disconnecting

Here you passed the ODBC connection string "DSN=mydsn" to the OOB client via oobping. The OOB client did not have sufficient attributes in the connection string to define what it should do, so it looked up the DSN 'mydsn' in the odbc.ini file to get additional attributes where it found ServerPort, LogonUser, LogonAuth, TargetDSN, TargetUser and TargetAuth. The OOB client then connected to the OOB Server on myserver port 8888, logged you in with LogonUser/LogonAuth values and finally connected via ODBC to the remote data source 'mysystemdsn'. In the above example the DSN pointed to MS SQL Server and there were some informational diagnostics returned saying the language is "us_english" and the database is "pubs" (not all databases will return informational messages on the connection). The OutConnectionString is a string returned by the OOB client which you can use to connect to this data source again (see below). The final messages show the database, DBMS Name, Driver name/version (OOB in this case obviously).

Instead of specifying just the name of a DSN to oobping and defining the other connection attributes in the odbc.ini file you can specify all the connection attributes in one go and not use an odbc.ini file. The OutConnectionString above shows you what connection string you could have passed to the OOB client to connect without a DSN (if you remove the "DSN=mydsn;").

For example:

oobping -d "UID=dbusername;PWD=dbpassword;SERVERPORT=myserver:8888;
TARGETDSN=mysystemdsn;
LOGONUSER=me;LOGONAUTH=mypassword;"

would produce the same result as above.

You might be slightly confused why you specify TargetUser/TargetAuth in the odbc.ini file but UID/PWD in the connection string. Strictly speaking, the ODBC defined attributes are UID/PWD and they are passed to the database for database authentication. As far as ODBC connection strings are concerned UID/PWD and TargetUser/TargetAuth are synonymous in OOB but if specified in the odbc.ini file you should alway use TargetUser/TargetAuth.

Now you know how to use oobping with connection string here are some examples of common problems you might see:

oobping -d "UID=dbuser;PWD=dbauth;TargetDSN=test;
LogonUser=me;LogonAuth=mypassword"
Using Connection string :
UID=dbuser;PWD=dbauth;TargetDSN=test;
LogonUser=me;LogonAuth=mypassword

IM002:1:0:[Easysoft ODBC (Client)]
Data source not found and no default driver specified
HY000:2:0:[Easysoft ODBC (Client)]
general error: Missing attribute(s): SERVERPORT

The initial diagnostic, IM002 is defined by ODBC but as it rather vague the OOB client added a secondary more helpful diagnostic. Here the OOB connection attribute 'ServerPort' was omitted and so the OOB client did not know which server to connect to.

oobping -d "ServerPort=myserver:8888;UID=dbuser;PWD=dbauth;
TargetDSN=test;LogonUser=me;LogonAuth=mypassword"
Using Connection string :
ServerPort=myserver:8888;UID=dbuser;PWD=dbauth;TargetDSN=test;
LogonUser=me;LogonAuth=mypassword

28000:1:18456:[][Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed for user 'dbuser'.

The above error comes from MS SQL Server and suggests the database password 'dbauth' is not correct for the database user 'dbuser'. This error illustrates an important point with ODBC errors and that is how to identify what component is reporting the error. You should examine the components in [], the one furthest right is the reporting component and as you move left through the components you are moving closer to the OOB client.

For example, if you specified a TargetDSN which did not exist on the server machine, the driver manager on the remote machine would be the component reporting the error and so the diagnostic (for Windows) would be:

IM002:1:0:[][Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified

The other important thing to note here is that oobping outputs any ODBC diagnostics as:

ODBC State : diagnostic sequence : ODBC native error: text

The native error code is specific to the component reporting the error, for example in the SQL Server login failure above you can look up native error code 18456 in the SQL Server documentation which should tell you precisely what circumstances this is reported.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.