Setting up docker image with R and SQL server drivers

Question

This is probably going to be an underspecified question, but as I’ve spent close to two days trying to get it to work I figured I might give it a try:

I am trying to set up a docker image that can comminicate with a SQL Server db through R (either using RODBC or odbc)

The problem I keep encountering seems to be that I fail to install (or locate) the neccisary SQL server drivers when establishing a connection, when running:

 con <- dbConnect(odbc(),
             Driver = "SQL Server",
             Server = "xxxx",
             Database = "xxxx",
             UID = "xxx",
             PWD = “xxxx")

Has anyone set up a similar Docker image ?

EDIT:

Here is my current dockerfile (I have tried multiple things but this one reproduces the error message)

    FROM rocker/r-ver:3.5.0

    RUN apt-get -qq update \
        && apt-get -qq dist-upgrade -y \
        && apt-get -qq install git unixodbc unixodbc-dev postgresql-9.5 odbc-postgresql libssl-dev sudo -y

    COPY . /usr/local/src/myscripts
    WORKDIR /usr/local/src/myscripts

    RUN R -e 'install.packages("odbc")'
    RUN R -e 'install.packages("plumber")'

    EXPOSE 8000

    CMD ["Rscript", "plumber.R"]

And running the container and executing odbc commands gives:

    > library('odbc')
    > con <- dbConnect(odbc(),
    +                    Driver = "SQL Server",
    +                    Server = "xx",
    +                    Database = "xx",
    +                    UID = "xx",
    +                    PWD = "xx")
    Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

Where is the database? In a volume or is it an external connection? The basic idea is that you'd load an R image in your `Dockerfile`, and then import any additional dependencies, like your SQL Server drivers/connector. You also have to make the DB visible to the Docker container, either via a volume, or opening a port. — Mako212, Oct 23 '18 at 17:16
@OrtomalaLokni - I have added the Dockerfile to the question — Andri Már Stefánsson, Oct 23 '18 at 19:30
@Mako212 - this would be an external connection - so yes I would have to open a port. I now how to do it for the plumber API, yet to learn how to do it for the SQL db connection. — Andri Már Stefánsson, Oct 23 '18 at 19:33

score 12 · Accepted Answer · answered Oct 23 '18 at 19:29

12

In your Dockerfile you are installing ODBC drivers for PostgrSQL but not for MS SQL server. In a docker image that needed MS SQL I used the drivers from Microsoft:

FROM rocker/r-ver:3.5.1

RUN apt-get update \
 && apt-get install --yes --no-install-recommends \
        apt-transport-https \
        curl \
        gnupg \
        unixodbc-dev \
 && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
 && curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list \
 && apt-get update \
 && ACCEPT_EULA=Y apt-get install --yes --no-install-recommends msodbcsql17 \
 && install2.r odbc \
 && apt-get clean \
 && rm -rf /var/lib/apt/lists/* \
 && rm -rf /tmp/*

In the R script I then used

con <- odbc::dbConnect(odbc::odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = Sys.getenv("SERVER"),
                 Database = Sys.getenv("DB"),
                 UID = Sys.getenv("USER"),
                 PWD = Sys.getenv("PWD"))

answered Oct 23 '18 at 19:29

Ralf Stubner

26,263
3
40
75

@RalfStaubner Thank I'll try this out. One of the things I tried was modifying this - https://gist.github.com/ibombonato/fc88f5d5418e42abf3be3449c9893a01 - but had no luck building this image – Andri Már Stefánsson Oct 23 '18 at 19:40
Using this docker file the build quits with the following error `ERROR: Job failed: command terminated with exit code 1` – Kresten Apr 24 '19 at 08:04
@atomman Context? Does the error happen during `docker build` or `docker run`? Which command triggers the error? – Ralf Stubner Apr 24 '19 at 08:10
I'm setting up a docker image to use for CI of r packages. during `docker build .` the build ends. To me it seems like a time out error. – Kresten Apr 24 '19 at 08:31
@atomman Why do you think it is a time out? Which command fails? – Ralf Stubner Apr 24 '19 at 08:47
@RalfStubner Can you please help wrt Snowflake drivers https://stackoverflow.com/questions/75016156/snowflaker-setting-up-docker-image-with-r-and-snowflake-drivers – Karthik Jan 05 '23 at 12:45

Setting up docker image with R and SQL server drivers

1 Answers1