![]() |
![]() |
![]() |
![]() |
|||
Configuration
Configuring the Easysoft ODBC-ODBC Bridge
This chapter covers configuring the bridge at a lower level, for example to tune the system or work around bugs in certain database engines. For a straightforward explanation of how to configure a bridge DSN to connect to a remote data source, see Chapter 3, "Connection".
Chapter Guide
· ODBC Driver Managers
· The OOB Server under Windows 95/98
· The OOB Server under Windows 2000/NT
· Configuring the OOB Server under Unix
· Server Configurable Parameters (Windows and Unix)
· Access Control (Windows and Unix)
· Building a driver for use with the OOB Server (Unix)
ODBC Driver Managers
In Windows, a configured ODBC-ODBC Bridge DSN appears to the client program as an ordinary DSN, connected through the driver manager like any other ODBC driver. On non-Windows systems, which do not necessarily have a driver manager installed, it can be configured with or without one.
At the server end, the OOB acts like an ordinary ODBC-compliant application. In Windows NT, it takes the form of a network service, configured to start automatically. In Windows 95 it must be started manually or by placing it in the
Startup
menu directory. Under Unix, it is called byinetd
and is linked to the driver or driver manager of your choice.In general, ODBC applications must be linked with either an ODBC Driver or a Driver Manager. The Windows platform provides a single driver manager, to which programmers link their code (
odbc32.dll
). When a program callsSQLDriverConnect
(orSQLConnect
), it passes in a connection string, which normally contains at least a Data Source Name, or DSN. The driver manager examines the connection attributes and looks up the required driver from the DSN or the Driver name, and then dynamically loads the driver DLL. From then on the driver manager relays ODBC calls to the driver, and passes the result back to its caller.There is no standard driver manager for Unix at present, although at the time of writing Easysoft know of two driver managers distributed as free software under the GNU Lesser General Public License.
The GNU Lesser General Public License (LGPL) basically protects your right to give and receive free copies of the library, whilst allowing you to ship commercial products that rely on it. For full details see http://www.gnu.org/copyleft/lesser.html
If your client application runs on Unix and the OOB Client driver will be the only ODBC driver used on that machine, then you do not need a driver manager and you can link your application directly with the OOB. An example of this is using Perl in Linux to access a Windows ODBC driver across the OOB.
If you already have a driver manager or would like to use more than one ODBC driver on a Unix system then you should set up a DSN for the OOB in the driver manager. How you do this depends on the driver manager.
Easysoft recommend that you use the unixODBC driver manager for Unix platforms in the first instance. Other driver managers are available, but at the time of writing, unixODBC is demonstrably the most flexible and reliable open source solution we have seen. unixODBC is not an Easysoft product, but is available free from the Easysoft web site and is distributed with the OOB.
The unixODBC driver manager is included with the OOB distribution (see http://www.easysoft.com/)
http://www.unixodbc.org/ is the home page for unixODBC. The distribution found here contains the unixODBC driver manager plus a set of ODBC drivers.
The OOB Server under Windows 95/98
For Microsoft Windows, the OOB Server is distributed as an executable linked with Microsoft's driver manager. In Windows 2000 or Windows NT it is a network service configured to be started automatically by the Service Manager when Windows starts. In other Win32 environments it must be started manually or by placing it in the Startup menu directory:
1. Choose Start > Settings > Taskbar & Start Menu.
2. Select the Start Menu Programs tab.
3. Click Add...
4. Type\
InstallPath\esoobserver standalone
in the Command Line box, substituting the full path to theesoobserver.exe
program for InstallPath.
5. Click Next.
6. Navigate toPrograms/Startup
.
7. Click Next.
8. Change the name if you wish, and click Finish.
9. click Ok in the Taskbar Properties dialog to close it.
You have now added the OOB server to your startup menu. It will be run automatically when you start Windows.
In Windows 95 and 98, the Easysoft OOB Server runs in a DOS box which starts minimised by default. It will not normally be necessary to display the window, but any error messages will be written in that window.
The server runs with privileges of the user who started it. This means that connecting clients will only be able to see data sources to which you have access.
To run the Easysoft OOB Server under Windows 95 you will require Winsock 2. This can be obtained from http://www.microsoft.com/
The OOB Server under Windows 2000/NT
The OOB Server under Windows NT is installed as a network service, configured to be started automatically by the NT Service Manager when the machine boots.
The server runs with administrative privileges, allowing it access to any data source on the system. When a user connects, the OOB Sever effectively becomes the user specified in the
LogonUser
attribute so that it acts with the specified user's permissions.For Windows NT and Windows 2000 we can configure the service like any other network service -- for instance, we can set it up not to start automatically on system boot. We can also explicitly stop the service.
1. Choose Start > Programs > Control Panel.
2. Open theServices
icon.
The service manager presents a list of services, along with their status (i.e. whether "Started" or not), and their startup configuration (Manual, Automatic, or Disabled). Refer to the relevant subsection below.
To Start or Stop the Service in Windows NT
3. In the Services dialog, ensure that theEasysoft ODBC-ODBC Bridge Server
entry is selected.
4. If the service does not haveStarted
recorded in the Status column, you can click Start to run the service.
OR
If the service has
Started
recorded in the Status column, you can click Stop to stop the service. Click Yes when the service manager asks you to confirm your action.To Configure Automatic/Manual/Disabled status
When installed, the Easysoft ODBC-ODBC Bridge server is configured to run automatically when windows starts. Windows offers two other options for services.
The Manual startup mode dictates that the service should start only when requests come in or the user explicitly runs the service. This can save resources if the service in question is rarely used.
It may be that you need to make the service unavailable from time to time, for example if you need to restructure your data source or you have a security policy stating that services should not be available outside allotted times. Windows provides the Disabled state for this.
5. In the Services dialog, ensure that theEasysoft ODBC-ODBC Bridge Server
entry is selected.
6. Click Startup... (not Start!) to open the Service configuration dialog.
7. In the Startup Type section, choose the relevant radiobutton:
OR
- If you want the service to be started automatically by windows, select Automatic. This is the recommended option.
OR
- If you want the service to be available only when started by a user on the local machine, select Manual,
User Accounts and the Server
Windows NT's server model distinguishes two types of service with respect to their privileges. Services running in the System Account have access to all resources on the local machine whilst idle. When the OOB Server runs in this mode, an authentication process takes place when a user connects using the
LogonUser
andLogonAuth
attributes. The server then performs all actions with the privileges of the user specified inLogonUser
.This is the default configuration of the OOB Server, and it provides the greatest general flexibility.
The other option is to set up the server to always run with a specific user's permissions. This has two key effects, each of which may be useful on its own:
1. Be sure that the user account you wish to use is set up and that you know its password. It may be an existing end-user's account, or one created specifically for the OOB server.
- When the server is running with administrative privileges, and switches to another user on connect, some database systems (notably SQL Server) grant greater permissions than they would if the server had been running as that user throughout. Setting the server to run as a normal user tightens up access control in DBMSes that behave this way.
- Windows NT's authentication procedure, while quite satisfactory for a human user, is often too slow for an intensive application like recording website hits in real time. By setting up the server to run with a specific user's attributes, the authentication process is only executed at server start time.
If the server is set up to always run as a particular user, then you should not use LogonUser
orLogonAuth
when establishing a connection.
2. In the Services dialog, ensure that theEasysoft ODBC-ODBC Bridge Server
entry is selected.
3. Click Startup... (not Start!) to open the Service configuration dialog.
4. In the Log On As section, select the This Account radiobutton.
5. In the This Account text box, type the user name or click ... to browse for a user name.
6. In the Password box, type the user account password.
7. Type the same password in the Confirm Password box.
8. Click Ok to close the dialog and commit your changes.
You may have noticed the Allow Service to Interact With Desktop checkbox. This has no effect on the OOB Server.
The HTTP Configuration Interface
There are a number of configurable parameters in the server, irrespective of platform. In Windows, the parameters are stored in the registry but the server provides an HTTP interface to avoid editing the registry by hand.
This subsection provides a tour of the HTTP interface and shows you where to go to change the server-configurable parameters. The parameters themselves are documented in Server Configurable Parameters (Windows and Unix).
In order to follow this subsection you will need a Windows machine running the OOB server, and you will need to know the OOB Administrator's NT user name (HTTPAdmin) and password.
1. Start a web browser and open the URL http://localhost:8890/. Depending on your individual machine settings, you may need to specify your machine's actual name in place of localhost, or you may need to use a different port address than the Easysoft default of 8890.
If you forget the HTTP port setting for your OOB server you must look in the registry. Run regedit.exe
and look in:
HKEY_LOCAL_MACHINE\SOFTWARE\Easysoft ODBC-ODBC Bridge\Configuration\System\settings
for theHTTPPort
setting. This is the port you should connect to.
The web page returned is generated by the server process, and displays runtime statistics for the latest run of the server. There are four large icons at the top of the screen, marked Configuration, Statistics, Datasources and Access Control.
![]()
Figure 4.1: The Statistics Screen of the HTTP Interface Statistics (the current screen) and Datasources are only used to display information, but the Configuration and Access Control screens allow you to modify the server's runtime parameters.
2. Click on the Datasources icon in the browser window.
3. Click Configuration.
Figure 4.2: The Datasources Screen of the HTTP Interface
The HTTP Interface shows the configurable parameters of the server process.
![]()
Figure 4.3: The Configuration Screen of the HTTP Interface This screen shows the configuration state of the server process. There is also a link, Change Configuration, to a form that allows you to modify the server settings.
4. Click Change Configuration. The browser prompts you for a user name and password.
5. Type the HTTPAdmin user name and password. This is the NT user name that you typed during the install process in the Figure 2.9. The Change Configuration screen is displayed.![]()
6. If you want to make any changes to these parameters then you should refer to Server Configurable Parameters (Windows and Unix). Make your changes and click Submit.
Figure 4.4: The Change Configuration Screen of the HTTP Interface
7. Click Access Control to view or change the set of hosts that are allowed to connect to the ODBC-ODBC Bridge. If you haven't already entered them, then you are prompted for the HTTPAdmin user name and password. The following screen appears: ![]()
Figure 4.5: The Access Control Screen of the HTTP Interface If you want to change the contents of the access control lists, you should first refer to Access Control (Windows and Unix).
To add an IP address or set of IP addresses to the lists, type the address or address-stem into the Add New Allowed Access Entry or Add New Denied Access Entry box and click Add. At the confirmation screen, click the back button to return to this screen and see the changes you have made.
Configuration using the Registry
For experienced users, the server can be configured by editing the registry directly. No users, and so no step-by-step guide will be provided here. Server Configurable Parameters (Windows and Unix) gives the names and values to use to achieve various results. These can be found in the registry under the key:
\HKEY_LOCAL_MACHINE\SOFTWARE\Easysoft ODBC-ODBC Bridge\Configuration\System\Settings
.Note that the bitmask values are stored as a string representing the decimal value of the bitmask.
![]()
The access lists are stored as strings. IP addresses and address stems are separated by spaces.
Configuring the OOB Server under Unix
On Unix systems, the OOB Server is by default installed as a network service under
inetd
, but it may be configured in its stand-alone mode. This simply means that inetd does not know about the service and it must be run manually or by some other process.When the OOB Server is installed as an
inetd
-controlled service, entries are added to theinetd.conf
andservices
files. Examples of these entries might be
esoobserver stream tcp nowait root /bin/sh /bin/sh /usr/local/easysoft/oob/server/s
erver
esoobserver 8888/tcp # Easysoft OOB Server
These are just examples and may differ if you picked a different port, service name or installation path.
The mechanics of inetd and the server
The
inetd.conf
file entry instructsinetd
to listen on behalf of the OOB Server. The services file specifies that it should listen on port 8888. When a client attaches to port 8888,inetd
consults the fileinetd.conf
and finds the line relating to the service name (esoobserver
). It runs the shell/bin/sh
-- given ininetd.conf
-- passing it the arguments on the rest of the line.
Note that /bin/sh
is repeated. The first time it appears (in the sixth position on the line) it is the name of the executable to run. The second time it appears it is the value to be passed as the zeroeth argument to/bin/sh
. Normally this is the same as the name of the executable.
In this example, the arguments on the line cause
sh
to run the Easysoft ODBC-ODBC Bridge server startup script/usr/local/easysoft/oob/server/server
. The script then sets any necessary environment variables required by the dynamic linker and runsesoobserver
.The script passes an argument of
inetd
in to the server executable. This notifies the server that it is running underinetd
rather than at the command line. The server inherits the sockets frominetd
and begins communicating with the OOB client.inetd
returns to listening on port 8888 for any new connections.Choosing another Port or Service Name
If you have a port conflict or a service name conflict, or for some other reason you want to change the port or service name of the Bridge server, then you will need to edit the configuration files by hand.
The port number appears only in
/etc/services
, but the service name appears in both/etc/inetd.conf
and/etc/services
. After making the necessary amendments you will have to send SIGHUP to theinetd
process to make it re-read the files. This can be achieved with the command:where pid is the process ID of
inetd
. You need to be root to edit the configuration files and sendinetd
a SIGHUP.Configuring the Server as Standalone
When the OOB Server is installed standalone, it has some subtly different properties. First, it no longer uses
inetd
to listen on the socket, so this approach might bypass any firewall implemented as a tcp wrapper. Secondly, you should note that if the server is started by a non-root user then it will always operate as that user. If a client specifies the LogonUser and LogonAuth attributes then they will be disregarded.When the server is not present in the
/etc/inetd.conf
and/etc/services
files (or is commented out), it needs to be started manually -- or the root user can add the startup script to the kernel boot sequence.To start the OOB Server in this mode you need to run it with the argument "standalone":
This notifies the server that it must listen on the socket itself, and fork its own child process when a connection is made.
Changing Server Configurable Parameters under Unix
There are a few DSN-independant configurable parameters in the server (See Server Configurable Parameters (Windows and Unix)). In Unix, the settings are held in the file named InstallPath
/easysoft/oob/server/esoobserver.ini
. If InstallPath is anything other than/usr/local/
then there will be a symbolic link/usr/local/easysoft
to the realeasysoft
directory.
Server Configurable Parameters (Windows and Unix)
This section explains the configurable parameters in the server, which apply either to the server itself, or as a default value for all DSNs accessed via the server. In Windows these settings are held in the registry, and may be accessed through the HTTP interface (see The HTTP Configuration Interface). In Unix, they are held in a file -- see Changing Server Configurable Parameters under Unix above.
The configurable parameters are shown below, each as a key=value pair, with its default value and an explanation. Keys are not case-dependent, so for example
Port
,PORT
andpOrT
are treated as the same key. Values are case-dependant if the operating-system is, so it is best to match case where possible.
- This is the port at which the server will listen for connections, if run in standalone mode. This may need to be changed if you already have a service on port 8888.
- This is the port at which the HTTP Configuration interface will listen, if enabled. See This Link for details on the interface.
- This is a bitmask controlling the degree of logging to perform. It should only be used as directed by Easysoft support as it will slow the OOB down considerably if set. See Tracing And Logging.
- This setting determines the location in which to write the log files when logging is enabled.
Logging
andLogDir
can also appear in the DSN attributes when a client connects, overriding the settings in the server attributes.
- This is a time period, measured in seconds, after which the OOB server will close a connection if it has not received any requests. The value should be high enough to allow users some idle time without losing their connections. The timeout is present because of an occasional problem with abnormal session terminations leaving orphaned server processes or threads on the host, using up resources indefinitely.
- ODBC 2.0 only supported fixed, 4-byte bookmarks. ODBC 3.0 deprecates fixed-width bookmarks and supports variable-width bookmarks. No upper limit is defined on the size of variable-length bookmarks but Easysoft have not yet encountered an application requiring more than 32 bytes. If you have such an application you should increase this setting. We would be grateful if you would also report the application to Easysoft so that we may update the default value.
- To set the flags, choose your required options and place the sum of their corresponding values in the configuration dialog or file. For help choosing whether to set each flag, see the subsection Server Flags.
- The number of times the OOB server should try to get a license slot for a new connection. If set to
0
or1
then one attempt is made. If your system is having trouble creating threads then this is how many times the server will try to create a new thread before returning a failure.
- The number of seconds between retries when trying to create a new thread to handle an incoming connection or create a new thread. Note that if your connection cannot be made, the OOB will take
RetryCount
xRetryPause
seconds before giving up and reporting the failure.Tracing And Logging
The OOB contains comprehensive tracing and logging support -- which should be turned off as it seriously impairs bridge performance. The client and server sides each carry a bitmask dictating what items should be logged. The actual logging mechanisms vary but the bitmask has the same meaning on any platform. The required value for this bitmask is derived by adding the values in the following table:
These flags, while correct at the time of writing, are subject to change without notice. It is unlikely that any of these flags will be removed, but it is likely that more will be added, and the precise functionality may change.
ESOOB_LOGENTRY Log all ODBC API function entry points ESOOB_LOGEXIT Log all ODBC API function exit points ESOOB_LOGRPC Log EasyRPC operations ESOOB_LOGSPECIAL Special notifications (see below) ESOOB_LOGVALUES Log all bound parameter and column values possible ESOOB_LOGINTERNAL Internal functions (see below) ESOOB_LOGDRIVER Log calls to the ODBC driver ESOOB_LOGDIAGS Log ODBC diagnostic routines and results ESOOB_LOGQUERIES Log queries - text posted to SQLPrepare/SQLExecDirect ESOOB_LOGCONNECT Log Connection stage ESOOB_LOGATTRIBUTES Log connection attribute setting and retrievals ESOOB_LOGSPECIAL enables some miscellaneous diagnostic messages in the bridge. For example, the server will record a message when it detects it is connected to SQLServer or Access drivers.
Note also that ESOOB_LOGINTERNAL does nothing unless ESOOB_LOGENTRY or ESOOB_LOGEXIT is set.
The Logging option may be set in the file /usr/local/easysoft/oob/server/esoobserver.ini
, e.g:
This affects both the client and the server. The log files are calledesoobclient.log_
PID andesoobserver.log_
PID and will appear in/tmp
. Alternatively, if the log files cannot be created there, an attempt will be made to create the log files in the current working directory in which case they have "fallback
" appended to the filenames. As with Windows, you can redirect the log file to a different directory by specifying the LogDir attribute (see the example above).
Tracing may also be turned on for EasyRPC by setting the environment variable ESRPC
toc
,a
orca
, wherec
stands for calls, anda
stands for arguments. EasyRPC logging is sent to the fileeasyrpc.log
.
Server Flags
This subsection documents the server flags in terms of their effect on the service. This is a bitmask with the following effects:
(no name) (Reserved, do not use) ESOOB_HTTP HTTP Enabled (Windows),
No effect (Unix).ESOOB_MULTI_PROCESS Create a new process on connection rather than a thread (Windows NT Only). ESOOB_NO_METADATA_BF Disable automatic block-fetch mode for metadata calls. ESOOB_HIDE_HTTPADMIN Disable automatic block-fetch mode for metadata calls. ESOOB_HTTP (0x02) should be set if you wish to use the HTTP-based server reporting and configuration utility. This is set by default, as the HTTP interface is more flexible than editing the registry manually. The server configuration screen is protected by a user id and password screen, but you may wish to prevent users from accessing the utility altogether. If this is the case, reset this flag.
Once the HTTP Configuration interface is disabled, you will need to edit the registry by hand to bring it back online.
ESOOB_MULTI_PROCESS (0x04) is only effective in Windows NT. It causes the service to create new processes rather than threads on receipt of new connections. The default is to use threads, as they are lighter on system resources. Though the ODBC standard states that drivers must be thread-safe, in practice some are not. If you have an ODBC driver that is not thread-safe then it is able to harm other connections unless you set this flag.
ESOOB_NO_METADATA_BF (0x08): As an efficiency-boosting measure, the OOB will normally use a block cursor to get read-only metadata. If you do not want this, for instance if your driver behaves badly with block fetches, then you can set this flag to inhibit the feature. Meta data is then retrieved one column at a time.
ESOOB_HIDE_HHTPADMIN (0x10): If set, then the name of the HHTP Admin user (see The HTTP Configuration Interface) will be hidden on the HTTP Admin interface. This is a security feature to prevent anyone using the HTTP Admin interface to learn an NT user name. By default this is off.
Access Control (Windows and Unix)
In addition to you system's usernames and passwords, and those of the database management system, the OOB provides another layer of security with access control lists. Unix administrators will recognise this mechanism from the
hosts.allow
andhosts.deny
files.
Though the approach is similar, the rules for determining whether or not a host should be allowed to connect are different than those for hosts.allow
andhosts.deny
. Please read the rest of this section to avoid misunderstanding.
OOB Access Control takes the form of two lists of IP addresses. When a host attempts to connect to the ODBC-ODBC Bridge Server, access is only granted if:
- The
allowed
list is empty and the IP address does not appear in thedenied
list, or- The IP address is in the
allowed
list and does not appear in thedenied
list.The lists can be edited either in their relevant flat files (Unix servers) or via the HTTP Interface (Windows servers). Entries must be in the IP `dot' notation, and those having fewer than four fields stand for all addresses matching those fields that do appear, e.g.
163.141.23.
(note the trailing dot) matches all IP addresses from163.141.23.0
to163.141.23.255
.
Building a driver for use with the OOB Server (Unix)
The server is distributed as an executable linked with the shared object
libodbc.
suffix; where suffix is the file suffix your system uses for shared objects. Assuming unixODBC is installed -- it is included in the OOB distribution -- thenlibodbc
is the unixODBC Driver Manager and the OOB will function `out of the box'. This section shows how to prepare a driver for use without a driver manager.If you have no driver manager then you can create a
1. Back up the server executable:libodbc.
suffix object by linking the driver with the server object code to create a new server executable.
Caution!
This is an extremely complex area and it is impossible to document it exhaustively. You should not attempt this unless you are comfortable with compiling and linking code, and you know what driver you have and that it is built properly.
In any event, we strongly recommend making a backup of the executableesoobserver
.
$ cd install-path/easysoft/oob/server
$ cp esoobserver esoobserver.bak
In order to support a wide range of clients, the OOB contains all the ODBC 3.0 API and some of the ODBC 2.0 API too. For this reason you will probably need to create a few stubs. First, we must find out what symbols are missing.
You should verify the name and location of your desired driver. Refer to the documentation for the driver and satisfy yourself that it is correctly built and that all the necessary components are on your system.
The library
2. Enter the following, from thelibserver.a
contains the actual server. The command line for building the driver is given below. Note the italics, which denote that you should substitute some specific text that depends on your installation.easysoft/oob/server
directory:
$ cc -o esoobserver -L../../lib -L. -Ldriver-path
libserver.a -ldate -lsupport -lesrpc -lextra -ldriver
In a typical installation, install-path will be
/usr/local
, but it is in no way unusual for theeasysoft
directory to be located elsewhere. driver-path should be a path to the driver library, and the driver library is namedlib
driver.a
, (the suffix varies depending on the platform and how the driver was built).
The driver name given to the linker excludes the filename suffix and the preceding lib
. For example, if your driver is in the filelibodbc.so
then the corresponding command-line option is-lodbc
.
This command is likely to fail. If it completes successfully, then your chosen driver is very comprehensive and is probably a driver manager. In this case you have created the executable
esoobserver
in the directory install-path/oob/server
, and the bridge is ready for configuring. Refer to Chapter 3 for information on how to do this.In most cases, however, this first attempt will fail with a number of "undefined reference" errors, as the OOB server library depends on a lot of symbols being present in the driver library. Functions such as
SQLAllocEnv
,SQLAllocConnect
andSQLAllocStmt
are in ODBC 2.0 but not ODBC 3.0. They are likely to be absent from an ODBC 3.0 driver, but the bridge server must contain calls to them in order to support clients using the ODBC 2.0 API.You can create stubs (functions that do nothing except return) for the missing names, and link them in with the driver and bridge server. As long as your client and driver support the same version of ODBC then the stubs should never be called and everything should function correctly.
3. Create a file in theserver
directory called, for examplestubs.c
, and open it in your editor.
4. Insert#include "odbc.h"
at the top of the file.
5. The remainder of the file should consist of definitions for the missing functions. You will have to look in the header files to locate their prototypes, and you must give each a body:
So for illustration, you can expect to have a file looking something like this:
SQLRETURN SQL_API SQLAllocConnect(
6. Compile this into object code (substituting for
SQLRETURN SQL_API SQLAllocStmt(
.o
according to platform):
7. Then build the server again, this time including the
$ cc -L../client/include -o stubs.o -c stubs.c
stubs
object code.
$ cc -o esoobserver -L../../lib -L. -Ldriver-path libserver.a stubs.o -ldate -lsupport -lesrpc -lextra -ldriver
If your
stubs.c
file is still not correct, you will still get messages about functions beginningSQL...()
. If you get other messages then it is possible that your driver is not correctly compiled or linked.
Easysoft - The Home of Data Access |
||||