'ODBC Driver 18 for SQL Server' : file not found (connecting to database)

[rfsonders]

Using the latest DockerFile posted in this repo I am unable to connect via the HammerDB cli.

Error: Transaction Counter Error: connection failed [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (connecting to database)

I have added a separate RUN in the DockerFile using this pattern from Microsoft, for an additional test. This feels duplicative from the already scripted DockerFil in this repo. Both tests fail.
Transaction Counter Error: connection failed [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (connecting to database)

I can connect to the SQL 2022 in a linux container environment, from any Windows DB admin app, but not with HammerDB CLI in the container.
Thoughts?

[sm-shaw]

Initial thoughts are there is a blog on this topic here in a non container environment https://www.hammerdb.com/blog/uncategorized/configuring-unixodbc-and-sql-server-drivers-for-hammerdb-on-linux/

We will check the container

[rfsonders]

In the HammerDB DockerFile, in this repo, all is scripted correctly. I have cross-referenced the Microsoft. Which I also posted above.
In this section, all looks in order.

Install and configure Microsoft SQLServer client libraries

However, I still get the ODBC 18 not found error.
Have others used the DockerFile posted here to successfully connect to SQL 2022?

[rfsonders]

Testing a build with unixODBC 2.3.11. The current DockerFile uses 2.3.9.

Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/l/linux/linux-libc-dev_5.4.0-136.153_amd64.deb 404 Not Found [IP: 91.189.91.39 80]

Maybe this is the reason the HammerDb DockerFile remains at 2.3.9 ?

I will keep testing and report back.

[pooja-jain-17]

Thanks for testing this. Sure do let us know how it goes.

For now, Dockerfile is tested and works with ODBC Driver 17 for SQL Server (perhaps 2019)
We will plan to update unixODBC and move to ODBC 18 for v4.7. Thanks.

[rfsonders]

Hello.
Updating all references for 2.3.9 replaced with 2.3.11, does fail in the build. Let me know what you find.

[pooja-jain-17]

Hello, So I replaced the installation for UnixODBC 2.3.9 to 2.3.11 and ODBC Driver 17 to 18 , with some changes and the container builds fine. I haven't tested it against the database yet. I will look into this further.

Snippet as below:

Install and configure Microsoft SQLServer client libraries

RUN apt -y install -q
apt -y curl gcc make &&
apt update && apt install -y lsb-release &&
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - &&
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list &&
apt-get update &&
ACCEPT_EULA=Y apt-get install -y msodbcsql18 &&
ACCEPT_EULA=Y apt-get install -y mssql-tools18 && echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc &&
wget http://www.unixodbc.org/unixODBC-2.3.11.tar.gz && tar -xvzf unixODBC-2.3.11.tar.gz && cd unixODBC-2.3.11 &&
./configure --prefix=/usr/local/unixODBC --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8
--with-iconv-ucode-enc=UTF16LE --enable-threads=yes --enable-fastvalidate && make && make install && cd .. &&
echo 'export PATH="$PATH:/opt/mssql-tools18/bin:/usr/local/unixODBC/bin"' >> ~/.bashrc &&
echo 'export ODBCINI="/usr/local/unixODBC/etc/odbc.ini"' >> ~/.bashrc &&
echo 'export ODBCSYSINI="/usr/local/unixODBC/etc"' >> ~/.bashrc &&
echo 'export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/local/unixODBC/lib"' >> ~/.bashrc &&
rm -rf *.tar.gz *.zip unixODBC-2.3.11

[sm-shaw]

I have tested the connectivity for both ODBC 17 & 18 following the instructions here:
https://www.hammerdb.com/blog/uncategorized/configuring-unixodbc-and-sql-server-drivers-for-hammerdb-on-linux/

Only ODBC 17 is installed by default in the current v4.6 Docker release (although ODBC 18 is the HammerDB default) and also the correct odbcinst.ini is not updated for ODBC 17, however adding the correct entry to odbcinst.ini in /usr/local/unixODBC/etc then works correctly connecting to an external SQL Server database on Windows with ODBC 17.

For ODBC 18 it is necessary to install the drivers here as described here https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server, however the Ubuntu instructions require the lsb-release command and this is not installed by default. There you need to run:

apt-get install -y lsb-release

first, for the instructions to work correctly.

After installing lsb-release do:

apt-get install -y msodbcsql18
Unpacking msodbcsql18 (18.1.2.1-1) ...
Setting up msodbcsql18 (18.1.2.1-1) ...
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /usr/local/unixODBC/etc

Note it says that the target directory is /usr/local/unixODBC/etc

We can then verify the install:

hammerdb@REDPOLL:~$ ls /opt/microsoft/
msodbcsql17  msodbcsql18

root@REDPOLL:/usr/local/unixODBC/etc# more odbcinst.ini 
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
UsageCount=1

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

root@REDPOLL:/home/hammerdb/HammerDB-4.6# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/unixODBC/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@REDPOLL:/home/hammerdb/HammerDB-4.6# odbcinst -d -q
[ODBC Driver 17 for SQL Server]
[ODBC Driver 18 for SQL Server]

In my tests I can then use both ODBC Driver 17 and ODBC Driver 18 from within the Docker container.

So the Docker container for v4.7 needs to be updated to:

1. include the lsb_release command
2. Update unixODBC to 2.3.11 as above
3. Add ODBC Driver 18 (and ODBC Driver 17)
4. Update /usr/local/unixODBC/etc/odbcinst.ini with both drivers

Then the SQL Server for Linux connectivity should work without further configuration.

[rfsonders]

1. lsb_release

Hello, So I replaced the installation for UnixODBC 2.3.9 to 2.3.11 and ODBC Driver 17 to 18 , with some changes and the container builds fine. I haven't tested it against the database yet. I will look into this further.

Snippet as below:

Install and configure Microsoft SQLServer client libraries

RUN apt -y install -q apt -y curl gcc make && apt update && apt install -y lsb-release && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list && apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql18 && ACCEPT_EULA=Y apt-get install -y mssql-tools18 && echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc && wget http://www.unixodbc.org/unixODBC-2.3.11.tar.gz && tar -xvzf unixODBC-2.3.11.tar.gz && cd unixODBC-2.3.11 && ./configure --prefix=/usr/local/unixODBC --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-threads=yes --enable-fastvalidate && make && make install && cd .. && echo 'export PATH="$PATH:/opt/mssql-tools18/bin:/usr/local/unixODBC/bin"' >> ~/.bashrc && echo 'export ODBCINI="/usr/local/unixODBC/etc/odbc.ini"' >> ~/.bashrc && echo 'export ODBCSYSINI="/usr/local/unixODBC/etc"' >> ~/.bashrc && echo 'export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/local/unixODBC/lib"' >> ~/.bashrc && rm -rf *.tar.gz *.zip unixODBC-2.3.11

When testing this. I am getting a build error.
ERROR [ 5/12] RUN apt -y install -q && apt -y curl gcc make && apt update && apt install -y lsb-release && curl https 3.3s

#9 3.224 E: Invalid operation curl

[rfsonders]

What I am using that is failing on build.

Install and configure Microsoft SQLServer client libraries

RUN apt -y install -q &&
apt -y curl gcc make &&
apt update &&
apt install -y lsb-release &&
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - &&
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list &&
apt-get update &&
ACCEPT_EULA=Y apt-get install -y msodbcsql18 &&
ACCEPT_EULA=Y apt-get install -y mssql-tools18 && echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc &&
wget http://www.unixodbc.org/unixODBC-2.3.11.tar.gz && tar -xvzf unixODBC-2.3.11.tar.gz && cd unixODBC-2.3.11 &&
./configure --prefix=/usr/local/unixODBC --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8
--with-iconv-ucode-enc=UTF16LE --enable-threads=yes --enable-fastvalidate && make && make install && cd .. &&
echo 'export PATH="$PATH:/opt/mssql-tools18/bin:/usr/local/unixODBC/bin"' >> ~/.bashrc &&
echo 'export ODBCINI="/usr/local/unixODBC/etc/odbc.ini"' >> ~/.bashrc &&
echo 'export ODBCSYSINI="/usr/local/unixODBC/etc"' >> ~/.bashrc &&
echo 'export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/local/unixODBC/lib"' >> ~/.bashrc &&
rm -rf *.tar.gz *.zip unixODBC-2.3.11

[pooja-jain-17]

Well, Probably some formatting error in the comment section. Please refer the snippet here. Docker builds fine.

https://github.com/pooja-jain-17/HammerDB/blob/docker-4.7/Docker/Dockerfile

[rfsonders]

Well, Probably some formatting error in the comment section. Please refer the snippet here. Docker builds fine.

https://github.com/pooja-jain-17/HammerDB/blob/docker-4.7/Docker/Dockerfile

I still see this issue with your latest 4.7 repo build.

#8 40.95 Err:39 http://security.ubuntu.com/ubuntu focal-updates/main amd64 linux-libc-dev amd64 5.4.0-136.153
#8 40.95 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 Fetched 35.6 MB in 38s (938 kB/s)
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/krb5-locales_1.17-6ubuntu4.1_all.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libkrb5support0_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libk5crypto3_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libkrb5-3_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libgssapi-krb5-2_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/l/linux/linux-libc-dev_5.4.0-136.153_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 40.97 E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

[pooja-jain-17]

I have noticed this behavior when we have broken builds on the system. Please check if there are any containers running,
'docker ps -a'

Once try post removing the prior hammerdb images and containers.

[pooja-jain-17]

@sm-shaw , The RUN command specified above has all the recommendations in your comment, except for
4. Update /usr/local/unixODBC/etc/odbcinst.ini with both drivers

From within the container,

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# ls /opt/microsoft/
msodbcsql18

I have added this manually in the file odbcinst.ini file for now. Need to add it to the Dockerfile.

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# more /usr/local/unixODBC/etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/unixODBC/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# odbcinst -d -q
[ODBC Driver 18 for SQL Server]

[rfsonders]

The RUN command specified above has all the recommendations in your comment, except for

Really good tip regarding removing previous containers. I did have a few running. I did clean up everything 100%, but still the error regarding the ubuntu security.

#8 43.55 Err:39 http://security.ubuntu.com/ubuntu focal-updates/main amd64 linux-libc-dev amd64 5.4.0-136.153
#8 43.55 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 Fetched 35.6 MB in 40s (885 kB/s)
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/krb5-locales_1.17-6ubuntu4.1_all.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libkrb5support0_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libk5crypto3_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libkrb5-3_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/k/krb5/libgssapi-krb5-2_1.17-6ubuntu4.1_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/l/linux/linux-libc-dev_5.4.0-136.153_amd64.deb 404 Not Found [IP: 91.189.91.38 80]
#8 43.59 E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

[pooja-jain-17]

Do remove any broken images as well.

docker images
docker rmi imageid

and then do a docker build without cache
docker build --no-cache -t hammerdb-v4.6 .

Hope this helps.

[sm-shaw]

@pooja-jain-17 one check for updating odbcinst.ini is that it is done after the driver installation. If the entry is already there, then I found the driver will not install and gives a message that it is already installed.

@sm-shaw , The RUN command specified above has all the recommendations in your comment, except for 4. Update /usr/local/unixODBC/etc/odbcinst.ini with both drivers

From within the container,

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# ls /opt/microsoft/
msodbcsql18

I have added this manually in the file odbcinst.ini file for now. Need to add it to the Dockerfile.

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# more /usr/local/unixODBC/etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/unixODBC/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@795f05bc435c:/home/hammerdb/HammerDB-4.6# odbcinst -d -q
[ODBC Driver 18 for SQL Server]

[pooja-jain-17]

Thanks Steve. That's taken into account. For next version of HammerDB we will have UnixODBC 2.3.11 and ODBC Driver 18 for MSSQL Server client libraries. I have updated the Dockerfile here here to reflect the same.
https://github.com/pooja-jain-17/HammerDB/blob/docker-4.7/Docker/Dockerfile