MSSQL-Resolver ODBC Driver and Module error

Question
1

hello i´m trying to configure an mssql resolver to get users from an external application. installation done described in this LinOTP Howto (https://www.linotp.org/howtos/howto-mssql_pyodbc.html) , but i got module and plugin errors.

[privacyidea.lib.user:661] ModuleNotFoundError(“No module named ‘pyodbc’”,)

OS Version:
No LSB modules are available.

Distributor ID: Ubuntu
Description: Ubuntu 18.04.4 LTS
Release: 18.04
Codename: bionic

any ideas or detailed installation guides ?

thanks
hans

2

Hello Hans,
welcome to privacyIDEA.
If you installed privacyIDEA from the repositories it is running in a python virtualenv in /opt/privacyidea.
I.e. pyodbc must be installed in the virtualenv - not via apt.

Hope this helps.
Regards
Cornelius

3

Hello Cornelius,

tanks for this quick replay…

mybe this my main issue, can you describe how to install pyodbc and other modules in this virtual env

regards
hans

4

When Your virtualenv is located in /opt/privacyidea/ You can call
$ source /opt/privacyidea/bin/activate which will activate the virtualenv. Then just call
$ pip install pyodbc. With deactivate You can leave the virtualenv again.

5

due this note pyodbc is installed, but now i got another error when i try to connect MSSQL Database

privacyidea.lib.user:661] InterfaceError("(pyodbc.InterfaceError) (‘IM002’, ‘[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)’)",)

6

It looks like the connect string does not work. Maybe You can find some more information here: https://docs.sqlalchemy.org/en/13/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc
and here: https://docs.microsoft.com/en-us/openspecs/sql_server_protocols/ms-odbcstr/13b4e848-b36c-4b11-acce-d6bf199d5391

7

sorry it’s not really clear for me, where this connection string will be configured, maybe you give an example ?
what i done in the meantime, adding the specific driver to use and the driver error is gone

[MSSQL Resolver Configuration]

Bildschirmfoto 2020-07-20 um 08.07.24

[Output privacyidea.log]

[2020-07-20 06:06:17,882][34428][140247436535552][INFO][privacyidea.lib.resolvers.SQLIdResolver:557] using the connect string mssql+pyodbc:///?odbc_connect=DRIVER={FreeTDS}://UserDB:TopSecret@192.168.30.11:1433/Test

[2020-07-20 06:06:17,887][34428][140247436535552][ERROR][privacyidea.lib.user:661] OperationalError("(pyodbc.OperationalError) (‘08001’, ‘[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)’)",)

[2020-07-20 06:06:19,384][34428][140247503677184][ERROR][privacyidea.lib.user:661] OperationalError("(pyodbc.OperationalError) (‘08001’, ‘[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)’)",)

8

We build the connect string from the fields in the following form:
{driver}://{user}:{password}@{server}:{port}/{databse}?{connectionParams}
So we currently can not pass through the exact PyODBC string, instead the hostname based connect string must be used (pieced together from the given fields):
https://docs.sqlalchemy.org/en/13/dialects/mssql.html#connecting-to-pyodbc