SQL Anywhere Linux ODBC and TLS

[#1]

I am setting up a Docker container to connect to a SQL Anywhere database running on the same network. I was able to get it to connect and run a query successfully when I ran the Docker container on the same machine as the database server. I am using the sql Anywhere Linux ODBC client software and unixodbc.

This is the odbc.ini that works when the container is ran on the same machine:

[ODBC Data Sources]
Prototype = SQL Anywhere 17

[Prototype]
Driver=/opt/sqlanywhere17/lib64/libdbodbc17.so
Description=Some Database
Host=192.168.1.11:2638
DatabaseName=databasename
ServerName=servername
UID=username
Integrated=NO
Encryption=SIMPLE
Password=password

Here is how the database is sarted:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLANYs_testdb\Parameters]
"Parameters"="-k -qi -qs -on 10m -o C:\\CPDB\\console.txt -ti240 -tl120 -gnh 500 -gl ALL -gd ALL -x tcpip(ServerPort=2636,2638) -xs HTTPS(PORT=2637;IDENTITY=c:\\somefolder\\odbc.id;IDENTITY_PASSWORD=test) -es -ec SIMPLE,TLS(IDENTITY=c:\\somefolder\\odbc.id;IDENTITY_PASSWORD=test) -n test C:\\CPDB\\test.db @c:\\somefolder\\.tekey"

I am assuming that this works because it is on the same machine and it uses Encryption=SIMPLE. When I run this Docker image on a different machine on the same network with this odbc.ini:

[ODBC Data Sources]
Prototype = SQL Anywhere 17

[Prototype]
Driver=/opt/sqlanywhere17/lib64/libdbodbc17.so
Description=Some Database
Host=192.168.1.11:2638
DatabaseName=databasename
ServerName=servername
UID=username
Integrated=NO
Encryption=TLS(trusted_certificates=*)
Password=password

When I run:

ODBCTRACE=1 ODBCTRACEMODE=console isql -v Prototype

I get:

[08S01][unixODBC][SAP][ODBC Driver][SQL Anywhere]TLS handshake failure
[ISQL]ERROR: Could not SQLConnect

Here is what the public key looks like minus sensitive details:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            <omitted>
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, ST=UT, L=Somewhere, O=Org, OU=Database, CN=*.somecompany.com, [email protected]
        Validity
            Not Before: Feb 28 17:52:02 2024 GMT
            Not After : Feb 15 17:52:02 2074 GMT
        Subject: C=US, ST=UT, L=Somewhere, O=Org, OU=Database, CN=*.somecompany.com, [email protected]
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <omitted>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                <omitted>
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <omitted>

Where can I find the different options that can be used for TLS(...) in the odbc.ini file ?

And does anyone have any suggestions on how I can troubleshoot this ? I think I am just not getting the TLS configured correctly in the odbc.ini. 

[warrenbell · Accepted solution]

I was finally able to get it to work from a Docker container. I did not have the correct cert for the db. Once I got the right cert I was able to connect and run a query. I am going to show my solution below in case someone else comes across the same issues. The Docker container needs to be cleaned up a bit. I am probably installing extra software I do not need.

Here is the ODBC.ini I ended up with: (The Encryption entry may be different depending on your cert and db start up params)

[ODBC Data Sources]
DataSourceName = SQL Anywhere 17

[DataSourceName]
Driver=/opt/sqlanywhere17/lib64/libdbodbc17.so
Description=Some Database
Host=23c8b.somedomain.com:2638
DatabaseName=databasename
ServerName=servername
UID=username
Integrated=NO
Encryption=TLS(trusted_certificates=*;company=23c8b.somedomain.com)
Password=password

Here is the odbcinst.ini file:

[SQL Anywhere 17]
Description=SQL Anywhere ODBC Driver
Driver=/opt/sqlanywhere17/lib64/libdbodbc17.so
Setup=/opt/sqlanywhere17/lib64/libdbodbc17.so

Here is the Dockerfile:

FROM ubuntu:22.04

# Prevent interactive prompts during package installation
ENV DEBIAN_FRONTEND=noninteractive

# First install unixODBC packages
RUN apt-get update && apt-get install -y \
    unixodbc \
    unixodbc-dev \
    odbcinst \
    curl \
    gnupg2 \
    wget \
    ca-certificates \
    libncurses5 \
    build-essential \
    locales \
    inetutils-ping \
    openssl \
    tdsodbc \
    file \
    libaio1 \
    && rm -rf /var/lib/apt/lists/*

# Set locale
RUN locale-gen en_US.UTF-8
ENV LANG en_US.UTF-8
ENV LANGUAGE en_US:en
ENV LC_ALL en_US.UTF-8

# Create directories for SQL Anywhere
RUN mkdir -p /opt/sqlanywhere17/lib64
RUN mkdir -p /opt/setup_files

# This copies over the SQL Anywhere client install sqla17_client_linux_x86x64.tar.gz file. Download from SAP.
COPY ./sql_anywhere_client/ /opt/setup_files/

# Install SQL Anywhere client
RUN cd /opt/setup_files && \
    tar -xzf sqla17_client_linux_x86x64.tar.gz && \
    cd client17011 && \
    chmod +x setup && \
    ./setup -silent -nogui -I_accept_the_license_agreement

# Create wrapper script for odbcinst. This is a hack to get odbcinst to work with the system libraries.
RUN echo '#!/bin/bash\n\
# Temporarily set LD_LIBRARY_PATH to use system libraries for odbcinst\n\
OLD_LD_LIBRARY_PATH="$LD_LIBRARY_PATH"\n\
export LD_LIBRARY_PATH="/lib/x86_64-linux-gnu:$LD_LIBRARY_PATH"\n\
/usr/bin/odbcinst "$@"\n\
RESULT=$?\n\
export LD_LIBRARY_PATH="$OLD_LD_LIBRARY_PATH"\n\
exit $RESULT' > /usr/local/bin/odbcinst_wrapper && \
    chmod +x /usr/local/bin/odbcinst_wrapper

# Set SQL Anywhere environment variables
ENV SQLANY17=/opt/sqlanywhere17
ENV SQLANYSAMP17=/opt/sqlanywhere17/samples
ENV PATH=$SQLANY17/bin64:$SQLANY17/bin32:$PATH
ENV NODE_PATH=$SQLANY17/node:$NODE_PATH
ENV LD_LIBRARY_PATH=$SQLANY17/lib64:$SQLANY17/lib32:$LD_LIBRARY_PATH
ENV ODBCINI=/etc/odbc.ini
ENV ODBCSYSINI=/etc

# Copy your ODBC data source configuration
COPY odbc.ini /etc/odbc.ini
COPY odbcinst.ini /etc/odbcinst.ini

# Register the driver with unixODBC using the wrapper script
RUN /usr/local/bin/odbcinst_wrapper -i -d -f /etc/odbcinst.ini

# Run ldconfig to update the shared library cache
RUN ldconfig

# Clean up setup files to reduce image size
RUN rm -rf /opt/setup_files
    
# Test connection with below
# ODBCTRACE=1 ODBCTRACEMODE=console isql -v DataSourceName username password
# Default command to start bash for debugging
CMD ["/bin/bash"]

There is a lot that can be cleaned up in the Dockerfile. Not sure if the wrapper script is even needed since the driver is hard coded into the odbc.ini file.

[dmo8 · Accepted solution]

Thanks for this. My problem: I miss the libdbodbc17.so in the https://d5d4ifzqzkhwt.cloudfront.net/sqla17client/sqla17_client_linux_x86x64.tar.gz. Any Idee what I do wrong?