Access MSSQL databases in LinOTP via pyodbc

Warning

As the required software stack to connect to MSSQL has proven not fully reliable in every scenario (and differs from distribution to distribution) we do not recommend to use MSSQL in a production environment. This guide is just for academic use. MSSQL is not officially supported by LinOTP due to potential problems in the driver stack.

Introduction

This guide describes how to connect LinOTP to MSSQL databases in order to use them either as UserIdResolver or as backend for LinOTP itself. This how-to uses python-pyodbc and should work for LinOTP version 2.8 upwards and applies (and is recommended) for Debian jessie based LinOTP Virtual Smart Appliance 2.0.

Important:
If you run LinOTP on an old distribution (e.g. Debian squeeze) or LinOTP Virtual Smart Appliance 1.2 please refer to our documentation for python-pymssql.

Install requirements

LinOTP needs an properly configured underlying stack of components to connect to MSSQL databases:

unixodbc --> freetds --> python-pyodbc --> sqlalchemy --> LinOTP

Login to a terminal of your Appliance, enter the unsupported mode and execute the following commands:

apt-get update
apt-get install unixodbc python-pyodbc freetds-bin tdsodbc

Configuration

In this example configuration we assume the following:

  • name of the windows server hosting the MSSQL database: win_ad
  • IP of database server: 172.16.0.100
  • database to which we want to connect: auth
  • table containing the user: user
Tip:
Please make sure that the name of the database server can be resolved correctly to an IP. This can e.g. be achieved by configuring the /etc/hosts of the Appliance.

/etc/hosts (only needed if you do not have a DNS server providing the information to LinOTP/Appliance)

172.16.0.100 win_ad win_ad.example.net

/etc/odbcinst.ini

odbc must be configured to use the freeTDS driver:

[FreeTDS]
#the driver can be somewhere else
#use "find /usr -iname libtdsodbc.so
Driver=/usr/lib/odbc/libtdsodbc.so
Threading=1

For Debian jessie (and so for the LinOTP Smart Virtual Appliance 2.0) changing the configuration file odbcinst.ini is sufficient. You can proceed directly with the UserIdResolver.

The following configuration files are not needed for the proper working of the UserIdresolver but can be helpful if you have to debug problems concerning the connection to the MSSQL database.

/etc/odbc.ini (not needed - try first without)

[win_ad]
#the driver can be somewhere else
#use "find /usr -iname libtdsodbc.so

Driver=/usr/lib/odbc/libtdsodbc.so
Threading=1
Trace=no
#for test purposes:
#Servername=win_ad
#Database=auth
#TDS_Version=8.0

/etc/freetds/freetds.conf (not needed - try first without)

[global]
        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

[win_ad]
        host = win_ad.example.net
;this is the default and does not need to be specified:
;       port = 1433
;OR if you use dynamic ports via instance names:
;               instance = INSTANCE
        client charset = UTF-8
        tds version = 8.0

UserIdResolver

This is how a UserIdResolver could look like for connecting to a MSSQL database (fixed port):

Driver: mssql+pyodbc:///?odbc_connect=
Server: win_ad
Database table: user
Limit: 500
Additional connection parameter: DRIVER={FreeTDS};SERVER={win_ad};DATABASE={auth};
UID={USERNAME};PWD={PASSWORD};PORT={1433};TDS_Version={8.0};

This is how a UserIdResolver could look like for connecting to a MSSQL database (dynamic port + instance):

Driver: mssql+pyodbc:///?odbc_connect=
Server: win_ad
Database table: user
Limit: 500
Additional connection parameter: DRIVER={FreeTDS};SERVER={win_ad\INSTANCE};DATABASE={auth};
UID={USERNAME};PWD={PASSWORD};TDS_Version={8.0};

Tests

Python

This is an example for a MSSQL server running on fixed Port 1433:

import pyodbc
conn = pyodbc.connect(DRIVER='{FreeTDS}',SERVER='win_ad',DATABASE='auth',UID='USER',PWD='PASSWORD',TDS_VERSION='8.0')

This is an example for a MSSQL server configured with instances and dynamic ports:

import pyodbc
conn = pyodbc.connect(DRIVER='{FreeTDS}',SERVER='win_ad\INSTANCE',DATABASE='auth',UID='USER',PWD='PASSWORD',TDS_VERSION='8.0'

unixODBC

This test at the lowest level of the stack will only work if you have configured '/etc/odbcinst.ini' and '/etc/odbc.ini'.

isql -v win_ad 'USERNAME' PASSWORD

freeTDS

This test should work with the configuration for '/etc/freetds/freetds.conf' given above. As long as freeTDS does not connect to the database LinOTP (as it depends on freeTDS) will not be able to access the database as well.

tsql -S win_ad -U "USERNAME" -P PASSWORD