Unable to connect to Microsoft SQL Server inside Docker container using FreeTDS

Question

I want to host Shiny applications on my company network using Docker for Windows.

How do I set up the Docker, odbc.ini, odbcinst.ini, freetds.conf, or possibly other files so that my Shiny application can query data from an internal Microsoft SQL Server (2016) database? The database server is not running on the same machine running the Docker container.

I don't know if I need a newer version of FreeTDS or if I have mis-configured one of the files. I tried using the IP address to the server instead of sql-server.host.com in all files but get the same error message below.

$ tsql -C output:

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.104
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: no

$ odbcinst -j output:

unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat etc/odbcinst.ini output:

[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

$ cat etc/odbc.ini output:

[sql-server]
driver = FreeTDS
server = sql-server.host.com
port = 1433
TDS_Version = 4.2

$ cat etc/freetds/freetds.conf output:

[sql-server]
host = sql-server.host.com
port = 1433
tds version = 4.2

Command in R giving error:

con <- dbConnect(odbc::odbc(),
          driver = "FreeTDS",
          server = "sql-server.host.com",
          port = 1433,
          database = "database name",
          TDS_Version = 4.2)

Error:

Error: nanodbc/nanodbc.cpp:950: 08001: [FreeTDS][SQL Server]Unable to connect to data source 
Execution halted

Docker file:

# Install R version 3.5.3
FROM r-base:3.5.3

# Install Ubuntu packages
RUN apt-get update && apt-get install -y \
    sudo \
    gdebi-core \
    pandoc \
    pandoc-citeproc \
    libcurl4-gnutls-dev \
    libcairo2-dev/unstable \
    libxt-dev \
    libssl-dev \
    unixodbc unixodbc-dev \
    freetds-bin freetds-dev tdsodbc

# Edit odbc.ini, odbcinst.ini, and freetds.conf files
RUN echo "[sql-server]\n\
host = sql-server.host.com\n\
port = 1433\n\
tds version = 4.2" >> /etc/freetds.conf

RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" >> /etc/odbcinst.ini

RUN echo "[sql-server]\n\
driver = FreeTDS\n\
server = sql-server.host.com\n\
port = 1433\n\
TDS_Version = 4.2" >> /etc/odbc.ini

# Install R packages that are required
RUN R -e "install.packages(c('shiny', 'DBI', 'odbc'), repos='http://cran.rstudio.com/')"

# copy the app to the image
RUN mkdir /root/shiny_example
COPY app /root/shiny_example

COPY Rprofile.site /usr/lib/R/etc/

# Make the ShinyApp available at port 801
EXPOSE 801

CMD ["R", "-e", "shiny::runApp('/root/shiny_example')"]

Docker build and run commands:

docker build . -t shiny_example
docker run -it --network=host -p 801:801 shiny_example

Note that following R code works on my Windows machine running the Docker container and I can successfully query the database:

library(DBI)
con <- dbConnect(odbc::odbc(),
          driver = "SQL server",
          server = "sql-server.host.com")

$ isql -v sql-server output:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

$ tsql -S sql-server output:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20013 (severity 2):
        Unknown host machine name.
There was a problem connecting to the server

Answer 1

It looks like you're correct, but might have missed a little. I had similar issue, but I was able to fix it!

On python+mssqlserver+pymssql+docker(unbuntu16.04 base image)

Without fixing the end, upon running my code(using pymssql) was giving me this error

Traceback (most recent call last):
  File "<stdin>", line 4, in <module>
  File "src/pymssql.pyx", line 645, in pymssql.connect
pymssql.InterfaceError: Connection to the database failed for an unknown reason.

I followed created 3 files and copied in the image!

• Use myserver.orgName.com where MSsql server is hosted. docker/odbcinst.ini

[FreeTDS]
Description = v0.91 with protocol v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

docker/odbc.ini

[myserverdsn]
Driver = FreeTDS
Server = myserver.orgName.com
Port = 1433
TDS_Version = 7.3

docker/freetds.conf

[global]
    # TDS protocol version, use:
    # 7.3 for SQL Server 2008 or greater (tested through 2014)
    # 7.2 for SQL Server 2005
    # 7.1 for SQL Server 2000
    # 7.0 for SQL Server 7
    tds version = 7.2
    port = 1433

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field.
    # Try setting 'text size' to a more reasonable limit
    text size = 64512

# A typical Microsoft server
[myserverdsn]
    host = myserver.orgName.com
    port = 1433
    tds version = 7.3

Dockerfile content

RUN apt-get -y install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc 

COPY freetds.conf /etc/freetds/freetds.conf
COPY odbc.ini /etc/odbc.ini
COPY odbcinst.ini /etc/odbcinst.ini

Test python code that works:

python 
>>> import pymssql
>>> conn = pymssql.connect(server = 'myserver.orgName.com',
                   user = 'myusername',
                      password = 'mypassword',
                      database= 'mydbname')

works without error!