In Amazon Linux 2023.6 (AWS), I run a Docker container based on Ubuntu 20.04 that connects to SQL Server using sqlcmd and odbc::dbConnect() in R.
When I open an interactive shell inside the container and run the following, it works fine and retrieves results
[ec2-user@shinyanalytics InrixSpeedModule]$ sudo docker run --rm -it inrixspeedmodule /bin/bash
root@13a72816ef75:/# sqlcmd -S 150.135.237.9 -U REDACTED -P REDACTED -d REDACTED -Q "SELECT TOP 1 * FROM REDACTED"
timestamp SegmentID type speed average reference score confidenceValue travelTimeMinutes speedBucket segment_closed
----------------------- ---------- ---- ----- ------- --------- ----- --------------- ----------------- ----------- --------------
2025-03-18 18:39:12.000 115+04100 TMC 44 59 64 30 100 .63 2 0
(1 rows affected)
However, when I run the container normally, I get this error from R's odbc::dbConnect()
[ec2-user@shinyanalytics InrixSpeedModule]$ sudo docker run -p 80:3838 --rm inrixspeedmodule
R version 4.0.5 (2021-03-31) -- "Shake and Throw"
Copyright (C) 2021 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
Natural language support but running in an English locale
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> shiny::runApp('/root/app')
Loading required package: shiny
Attaching package: ‘shinyjs’
The following object is masked from ‘package:shiny’:
runExample
The following objects are masked from ‘package:methods’:
removeClass, show
System has not been booted with systemd as init system (PID 1). Can't operate.
Failed to create bus connection: Host is down
Warning in system("timedatectl", intern = TRUE) :
running command 'timedatectl' had status 1
Attaching package: ‘lubridate’
The following objects are masked from ‘package:base’:
date, intersect, setdiff, union
Attaching package: ‘data.table’
The following objects are masked from ‘package:lubridate’:
hour, isoweek, mday, minute, month, quarter, second, wday, week,
yday, year
Loading required package: lattice
Attaching package: ‘BSDA’
The following object is masked from ‘package:datasets’:
Orange
Attaching package: ‘shinydashboard’
The following object is masked from ‘package:graphics’:
box
Loading required package: ggplot2
Attaching package: ‘plotly’
The following object is masked from ‘package:ggplot2’:
last_plot
The following object is masked from ‘package:stats’:
filter
The following object is masked from ‘package:graphics’:
layout
Attaching package: ‘shinyWidgets’
The following object is masked from ‘package:shinyjs’:
alert
Attaching package: ‘DT’
The following objects are masked from ‘package:shiny’:
dataTableOutput, renderDataTable
Error in odbc_connect(connection_string, timezone = timezone, timezone_out = timezone_out, :
Evaluation error: ! ODBC failed with error 00000 from [Microsoft][ODBC Driver 17 for SQL
Server].
✖ Login timeout expired
• TCP Provider: Error code 0x2AFA
• A network-related or instance-specific error has occurred while establishing
a connection to SQL Server. Server is not found or not accessible. Check if
instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online.
ℹ From 'nanodbc/nanodbc.cpp:1150'..
Calls: <Anonymous> ... OdbcConnection -> withCallingHandlers -> odbc_connect
Execution halted
The Database connection function is as follows:
getSQLConnection <- function(server = "STL5") {
systemType <- Sys.info()
hostOS <- systemType['sysname']
# SQL CONNECTION PARAMETERS
UserName <- "REDACTED"
Password <- "REDACTED"
Database <- "REDACTED"
dsnName <- "150.135.237.9,1433"
if (server == "STL5") {
if (hostOS == "Windows") {
SQLdriver <- "{SQL Server}"
} else {
SQLdriver <- "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.so"
}
} else {
stop("Unknown Server")
}
# Establish a connection using DBI + odbc
conn <- DBI::dbConnect(odbc::odbc(),
Driver = "ODBC Driver 17 for SQL Server",
Server = dsnName,
Database = Database,
UID = UserName,
PWD = Password,
Encrypt = "no",
TrustServerCertificate = "Yes",
timeout = 60)
# Check if the connection was successful
if(dbIsValid(conn)){
cat("Connection successful!\n")
} else {
cat("Connection failed!\n")
}
return(conn)
}
and this is how I use it in my R Shiny application to establish connection and retrieve data:
conn_STL5 <- getSQLConnection('STL5')
Segments <- DBI::dbGetQuery(conn_STL5, paste("SELECT * FROM ", getINRIX_SegmentsTable(), sep = ""))
My Dockerfile is as follows:
FROM acottam1/basemodule
LABEL version="1.0"
# Set a different CRAN mirror for R packages
RUN echo 'options(repos = c(CRAN = "/"))' > /usr/lib/R/etc/Rprofile.site
# Update package lists and install necessary dependencies
RUN apt-get update && apt-get install -y \
curl \
gnupg2 \
unixodbc \
unixodbc-dev \
tar \
gzip \
gcc \
g++ \
make \
libxml2-dev \
libcurl4-openssl-dev \
libssl-dev \
&& apt-get clean
# Add Microsoft repository for ODBC Driver and install SQL Server tools
RUN curl .asc | apt-key add - && \
curl .04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list && \
apt-get update && \
ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools && \
apt-get clean
# Add sqlcmd to PATH
ENV PATH="${PATH}:/opt/mssql-tools/bin"
# Verify ODBC Driver installation
RUN ls -lah /opt/microsoft/msodbcsql17/lib64/
# Create a symlink for the ODBC driver dynamically
RUN if [ ! -f /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.so ]; then \
ln -s $(ls /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-*.so* | head -n 1) /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.so; \
fi
# Create app directory
RUN mkdir -p /root/app
COPY app /root/app
# Install required R packages
RUN R -e "install.packages(c('odbc', 'DBI'))"
# Verify R package installation
RUN R -e "library(odbc); library(DBI); cat('R packages installed successfully\n')"
# Expose Shiny port
EXPOSE 3838
# Start Shiny App
CMD ["R", "-e", "shiny::runApp('/root/app')"]
Why does sqlcmd work when I open a shell inside the container, but odbc::dbConnect() fails when running normally?
How can I make the container connect to the database without needing an interactive shell? What changes should I make to ensure the ODBC driver and environment variables are properly recognized when the container runs normally?