Using an InterSystems Database as an ODBC Data Source on UNIX®
An external application can use InterSystems databases as ODBC data sources. This chapter describes how to create a DSN for an InterSystems database on UNIX®, which you do by editing the ODBC initialization file.
Configuring the ODBC Initialization File
The ODBC initialization file is used as follows:
It provides information so that the driver manager can locate and connect to an available DSN, including the path of the ODBC client driver required for that particular connection.
It defines the DSNs (and optionally includes login credentials for them). The ODBC client drivers use this information.
The following is a sample initialization file for the InterSystems ODBC driver:
[ODBC Data Sources] sampleodbc=sampleodbc [sampleodbc] Driver = /usr/cachesys/bin/libcacheodbc.so Description = Cache ODBC driver Host = localhost Namespace = SAMPLES UID = _SYSTEM Password = SYS Port = 1972 Protocol = TCP Query Timeout = 1 Static Cursors = 0 Trace = off TraceFile = iodbctrace.log Authentication Method = 0 Security Level = 2 Service Principal Name = localhost.domain.com [Default] Driver = /usr/cachesys/bin/libcacheodbc.so
This file includes the following variables:
ODBC Data Sources — Lists all DSNs for the file. Each entry is of the form “DSNName=SectionHeading”, where DSNName is the name specified by the client application and the SectionHeading specifies the heading under which DSN information appears in this file.
Driver — Specifies the location of the client driver file to use for this DSN. In this case this is the file libcacheodbc.so.
Description — Contains an optional description of the DSN.
Host — Specifies the IP address of the DSN in dotted decimal or dotted quad form, such as “127.0.0.1”.
Namespace — Specifies the namespace for the DSN.
UID — Specifies the username for logging into the DSN. By default, this is “_SYSTEM” and is not case-sensitive.
Password — Specifies the password for the account specified by the UID entry. For the SYSTEM username, the password is “SYS” and is case-sensitive.Note:
Because it is an ODBC standard to allow the storing of usernames and passwords in clear text, the sample initialization file includes the username and password required to access the sample DSN. This is meant merely as an example. A secure ODBC program prompts the user for this information and does not store it, in which case it does not appear in the initialization file at all.
Port — Specifies the port for connecting to the DSN. The default for InterSystems is 1972.
Protocol — Specifies the protocol for connecting to the DSN. For InterSystems, this is always TCP.
Query Timeout — If 1, causes the ODBC client driver to ignore the value of the ODBC query timeout setting.
The ODBC query timeout setting specifies how long a client should wait for a specific operation to finish. If an operation does not finish within the specified time, it is automatically cancelled. The ODBC API provides functions to set this timeout value programmatically. Some ODBC applications, however, hard-code this value. If you are using an ODBC application that does not allow you to set the timeout value and the timeout value is too small, you can use the Disable Query Timeout option to disable timeouts.
Static Cursors — If 1, enables the InterSystems ODBC client driver’s static cursor support. If 0, then the cursor support provided by the ODBC Cursor Library will be used. In general, this flag should be off (that is, set to 0) unless you have a specific reason for not using the ODBC Cursor Library.
Trace — Specifies whether the driver manager performs logging (“on”) or not (“off”); by default, logging is off (see “Enabling logging for ODBC on UNIX®” for more information).
TraceFile — If logging is enabled by the Trace entry, specifies the location of the driver manager log file.
Authentication Method — Specify 0 for password authentication or 1 for Kerberos.
Security Level — Specify this if you use Kerberos for authentication. The allowed values are as follows:
1 = Kerberos
2 = Kerberos with packet integrity
3 = Kerberos with encryption
Service Principal Name — Specify this if you use Kerberos for authentication. This should be the name of the service principal that represents InterSystems.
For more information on Kerberos, see the Security Administration Guide.
Setting up a DSN with odbcinst
A UNIX® ODBC installation installs the program odbcinst. The location is dependent on the install but may be located under /usr/local/bin for example.
There are two template files included with a UNIX® installation located in \dev\odbc\redist\unixodbc. These are:
odbc.ini_unixODBCtemplate — A sample DSN entry template
odbcinst.ini_unixODBCtemplate — Intersystems driver template
Edit the template files to suit your configuration. To use them, you can call odbcinst in the following ways:
To register the driver, specify flags -i -d -f and your odbcinst.ini file. For example:
odbcinst -i -d -f odbcinst.ini_unixODBCtemplateCopy code to clipboard
To add a local DSN, specify flags -i -s -h -f and your odbc.ini file. For example:
odbcinst -i -s -h -f odbc.ini_unixODBCtemplateCopy code to clipboard
To add a System DSN, specify flags -i -s -l -f and your odbc.ini file. For example:
odbcinst -i -s -l -f odbc.ini_unixODBCtemplateCopy code to clipboard
[InterSystems ODBC] UsageCount=1 Driver=/home/cache/bin/libcacheodbc.so Setup=/home/cache/bin/libcacheodbc.so SQLLevel=1 FileUsage=0 DriverODBCVer=02.10 ConnectFunctions=YYN APILevel=1 DEBUG=1 CPTimeout=<not pooled>
Setting up SSL Configuration Files
InterSystems provides two template files for SSL configuration. The files are located in <Dir>\dev\odbc\redist\ssl. The directory also contains a readme.txt file with further information.
cacheodbc.ini.template — demonstrates how an odbc.ini file entry would be configured for use with an SSL connection.
odbcssl.ini.template — is an example of an SSL configuration file.
This is a sample odbc.ini file with an entry named [SampleSSL] that defines an SSL connection. A working file would be named <installdir>/mgr/cacheodbc.ini.
[ODBC Data Sources] SamplesSSL = SampleSSL [SampleSSL] Driver = /home/guest/cache/bin/libcacheodbc35.so Description = Cache ODBC driver Host = localhost Namespace = SAMPLES UID = _SYSTEM Password = SYS Port = 1972 Protocol = TCP Query Timeout = 1 Static Cursors = 0 Trace = off TraceFile = iodbctrace.log Service Principal Name = localhost.domain.com Authentication Method = 2 Security Level = 10 SSL Server Name = SampleSSLConfig
In the example above, the last three lines specify the SSL connection. The values must be defined as follows:
Authentication Method must be set to 2.
Security Level must be set to 10.
SSL Server Name must be set to the appropriate named configuration.
In this example, SampleSSLConfig is the SSL Server Name defined in the following sample file, odbcssl.ini.
This is a sample SSL configuration file. In order for a process to initiate an SSL connection with these values:
The name of this file (<path>/odbcssl.ini) must be specified in environment variable ISC_SSLconfigurations.
The process must be using a DSN that specifies [SampleSSLConfig] as the SSL Server Name (as shown in the previous example).
[SampleSSLConfig] CAFile=./CA.cer CertFile=./Client.cer KeyFile=./Client.key Password=MixOfAlphaNumericAndPuncChars! KeyType=2 Protocols=28 CipherList=ALL:!aNULL:!eNULL:!EXP:!SSLv2 VerifyPeer=1 VerifyDepth=9
This example defines the following values:
CAFile — specifies the file containing one or more certificates used to verify the server's certificate.
CertFile — specifies the file containing the client's certificate.
KeyFile — specifies the file containing the client's private key file.
Password — is the client's private key file password, if applicable.
KeyType — specifies the type of private key used by the client.
1 — DSA
2 — RSA (default)
Protocols — specifies which versions of SSL/TLS the client can perform.
1 — SSLv2
2 — SSLv3
4 — TLSv1.0
8 — TLSv1.1
16 — TLSv1.2
Protocol combinations are specified by adding individual numbers. For example, the default setting is 28 (TLSv1 + TLSv1.1 + TLSv1.2).
CipherList — specifies the list of enabled ciphersuites.
VerifyPeer — specifies the peer certificate verification level.
0 — None (Continue even if certificate verification fails)
1 — Require (Continue only if certificate verification succeeds; default)
VerifyDepth — specifies the maximum number of CA certificates allowed in peer certificate chain.
See “Using SSL/TLS” for detailed information on these values.
Name and Location of the Initialization File
The initialization file can have any name, but, typically, it is called .odbc.ini when it is located in a user’s personal directory, odbc.ini when located in an ODBC-specific directory. The InterSystems sample is called cacheodbc.ini and is located in the install-dir/mgr directory.
To locate this file, the InterSystems ODBC client driver uses the same search order as iODBC. It looks for the file in the following places, in this order:
The file specified by the ODBCINI environment variable, if this is defined. When defined, this variable specifies a path and file, such as:
ODBCINI=/usr/cachesys/cacheodbc.ini export ODBCINICopy code to clipboard
The .odbc.ini file in the directory specified by the user’s $HOME variable, if $HOME is defined and if .odbc.ini exists.
If $HOME is not defined, the .odbc.ini file in the “home” directory specified in the passwd file.
The file specified by the system-wide SYSODBCINI environment variable, if this is defined. When defined, this variable specifies a path and file, such as:
SYSODBCINI=/usr/cachesys/cacheodbc.ini export SYSODBCINICopy code to clipboard
The file odbc.ini file located in the default directory for building the iODBC driver manager (/etc/), so that the full path and file name are /etc/odbc.ini.
To use a different odbc.ini file, delete or rename the InterSystems sample initialization file to allow the driver manager to search the $HOME or /etc/odbc.ini paths. For example, go to <cachesys>/bin and execute the following command:
mv libodbc.so libodbc.so.old
and then move your user-defined odbc.ini to etc/odbc, where the driver manager can find it.