Running LinOTP with Microsoft SQL¶
This describes how to 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.
You need to adapt the following config files:
You need to install the following packages:
LinOTP (shipped by KeyIdentity)
FreeTDS 1 version 0.91
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++
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
Adapt the config file
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”.
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
[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
[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
Now you can define the connection to the database by editing the file
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¶
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