Driver error mssql+pyodbc Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0)

[Kiara0107]

Describe the bug
Error when trying to connect the database, log shows:

2023-08-18 08:20:27,219 - ERROR - query-exporter - error from database "database_01": (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
(Background on this error at: http://sqlalche.me/e/13/dbapi)

Installation details

• operating system: Ubuntu 20.04.6 LTS (GNU/Linux 5.4.0-156-generic x86_64)
• query-exporter installation type:
  • pip: [output from pip list]
  • docker image: adonato/query-exporter:latest
  • snap: [output from snap info query-exporter]

name:      query-exporter
summary:   Export Prometheus metrics from SQL queries
publisher: Alberto Donato (ack✪)
store-url: https://snapcraft.io/query-exporter
contact:   https://github.com/albertodonato/query-exporter/issues
license:   GPL-3.0+
description: |
  query-exporter is a Prometheus exporter which allows collecting metrics from
  database queries, at specified time intervals or when a request to the
  metrics endpoint is performed.

  Each query can be run on multiple databases, and update multiple metrics.

  The snap provides both the `query-exporter` command and a deamon instance of
  the command, managed via a Systemd service.
  To run the latter:

    - create or edit `/var/snap/query-exporter/current/config.yaml` with the
      configuration
    - run `sudo snap restart query-exporter`

  Currently supported databases are:

    - PostgreSQL (`postgresql://`)
    - MySQL (`mysql://`)
    - SQLite (`sqlite://`)
    - Microsoft SQL Server (`mssql://`)
    - IBM DB2 (`db2://`) on supported architectures (x86_64, ppc64le and
      s390x)
snap-id: ssukadvQVTCbvJ0h7QH6ouseq7X9vbib
channels:
  latest/stable:    2.8.1              2022-02-18 (836) 43MB -
  latest/candidate: ↑
  latest/beta:      ↑
  latest/edge:      2.8.3+git2.4f9442b 2023-04-12 (882) 47MB -

To Reproduce

If possible, please provide detailed steps to reproduce the behavior:

1. Config file content (redacted of secrets if needed)

databases:
  database_01:
    dsn: mssql+pyodbc://{{ user }}:{{ password }}@{{ host }}:1433/{{ db }}?driver=ODBC+Driver+17+for+SQL+Server&MARS_Connection=yes
    keep-connected: false
    autocommit: false

2. Ran query-exporter with the following command line ...

docker run -d  -it -p 9560:9560/tcp \
      -v /etc/query/query.yml:/config.yaml \
      --restart unless-stopped \
      --name query-exporter adonato/query-exporter:latest

3. Got the error when ...
   As soon as the first connection attempt is made.

Query exporter metrics:

# HELP database_errors_total Number of database errors
# TYPE database_errors_total counter
database_errors_total{database="database_01""} 22756.0
# HELP database_errors_created Number of database errors
# TYPE database_errors_created gauge
database_errors_created{database="database_01"} 1.6921779978228438e+09
# HELP queries_total Number of database queries
# TYPE queries_total counter
# HELP query_latency Query execution latency
# TYPE query_latency histogram
# HELP exceptionlog_count Sum of log entries in ExceptionLog table
# TYPE exceptionlog_count gauge

[albertodonato]

This should be fixed in 2.9.0, which is also current latest docker image

[Kiara0107]

Unfortunately not. To be sure I removed the docker image and pulled the latest image from the repository.

~$ docker images
REPOSITORY                        TAG       IMAGE ID       CREATED        SIZE
adonato/query-exporter            latest    98ec5e988c9c   2 days ago     515MB

Still the same error.

~$ docker logs query-exporter
2023-08-21 07:32:33,727 - ERROR - query-exporter - error from database "database_01": (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

[albertodonato]

Can you try using the dictionary format for the dsn, just to make sure it's not an issue with passing driver options?

[Kiara0107]

What do you mean by 'dictionary format' for the dsn?

[albertodonato]

dsn:
  dialect: <dialect>[+driver]
  user: <username>
  password: <password>
  host: <host>
  port: <port>
  database: <database>
  options:
    <key1>: <value1>
    <key2>: <value2>

as shown in the readme

[Kiara0107]

Ah, yes, tried that as well. Unfortunately the same result:

2023-08-23 13:07:14,425 - ERROR - query-exporter - error from database "database_01": (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
(Background on this error at: http://sqlalche.me/e/13/dbapi)

In query.yml

databases:
  database_01:
    dsn:
      dialect: mssql+pyodbc
      user: {{user}}
      password: {{password}}
      host: {{host}}
      port: 1433
      database: database_01
      options:
        driver: 'ODBC Driver 17 for SQL Server'
        MARS_Connection: yes
    keep-connected: false
    autocommit: false

[Kiara0107]

@albertodonato any updates or suggestions on this matter?

[Kiara0107]

I found a sort of work-around. When I update line 49 to ACCEPT_EULA=Y apt-get install -y --no-install-recommends msodbcsql17 && \ it works. And then ofcourse build locally a new image

[albertodonato]

Would you mind proposing a PR with the change?

[ilantnt]

@albertodonato Hi, I have an local branch ready for this fix (as written above), Can you add me permissions to submit my branch?

[albertodonato]

What do you mean? You don't need permissions to make a PR with your changes

[Drakonhawk]

With the current 2.9.2 image you have to change the URL to use ODBC driver 18 and ignore the self signed SSL certificate.

from:
mssql+pyodbc://user:password@host_ip:port/database?driver=ODBC+Driver+17+for+SQL+Server&MARS_Connection=Yes

to:
mssql+pyodbc://user:password@host_ip:port/database?TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server&MARS_Connection=Yes