Issue connecting to SQL Server instance using ODBC driver

[1ct0]

Container image name

rocker/tidyverse:4.1.1

Container image digest

No response

What operating system related to this question?

Linux

System information

No response

Question

Below (code block 1) is my dockerfile where I am attempting to set up the environment to run an R-script. The script needs to connect to a SQL server instance and query a table (example in code block 2).

FROM rocker/tidyverse:4.1.1

# Declare environment variables
ENV PATH=/root/.local/bin:$PATH \
    ACCEPT_EULA=y \
    DEBIAN_FRONTEND=noninteractive \
    TESSDATA_PREFIX=/usr/share/tesseract-ocr/4.00/

# Install system libraries
RUN apt-get update \
  && apt-get install -y --allow-downgrades binutils libmagick++-dev libavfilter-dev cmake cargo libpoppler-cpp-dev \
    libtesseract-dev libleptonica-dev tesseract-ocr-eng gnupg2 unixodbc \
    ## clean up
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/ \
    && rm -rf /tmp/downloaded_packages/ /tmp/*.rds

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/*

# Install R packages
RUN Rscript -e "install.packages(c('RMySQL', 'ragg', 'RMariaDB', 'nloptr', 'tidyverse', 'lme4', 'phyr', 'odbc',  \
    'MEMSS', 'mlmRev', 'gamm4', 'pbkrtest', 'semEff', 'merDeriv', 'car', 'rr2', 'magick', 'av', 'foreach',  \
    'doParallel', 'glue', 'data.table', 'dplyr', 'DBI'), dependencies = TRUE)"

# Set environment variables for ODBC configuration
ENV ODBCSYSINI /etc

# Copy your odbcinst.ini configuration file to the container
COPY odbcinst.ini /etc/odbcinst.ini

COPY . .

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

library("odbc")
library("DBI")
con <- odbc::dbConnect(odbc::odbc(),
                        Driver ="ODBC Driver 17 for SQL Server",
                        Server ="serverName",
                        Database ="DBNAME",
                        UID ="user",
                        PWD ="password", Port = 1433)

test <- odbc::dbGetQuery(con, "select top 10 * from table;")
test

I have confirmed my credentials for connecting to the database are correct. However, when attempting to query, I am getting the below error.

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1' : file not found 
Execution halted

I've tried other variations of installing the ODBC drivers, and I believe they should be installed in rocker/tinyverse already to begin with. Does anyone have any ideas on getting this so I can query the db? Below is my odbcinst.ini file which also could be contributing to the issue:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
UsageCount=1

[cboettig]

tidyverse already has unixodbc installed. You seem to be trying to use a Microsoft driver instead,

 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 \

Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1

I'm guessing this is the issue.

[1ct0]

Thanks -- you were correct. In case anyone has a similar issue, my dockerfile after implementing the change is below:

FROM rocker/tidyverse:4.1.1

# Declare environment variables
ENV PATH=/root/.local/bin:$PATH \
    ACCEPT_EULA=y \
    DEBIAN_FRONTEND=noninteractive \
    TESSDATA_PREFIX=/usr/share/tesseract-ocr/4.00/

# Install system libraries
RUN apt-get update \
  && apt-get install -y --allow-downgrades binutils libmagick++-dev libavfilter-dev cmake cargo libpoppler-cpp-dev \
    libtesseract-dev libleptonica-dev tesseract-ocr-eng gnupg2 curl gnupg apt-transport-https tdsodbc

# Install R packages
RUN Rscript -e "install.packages(c('RMySQL', 'ragg', 'RMariaDB', 'nloptr', 'tidyverse', 'lme4', 'phyr', 'odbc',  \
    'MEMSS', 'mlmRev', 'gamm4', 'pbkrtest', 'semEff', 'merDeriv', 'car', 'rr2', 'magick', 'av', 'foreach',  \
    'doParallel', 'glue', 'data.table', 'dplyr', 'DBI'), dependencies = TRUE)"

# Set environment variables for ODBC configuration
ENV ODBCSYSINI /etc

# Copy your odbcinst.ini configuration file to the container
COPY odbcinst.ini /etc/odbcinst.ini

COPY . .

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

And below is my odbcinst.ini file:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount=1

I added an installation for tdsodbc in my second dockerfile, removed Microsoft's odbc driver, and updated the ini file to reflect the change in driver.