.. _db_mssql:

Running LinOTP with Microsoft SQL
---------------------------------

.. index:: Microsoft SQL Server

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.

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 [#freetds]_ version 0.91

.. [#freetds] http://freetds.schemamania.org/
 
Installation
~~~~~~~~~~~~

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++

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

Install FreeTDS
...............

You need to download version 0.91 of FreeTDS from [#freetds]_ 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.

Configuration
~~~~~~~~~~~~~

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".

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


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


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