Installation Guide: OpenLink Single-Tier SQL Server ODBC Connector (Driver) for Linux (Lite Edition)

Data Access Drivers & Connectors Open Database Connectivity (ODBC)
, , , ,
1

This installation guide walks you through the installation of the Lite Edition ODBC Driver for Microsoft SQL Server to a Personal or Application Server host functioning as a client to a SQL Server Database.

SQL Server Lite Architecture

Step 1: Download the Installer Archive

  1. Visit the OpenLink ODBC Lite Edition Driver Download Page to download the appropriate ODBC driver for SQL Server.
  2. Alternatively, curl can be used to download the installer archives and install.sh script directly:
curl -O https://download3.openlinksw.com/uda/components/misc/install.sh
curl -O https://download3.openlinksw.com/uda/components/10.0/x86_64-generic-linux-glibc212-64/sql_lt.taz 
curl -O https://download3.openlinksw.com/uda/components/10.0/x86_64-generic-linux-glibc212-64/odbc_admin.taz

Step 2: Installation

  1. Log onto the target machine for the installation and create a suitable OpenLink installation directory {OPENLINK_INSTALL}.
  2. Copy all the downloaded components to the {OPENLINK_INSTALL} directory, which are the install.sh, odbc_admin.taz, sql_lt.tazfiles.
  3. Install OpenLink components using the install.sh shell script with the command –
 install.sh
  1. Take care to enter correct information when prompted pertaining to ports, passwords, etc., and ensure that you note these down for future use.
  2. The installation should finish without error.
Step 3: Configuration
  1. To set up the OpenLink environment, from the {OPENLINK_INSTALL} run the command –
. ./openlink.sh
  1. Place the License file (sql_lt.lic) either purchased or obtained during the evaluation download process in the {OPENLINK_INSTALL}/bindirectory.
  2. Start the OpenLink License Manager (oplmgr) by going to the {OPENLINK_INSTALL}/bindirectory and running the command –
./oplmgr +start

Step 4: Create Data Source Name (DSN)

  1. Open the file {OPENLINK_INSTALL}/bin/odbc.ini with a suitable text editor such as vi.
  2. Locate and edit the sample OpenLink Single-Tier(Lite) ODBC data source (DSN) called [sql_lite] created during the installation process and edit as follows –
[sql_lite]
Driver          = {OPENLINK_INSTALL}/lib/sql_mt_lt.so
ServerType      = SQL Server
Database        = my_database
Options         = -H {hostname} -P {port number} -S {servername}    ;;; connection attributes for the target SQL Server instance
Username        = sa
Password        = **********
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
JetFix          = no
WideAsUTF16 = Y
Description     = Sample SQL Server Single-Tier Connection
Driver ODBC DSN parameters

The following is a list of ODBC DSN parameters for the SQL Server ODBC Driver and can be added for an ODBC DSN as required:

  • Driver - Full path and filename of the ODBC driver (/{OPENLINK_INSTALL}/lib/sql_mt_lt.so or /{OPENLINK_INSTALL}/lib/sql_mt_lu.so)
  • ServerType - An option OpenLink proprietary parameter that associates the connection with a particular TDS version, can be left as SQLServer 2000
  • Username - User name for connecting to the database with
  • Password - Password for connecting to the database with
  • Database - The Microsoft SQL Server database name
  • Options - SQL Server specific connection options for the database
    • -H - The hostname or IP address on which Microsoft SQL Server listens
    • -P - The TCP port on which Microsoft SQL Server lists
    • -V - The TDS version to use for connecting to the SQL Server database
    • -S - The Server name of the Microsoft SQL Server instance if required.
    • -Y - Set ApplicationIntent to Read-Only on connection from the default of Read-Write. Applicable to SQL Server MSF cluster nodes.
    • –msf - This flag switches ON SQL Server Multi Subnet Failover mode.
    • –conn - Connection timeout in msec .
    • –auth - Authentication method to be used that can be any of:
      • unset - With SQL Server authentication using a LoginID and password entered by user (Default)
      • adDefault - With Integrated Windows authentication
      • adIntegrated - With Microsoft EntraID Integrated authentication (ActiveDirectoryIntegrated)
      • adPassword - With Microsoft EntraID Password authentication using a LoginID and password entered by user (ActiveDirectoryPassword)
      • adInteractive - With Microsoft EntraID Interactive authentication using a LoginID and password entered by user (ActiveDirectoryInteractive)
      • adMSI - With Azure Managed Service Identity authentication (ActiveDirectoryMsi)
      • adServicePrincipal - With Azure Service Principal authentication (ActiveDirectoryServicePrincipal)
      • adServicePrincipalAccessToken - With Azure Service Principal AccessToken authentication (ActiveDirectoryServicePrincipalAccessToken)
  • FetchBufferSize - This attribute specifies the number of records to be delivered from the driver to the client application in a single batch. Values can range from 1 to 999.
  • ReadOnly - Specifies whether the connection is Read-only, value is No by default ie allows write operations and must be set to Yes to enforce a Read-only connection.
  • JetFix - Facilitates translation of certain data types for the Microsoft Jet Engine. If you notice that money and other datatypes are mishandled with Microsoft or other applications, test with Jet fix enabled.
  • Description - Description for the ODBC DSN
  • WideAsUTF16 - When using the unixODBC Driver Manager, the WideAsUTF16 = Y parameter needs to be added to the target ODBC DSN, if not in place already, to enable successful connections with applications built using the unixODBC SDK. Default is N.
  • NoRowSetSizeLimit - Allows you to set a limit for the maximum number of rows to be returned from a query. The default value of 0 meaning no limit.
  • NoAutoCommit - Changes the commit behavior of the OpenLink driver. The default mode is AutoCommit and must be set to Yes for NoAutoCommit, then requiring manual commits to be performed.
  • NoLoginBox - No login dialog prompt on connect, default is Yes
  • DeferLongFetch - Defers fetching of LONG (BINARY, BLOB, etc.) fields in wildcard queries. This provides significant performance increases when fields in query do not include LONG data fields. Values Yes or No with No being the default.
  • MaxRows - Allows you to set a limit for the maximum number of rows to be returned from a query. The default value of 0 meaning no limit.
  • InitialSQL - Lets you specify a file containing SQL statements that will be run automatically against the database upon connection.
  • SqlDbmsName - Manually overrides the SQLGetInfo(SQL_DBMS_NAME) response returned by the driver. This is required for products like Microsoft InfoPath for which the value should be SQL Server.
  • NoQuotedIdentifiers - This option indicates that the underlying driver does not support quoted identifiers, which is required for Jet engine based products like MS Access. Set to Y to enable, default is N.
  • ShowSparseCols - Display SQL Server Sparse Columns in ODBC SQLColumns call. Set to Y to enable, default is N.

Step 5: Test Connection to DSN

Using the iODBC Runtime Environment
  1. Use the OpenLink iODBC iodbctest sample tool packaged with the HTTP-based OpenLink ODBC Administrator as follows to make a test connection –
$ cd {OPENLINK_INSTALL}
$. ./openLink.sh 
$ iodbctest "DSN=sql_lite;UID=sa;PWD=xxxxxxxx"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1723.0502
Driver: 10.00.251114 OpenLink SQL Server Lite Driver (sql_mt_lt.so) 

SQL>
  1. SQL statements can now be issued directly against the target database from the SQL> prompt –
SQL> select count(*) from orders

(count(*))       
-----------------
23               

result set 1 returned 1 rows.

SQL>
  1. The SQL Server ODBC Driver is now ready for use with target ODBC application(s).
Using the unixODBC Runtime Environment
  1. When using the unixODBC Driver Manager, the WideAsUTF16 = Y parameter needs to be added to the target ODBC DSN, if not in place already, to enable successful connections with applications built using the unixODBC SDK, e.g., its isql test application –
$ isql sql_lite sa xxxxxxxx
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from orders              
+------------------+
| (count(*))       |
+------------------+
| 23               |
+------------------+
SQLRowCount returns -1
1 rows fetched
SQL>

Additional Information