How to set up ODBC access to kosmos

… with special attention to Mac and Linux users. On Windows installations this is already set up, as it is on the managed compute servers.

Prerequisites

You must have Kerberos installed and correctly configured to use ODBC to connect to kosmos. Macs come with Kerberos installed.

If you are on a Mac, you need to have homebrew installed.

We’ll show how to do this in a terminal window. For Mac users: help on Terminal here, and on editing text files here. There is also a helper app for Kerberos in MacOS, called Ticket Viewer.

Step 1: Download and install a ODBC Driver Manager

On the Mac — go to the next step: the driver install as described will also install the driver manager as a dependency.

On Linux — install the unixodbc package from your distribution.

Step 2: Download the Microsoft SQL Server driver

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16 has instructions for both Linux and Mac.

Once the installation is finished, you should be able to run odbcinst -j. Run this in a terminal, and note the DRIVERS and the USER DATA SOURCES keywords; in my case the DRIVERS has the value /etc/odbcinst.ini. This will maybe be different in your machine.

[staber@linux ~]$ odbcinst -j
unixODBC 2.3.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/staber/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Step 3: Check/edit the odbcinst.ini file

The DRIVERS configuration (/etc/odbcinst.ini) should have a stanza that defines the SQL Server ODBC driver:

[staber@linux ~]$ cat /etc/odbcinst.ini 
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

(it may have many more — default locations for other drivers — ignore those!)

The string in hooks ([ODBC Driver 18 for SQL Server]) is the name of the driver, and you’ll need it in the next step. The Driver value is the actual dynamic library file for the driver. This should have been filled in by the installation script. If it is not, then you will need to find it and edit the value to point at it.

Step 4: Edit the ~/.odbc.ini file

The USER DATA SOURCES file will need to be created (“touch ~/.odbc.ini“), and should have the following text:

[staber@linux ~]$ cat ~/.odbc.ini 
[kosmos]
Driver = ODBC Driver 18 for SQL Server
Server = meb-sql02.meb.ki.se
TrustServerCertificate = yes
Trusted_Connection = yes

This will make a DSN (Data Source Name) “kosmos” (in hooks at the top of the file) that connect with the Microsoft ODBC Driver 18 for SQL Server to the database server meb-sql02.meb.ki.se. It will use your MEB login (Trusted_Connection = yes) and it will trust the locally signed certificate from the server (TrustServerCertificate = yes).

Step 5: test with isql and odbcinst

To access the database with your MEB user account (this is the default) you need a Kerberos ticket. Make one by saying kinit (possibly adding your username and the domain, if kinit doesn’t see the right one) , and giving your MEB password.

There are two utilities included with the installation of the driver manager. One is odbcinst that we used above. It can be used for querying the installation for which drivers it knows about (odbcinst -q -d) or for what DSNs it knows (odbcinst -q -s).

isql is a small terminal query application. It is best used for checking your installation: if you say isql -v kosmos (with the setup as described) it should give you a SQL> prompt:

[staber@linux ~]$ kinit 
Password for staber@MEB.KI.SE: 
[staber@linux ~]$ isql -v kosmos
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

If the connection does not work, you’ll get a descriptive error message — for instance, if you misspell the DSN, then this is what you get:

[staber@linux ~]$ isql -v cosmos
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Special tweaks

The DSN specification (in ~/.odbc.ini) can take more parameters (list here); the most common one is Database, where you can specify which database to connect to.