{"id":884,"date":"2024-01-30T14:12:34","date_gmt":"2024-01-30T13:12:34","guid":{"rendered":"https:\/\/www.meb.ki.se\/sites\/meb-it\/?p=884"},"modified":"2024-02-02T13:54:37","modified_gmt":"2024-02-02T12:54:37","slug":"how-to-set-up-odbc-access-to-kosmos","status":"publish","type":"post","link":"https:\/\/www.meb.ki.se\/sites\/meb-it\/how-to-set-up-odbc-access-to-kosmos\/","title":{"rendered":"How to set up ODBC access to kosmos"},"content":{"rendered":"\n

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

Prerequisites<\/h3>\n\n\n\n

You must have Kerberos<\/strong><\/code> installed and correctly configured to use ODBC to connect to kosmos. Macs come with Kerberos installed.<\/p>\n\n\n\n

If you are on a Mac, you need to have homebrew<\/strong><\/code> installed.<\/p>\n\n\n\n

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

Step 1: Download and install a ODBC Driver Manager<\/h2>\n\n\n\n

On the Mac — go to the next step<\/a>: the driver install as described will also install the driver manager as a dependency.<\/p>\n\n\n\n

On Linux — install the unixodbc<\/strong><\/code> package from your distribution.<\/p>\n\n\n\n

Step 2: Download the Microsoft SQL Server driver <\/h2>\n\n\n\n

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

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

[staber@linux ~]$ odbcinst -j\nunixODBC 2.3.12\nDRIVERS............: \/etc\/odbcinst.ini\nSYSTEM DATA SOURCES: \/etc\/odbc.ini\nFILE DATA SOURCES..: \/etc\/ODBCDataSources\nUSER DATA SOURCES..: \/home\/staber\/.odbc.ini\nSQLULEN Size.......: 8\nSQLLEN Size........: 8\nSQLSETPOSIROW Size.: 8<\/code><\/pre>\n\n\n\n
Step 3: Check\/edit the odbcinst.ini file<\/h2>\n\n\n\n
The DRIVERS configuration (\/etc\/odbcinst.ini<\/code>) should have a stanza that defines the SQL Server ODBC driver:<\/p>\n\n\n\n
[staber@linux ~]$ cat \/etc\/odbcinst.ini \n[ODBC Driver 18 for SQL Server]\nDescription=Microsoft ODBC Driver 18 for SQL Server\nDriver=\/opt\/microsoft\/msodbcsql\/lib64\/libmsodbcsql-18.3.so.2.1\nUsageCount=1<\/code><\/pre>\n\n\n\n
(it may have many more — default locations for other drivers — ignore those!)<\/p>\n\n\n\n
The string in hooks ([ODBC Driver 18 for SQL Server]<\/code>) is the name<\/strong> of the driver, and you’ll need it in the next step. The Driver<\/strong> 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.<\/p>\n\n\n\n
Step 4: Edit the ~\/.odbc.ini file<\/h2>\n\n\n\n
The USER DATA SOURCES file will need to be created (“touch ~\/.odbc.ini<\/code>“), and should have the following text:<\/p>\n\n\n\n
[staber@linux ~]$ cat ~\/.odbc.ini \n[kosmos]\nDriver = ODBC Driver 18 for SQL Server\nServer = meb-sql02.meb.ki.se\nTrustServerCertificate = yes\nTrusted_Connection = yes<\/code><\/pre>\n\n\n\n
This will make a DSN (D<\/strong>ata S<\/strong>ource N<\/strong>ame)  “kosmos<\/strong><\/code>” (in hooks at the top of the file) that connect with the Microsoft ODBC Driver 18 for SQL Server<\/strong><\/code> to the database server meb-sql02.meb.ki.se<\/strong><\/code>. It will use your MEB login (Trusted_Connection = yes<\/code>) and it will trust the locally signed certificate from the server (TrustServerCertificate = yes<\/code>).<\/p>\n\n\n\n
Step 5: test with isql and odbcinst<\/h2>\n\n\n\n
To access the database with your MEB user account (this is the default) you need a Kerberos ticket. Make one by saying kinit<\/code> (possibly adding your username and the domain, if kinit doesn’t see the right one) , and giving your MEB password. <\/p>\n\n\n\n
There are two utilities included with the installation of the driver manager. One is odbcinst<\/code> that we used above. It can be used for querying the installation for which drivers it knows about (odbcinst -q -d)<\/code> or for what DSNs it knows (odbcinst -q -s<\/code>).<\/p>\n\n\n\n
isql<\/code> is a small terminal query application. It is best used for checking your installation: if you say isql -v kosmos<\/code> (with the setup as described) it should give you a SQL> <\/code> prompt:<\/p>\n\n\n\n
[staber@linux ~]$ kinit \nPassword for staber@MEB.KI.SE: \n[staber@linux ~]$ isql -v kosmos\n+---------------------------------------+\n| Connected!                            |\n|                                       |\n| sql-statement                         |\n| help [tablename]                      |\n| echo [string]                         |\n| quit                                  |\n|                                       |\n+---------------------------------------+\nSQL> <\/code><\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
[staber@linux ~]$ isql -v cosmos\n[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified\n[ISQL]ERROR: Could not SQLConnect<\/code><\/pre>\n\n\n\n
Special tweaks<\/h2>\n\n\n\n
The DSN specification (in ~\/.odbc.ini<\/code>) can take more parameters (list here<\/a>); the most common one is Database<\/code>, where you can specify which database to connect to.  <\/p>\n","protected":false},"excerpt":{"rendered":"
… 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11,33,32,22],"tags":[],"class_list":["post-884","post","type-post","status-publish","format-standard","hentry","category-dba","category-linux","category-mac","category-sql"],"blocksy_meta":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/884","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/comments?post=884"}],"version-history":[{"count":15,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/884\/revisions"}],"predecessor-version":[{"id":904,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/posts\/884\/revisions\/904"}],"wp:attachment":[{"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/media?parent=884"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/categories?post=884"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.meb.ki.se\/sites\/meb-it\/wp-json\/wp\/v2\/tags?post=884"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}