MarkLogic Server 11.0 Product Documentation

SQL Data Modeling Guide — Chapter 5

Configuring the ODBC Driver on Windows

Tableau communicates with MarkLogic Server via a 32-bit or 64-bit ODBC driver. You must install and configure the ODBC driver that matches the word length (32 or 64-bit) of the connecting application, regardless of the MarkLogic server or operating system. For example, your computer may be running 64-bit windows with a 32-bit installation of Microsoft Excel. In this case, you will need the 32-bit ODBC driver to connect Excel to any MarkLogic instance. You can install the 32-bit and 64-bit ODBC drivers on the same machine.

This section describes how to configure your ODBC driver for use with MarkLogic.

1. Launch the correct version of the Windows ODBC Data Source Administrator:

   64-bit: %systemdrive%\Windows\System32\Odbcad32.exe
   32-bit: %systemdrive%\Windows\SysWoW64\Odbcad32.exe

2. Click the System DSN tab and click Add:

   

3. Select either the MarkLogic SQL (64-bit) or MarkLogic SQL (x86) (32-bit) driver and click Finish:

   

4. Set up an ODBC app server, as described in Create an ODBC App Server.
5. In the MarkLogic SQL ODBC Driver Setup dialog, enter a name for your data source, the database name (SQLdata), the name of the machine that hosts your MarkLogic Server, the port number of your MarkLogic ODBC App Server (5432), set SSL mode to 'allow', and your MarkLogic Server login credentials. Click Test to test the connection to MarkLogic Server.

   

6. If your connection test was successful, click Save. Otherwise, recheck your settings and retest.

   

Configuring the ODBC Driver on Linux

Dependencies:openssl and unixODBC.

The following procedure describes how to install the MarkLogic ODBC driver on Linux. The unixODBC tool is only needed for testing your connection to MarkLogic via the mlsqlodbc driver. If you don't think it is necessary to test your ODBC connection, then only do Step 3.

1. Obtain a copy of unixODBC (used version 2.3.4 in this example). You might be able to install it with yum, but if not, you can download the correct version from http://www.unixodbc.org/ to your /tmp directory and use the following procedure to install:

   cd /tmp
   tar -xvzf unixODBC-2.3.4.tar.gz
   cd /tmp/unixODBC-2.3.4
   ./configure
   make
   sudo make install

2. If you want to communicate with MarkLogic over SSL, you can install the openssl libraries as follows:

   yum install openssl-libs

   You can optionally install the GUI tools for unixODBC:

   yum install unixODBC-gui-qt 

3. Install the ODBC driver package (named mlsqlodbc-1.4-20170317.x86_64.rpm in this example):

   rpm -i mlsqlodbc-1.4-20170317.x86_64.rpm

4. Call odbcinst to write the DSN to the current user's .odbc.ini file:

   odbcinst -i -s -f /opt/MarkLogic/templates/mlsql.template

5. The name of the ODBC driver is MarkLogicSQL. Use isql to connect to MarkLogicSQL to confirm that the ODBC driver was correctly installed (the MarkLogic username and password in this example is admin/admin):

   isql -v MarkLogicSQL admin admin

6. If you don't want to have to enter your username and password each time you run isql, you can edit the ~/.odbc.ini file to add your MarkLogic username and password:

   [MarkLogicSQL]
   Description      = MarkLogicSQL
   Driver           = MarkLogicSQL
   Trace            = No
   TraceFile        =
   Database         = marklogic
   Servername       = localhost
   Username         = admin
   Password         = admin
   Port             = 5432
   Protocol         = 7.4
   ReadOnly         = No
   SSLMode          = disable
   UseServerSidePrepare  = Yes
   ShowSystemTables      = No
   ConnSettings          =

7. Test using isql without a username and password:

   isql -v MarkLogicSQL

   If you encounter problems, make sure that the settings in the configuration files point to the right locations for your environment. Calling odbcinst -j will return the list of the configuration files for the ODBC driver.

Enabling Client Certificate Verification on an ODBC App Server

To enable client certificate verification for a MarkLogic ODBC App Server, you need a valid certificate/private key combination. Confirm that the ODBC App Server has a certificate template selected in the ssl certificate template field, has the appropriate certificate client authority selected in the ssl client certificate authorities field, and that ssl require client certificate is set to true.

To configure PostgreSQL for client certificate verification, point to the path of the certificate with the environment variable PGSSLCERT and the path of the private key with PGSSLKEY, as described in https://www.postgresql.org/docs/10/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT.

For details on how to enable SSL for App Servers, see Configuring SSL on App Servers in the Security Guide.

Troubleshooting ODBC Driver Problems

This section describes how to log errors for debugging the ODBC driver.

[ODBC]
Trace=Yes
TraceFile=/tmp/odbctrace.log
Debug=Yes
DebugFile=/tmp/mylog.log

[ODBC][39034][1548446607.890671][__handles.c][460]
               Exit:[SQL_SUCCESS]|
                        Environment = 0x55fa43ca2750

[ODBC][39034][1548446607.890736][SQLAllocHandle.c][375]
               Entry:
                        Handle Type = 2
                        Input Handle = 0x55fa43ca2750

[ODBC][39034][1548446607.890798][SQLAllocHandle.c][493]
               Exit:[SQL_SUCCESS]
                        Output Handle = 0x55fa43ca3050

[ODBC][39034][1548446607.890854][SQLConnect.c][3731]
               Entry:
                        Connection = 0x55fa43ca3050
                        Server Name = [local-sql-els-odbc-7050][length = 23 (SQL_NTS)]
                        User Name = [NULL]
                        Authentication = [NULL]
                        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][39034][1548446608.579017][SQLConnect.c][4309]
               Exit:[SQL_SUCCESS]

[ODBC][39034][1548446608.579076][SQLGetFunctions.c][151]
               Entry:
                        Connection = 0x55fa43ca3050
                        Id = SQLMoreResults
                        Supported = 0x55fa438fc004

[ODBC][39034][1548446608.579098][SQLGetFunctions.c][186]
               Exit:[SQL_SUCCESS]
                        Supported = 0x55fa438fc004 -> 1