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