Defining an ODBC Data Source on UNIX® Contents Structure of the ODBC Initialization File Setting up a DSN with odbcinst Setting up TLS Configuration Files Name and Location of the Initialization File 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. To create a DSN for an InterSystems database, the InterSystems ODBC driver for UNIX must be installed. If InterSystems IRIS is installed on the host you are working on, the driver is already installed; if not, do the following: Download the InterSystems ODBC driver from the InterSystems IRIS Drivers pageOpens in a new tab Create the directory in which you want to install the driver, for example /usr/irisodbc, and unpack the downloaded .tar file in that directory. Execute the ODBCinstall script. Structure of 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/irissys/bin/libirisodbc.so Description = InterSystems IRIS ODBC driver Host = localhost Namespace = USER UID = _SYSTEM Password = SYS Port = 51774 Protocol = TCP Query Timeout = 1 Static Cursors = 0 Trace = off TraceFile = iodbctrace.log Authentication Method = 0 Security Level = 2 Service Principal Name = iris/localhost.domain.com [Default] Driver = /usr/irissys/bin/libirisodbc.so This file includes the following variables: ODBC Data Sources — This section lists all DSNs defined in the file 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 libirisodbc.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 in to the DSN. By default, this is _SYSTEM (not case-sensitive). Password — Specifies the password for the account specified by the UID entry. For default username _SYSTEM, the password is SYS. Unlike the UID, the password 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 (see “DefaultPort” in the Configuration Parameter File Reference). 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 “ODBC Logging 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 Kerberos Authentication. Setting up a DSN with odbcinst A UNIX® ODBC installation includes 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 install-dir\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_unixODBCtemplate 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_unixODBCtemplate 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_unixODBCtemplate From: install-dir\dev\odbc\redist\unixodbc\odbcinst.ini_unixODBCtemplate [InterSystems ODBC] UsageCount=1 Driver=/home/iris/bin/libirisodbc.so Setup=/home/iris/bin/libirisodbc.so SQLLevel=1 FileUsage=0 DriverODBCVer=02.10 ConnectFunctions=YYN APILevel=1 DEBUG=1 CPTimeout=<not pooled> Setting up TLS Configuration Files InterSystems IRIS provides two template files for TLS configuration. The files are located in install-dir\dev\odbc\redist\ssl. The directory also contains a readme.txt file with further information. irisodbc.ini.template — demonstrates how an odbc.ini file entry would be configured for use with a TLS connection. odbcssl.ini.template — is an example of a TLS configuration file. irisodbc.ini.template This is a sample odbc.ini file with an entry named [SampleSSL] that defines a TLS connection. A working file would typically be named install-dir/mgr/irisodbc.ini. [ODBC Data Sources] SamplesSSL = SampleTLS [SampleTLS] Driver = /home/guest/iris/bin/libirisodbc35.so Description = IRIS ODBC driver Host = localhost Namespace = SAMPLES UID = _SYSTEM Password = SYS Port = 51774 Protocol = TCP Query Timeout = 1 Static Cursors = 0 Trace = off TraceFile = iodbctrace.log Service Principal Name = iris/localhost.domain.com Authentication Method = 2 Security Level = 10 SSL Server Name = SampleSSLConfig In the example above, the last three lines specify the TLS 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. odbcssl.ini This is a sample TLS configuration file. In order for a process to initiate a TLS 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 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 InterSystems TLS Guide 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 irisodbc.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/irissys/irisodbc.ini export ODBCINI 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/irissys/irisodbc.ini export SYSODBCINI 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 install-dir/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.