7.3. Running LinOTP with Microsoft SQL

This describes howto setup LinOTP with a connection to a Microsoft SQL Server. In this example LinOTP is running on a RedHat system using the pip install method. On other distributions this may vary.

7.3.1. Overview

You need to adapt the following config files:

• /etc/linotp2/linotp.ini
• /etc/odbc.ini
• /etc/odbcinst.ini
• /usr/local/etc/freetds.conf

You need to install the following packages:

• unixODBC
• pyODBC
• LinOTP (shipped by LSE)
• FreeTDS [1] version 0.91

[1]	(1, 2) http://freetds.schemamania.org/

7.3.2. Installation

7.3.2.1. Install unixODBC

You need to install unixODBC and the devel package as some parts of the python module pyODBC need to get compiled:

yum install unixODBC unixODBC-devel gcc-c++

7.3.2.2. Install pyODBC

Given you installed LinOTP to a virtualenv like /opt/LinOTP, you now can install the ODBC python module:

cd /opt/LINOTP
source bin/activate
pip install pyodbc

7.3.2.3. Install FreeTDS

You need to download version 0.91 of FreeTDS from [1] and build it.

This can usually be done running:

./configure
make
make install

The database driver gets installed to /usr/local. If you want to use any other directory, you need to specify it as parameter for the configure script.

7.3.3. Configuration

7.3.3.1. LinOTP

Adapt the config file /etc/linotp2/linotp.ini:

sqlalchemy.url = mssql+pyodbc://username:password@linotpdb

“linotpdb” is an identifier you may choose to your flavor. This identifier references the configuration in the following config files.

If you want to use the SQLAudit module, you can add a corresponding line accordingly. If the audit data is supposed to be located in another database, you would need to choose a second identifier like “audit_db”.

7.3.3.2. ODBC

You need to tell ODBC that it is also capable of talking to MS SQL databases. You can do this by adding the following to the file /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS driver
Driver=/usr/local/lib/libtdsodbc.so

Now you can define a new database connection by adding an entry to the file /etc/odbc.ini with the corresponding identifier from the linotp.ini file:

[linotpdb]
# see http://www.freetds.org/userguide/odbcconnattr.htm
Driver=FreeTDS
Description=LinOTP database
Servername=TDSServer
Port=1433
Database=linotp2
ClientCharset=UTF-8
tds_version = 7.0

7.3.3.3. FreeTDS

Now you can define the connection to the database by editing the file freetds.conf.

This file usually is located at /usr/local/etc. You should add a section like this:

[TDSServer]
host = 172.16.200.148
port = 1433
instance = SQLEXPRESS
tds version = 7.0
client charset=UTF-8

There you can specify the hostname of the Database server, the port and the instance you want to use.

To test the connection run:

isql linotpdb username password

7.3.3.4. Create the database tables

Note

Please assure the following prerequisites:

• The database and the user for this database who has the right to create tables are already created.

Now you can create the database tables by issuing the command:

paster setup-app /etc/linotp2/linotp.ini