Run error when using docker with MS SQL server (odbc package)

ShinyProxy
#1

Hello! How are you? I hope you are doing well :slight_smile:

I am trying yo use the odbc package to connect to a ms sql server data base for my application. When I run it locally, for example, on Rstudio, it does work just fine but when I try to run it on shiny proxy, or in a container directly, it doesnt work.

Here is the code of the dockerfile

FROM openanalytics/r-base

#update all packages
RUN apt-get update

#upgrade
RUN apt-get upgrade -y

#install additional packages
RUN apt install gpg-agent -y unixodbc apt-utils curl unixodbc-dev \
    sudo \
    pandoc \
    pandoc-citeproc \
    libcurl4-gnutls-dev \
    libcairo2-dev \
    libxt-dev \
    libssl-dev \
    libssh2-1-dev \
    libssl1.1 \
    libmpfr-dev \
    libxml2-dev

# R packages
RUN R -e "install.packages(c('shiny', 'rmarkdown'), repos='https://cloud.r-project.org/')"
RUN R -e "install.packages('Rmpfr', repos='https://cloud.r-project.org/')"
RUN R -e "install.packages('shinydashboard', repos='https://cloud.r-project.org/')"
RUN R -e "install.packages('xml2')"
RUN R -e "install.packages('rvest')"
RUN R -e "install.packages('plotly', repos='https://cloud.r-project.org/')"
RUN R -e "install.packages('reactable', repos='https://cloud.r-project.org/')"
RUN R -e "install.packages('tidyverse')"
RUN R -e "install.packages('DBI')"

#get msodbcsql17 and install it
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update -y
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17 
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools

#install packaes needed for running the app
RUN R -e "install.packages(c('odbc', 'data.table'))"

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

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

EXPOSE 3838

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

And here is the line where I connect to the data base

db_Inv <- DBI::dbConnect(odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server", Server = "*****", Database = "Investigacion", UID = "*****", PWD = "*****", Port = 1433)

Where I hide the server, uid and pwd. As you can see, on the Dockerfile there are some lines about installing ms sql server drivers. I run the same lines on rstudio console and everything work just fine.

The problem when I run the code on shinyproxy or in the container directly is this:

Warning: Error in : nanodbc/nanodbc.cpp:1021: 00000: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute
57:
Error : nanodbc/nanodbc.cpp:1021: 00000: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

Do you have a guess?

Thanks!

#2

Hi

As you can see in the error, the database driver cannot establish a connection to your SQL server. If you are referring to a local IP, this probably won’t work in the Docker container right away. Make sure to use an IP that is accessible from the Docker container (e.g. a public IP, but not something like 127.0.0.1). If you are already using a public IP or DNS name, make sure that the Docker server is allowed to connect to that SQL server (e.g. check the firewall or settings of the SQL server).
In order to debug this, you could try logging in on one of the Docker containers and try to establish a connection using the CLI tools (e.g. the driver tool, or something like telnet).