Docker部署R Shiny应用启动失败，求ODBC驱动配置及Dockerfile修改方案

Hey there! It looks like your Dockerized Shiny app is failing because you're missing the actual SQL Server ODBC driver—you've installed unixodbc-dev (the base ODBC development files), but not the specific driver that lets R connect to SQL Server. Let's fix your Dockerfile step by step, plus cover a few other gotchas to get your app running smoothly in a container.

Updated Dockerfile #

# Base image from rocker project
FROM rocker/shiny:latest

# Add Microsoft's official repository for SQL Server ODBC driver
RUN apt-get update -qq && \
    apt-get install -y --no-install-recommends gnupg2 curl && \
    # Import Microsoft GPG key to verify package integrity
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    # Add SQL Server repository for Debian (matches rocker/shiny's base OS)
    curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list

# Install system dependencies + SQL Server ODBC Driver 17
RUN apt-get update -qq && apt-get -y --no-install-recommends install \
    libxml2-dev \
    libcairo2-dev \
    libsqlite3-dev \
    libmariadbd-dev \
    libpq-dev \
    libssh2-1-dev \
    unixodbc-dev \
    libcurl4-openssl-dev \
    libssl-dev \
    # The critical missing component: ODBC Driver 17 for SQL Server
    msodbcsql17

# Clean up apt cache to reduce final image size (good Docker practice)
RUN apt-get clean && rm -rf /var/lib/apt/lists/*

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

# Copy app and config files into the image
COPY shiny-server.conf /etc/shiny-server/shiny-server.conf
COPY /app /srv/shiny-server/

# Expose port 80 for Shiny access
EXPOSE 80

# Copy and make startup script executable (fixes potential permission issues)
COPY shiny-server.sh /usr/bin/shiny-server.sh
RUN chmod +x /usr/bin/shiny-server.sh

CMD ["/usr/bin/shiny-server.sh"]

Key Changes Explained #

• Added SQL Server ODBC Driver: We added the official Microsoft repository and installed msodbcsql17—this is exactly the driver your R code references with Driver = "ODBC Driver 17 for SQL Server".
• Cleaned Up Apt Cache: The apt-get clean step removes temporary package files, keeping your Docker image small.
• Fixed Script Permissions: The chmod +x command ensures your startup script can run (file permissions sometimes get lost when copying files into containers).

Additional Troubleshooting Tips #

1. SQL Server Connection Address: If your SQL Server runs on your local machine (not another container), you can't use localhost inside the container (it refers to the container itself). Use your host machine's IP address, or on Docker Desktop (Windows/macOS), use host.docker.internal to reach the host.
2. Avoid Hardcoded Credentials: Storing UID and PWD directly in server.r is insecure. Use environment variables instead:
   • Update your server.r to pull credentials from env vars:

     conn <- DBI::dbConnect(odbc::odbc(), 
                            Driver = "ODBC Driver 17 for SQL Server",
                            Server = Sys.getenv("DB_SERVER"),
                            Database = Sys.getenv("DB_NAME"),
                            UID = Sys.getenv("DB_USER"),
                            PWD = Sys.getenv("DB_PWD"),
                            port=1433)
     

   • Pass the variables when running the container:

     docker run -e DB_SERVER=your-server-ip -e DB_NAME=your-db -e DB_USER=your-uid -e DB_PWD=your-pwd -p 80:80 your-image-name
     

3. Check Container Logs: If the app still fails, run docker logs <your-container-id> to see exact error messages—this will help debug issues like connection timeouts or missing dependencies.

内容的提问来源于stack exchange，提问作者zesla