Docker中Jupyter R Notebook连接SQL Server遇证书及连接串错误

我在Docker中运行来自jupyter/docker-stacks的datascience-notebook独立镜像，需要通过R和Python Notebook访问Microsoft SQL Server数据库。已通过编辑Dockerfile扩展标准镜像，添加数据库访问相关配置：

FROM jupyter/datascience-notebook:85f615d5cafa
# Install Python libraries from requirements.txt file
COPY --chown=${NB_UID}:${NB_GID} requirements.txt /tmp/
RUN pip install --quiet --no-cache-dir --requirement /tmp/requirements.txt && \
    fix-permissions "${CONDA_DIR}" && \
    fix-permissions "/home/${NB_USER}"

USER root

RUN apt-get update && apt-get install -y --no-install-recommends apt-utils

RUN export DEBIAN_FRONTEND=noninteractive && \
    apt-get update && \
    apt-get install --yes --no-install-recommends procps ca-certificates \
    postgresql postgresql-contrib libpq-dev gnupg
# openssh-server

# MS SQL Server requirements
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN export DEBIAN_FRONTEND=noninteractive && \
    export ACCEPT_EULA=Y && \
    apt-get update && \
    apt-get install --yes --no-install-recommends unixodbc-dev msodbcsql18 mssql-tools18

ENV PATH="$PATH:/opt/mssql-tools18/bin"

Python连接正常情况 #

通过Python Notebook使用pyodbc和sqlalchemy可以正常连接，确认凭据正确：

import pyodbc
from sqlalchemy import create_engine
import os

url = "mssql+pyodbc://{}:{}@{}:{}/{}?{}&{}".format(
    os.getenv('CABOODLE_DB_USER'),
    os.getenv('CABOODLE_DB_PASSWORD'),
    os.getenv('CABOODLE_DB_HOST'),
    os.getenv("CABOODLE_DB_PORT"),
    os.getenv("CABOODLE_DB_NAME"),
    "driver=ODBC+Driver+18+for+SQL+Server",
    "TrustServerCertificate=yes",
)
engine = create_engine(url, pool_size=1, max_overflow=1)
engine

R连接失败问题 #

但R版本无法正常工作，使用以下语法（已尝试硬编码环境变量值）：

db <- DBI::dbConnect(odbc::odbc(),
                 driver = 'ODBC Driver 18 for SQL Server',
                 server = Sys.getenv("CABOODLE_DB_HOST"),
                 database = Sys.getenv("CABOODLE_DB_NAME"),
                 trusted_connection = 'yes',
                 uid = Sys.getenv("CABOODLE_DB_USER"),
                 pwd= Sys.getenv("CABOODLE_DB_PASSWORD"),
                 port = as.integer(Sys.getenv("CABOODLE_DB_PORT"))
                 )

错误信息 #

Error: nanodbc/nanodbc.cpp:1118: 00000: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:16000069:STORE routines::unregistered scheme:scheme=file][error:80000002:system library::No such file or directory:calling stat(/opt/conda/ssl/certs)][error:16000069:STORE routines::unregistered scheme:scheme=file]  [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection  [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute

推测存在两个问题：证书错误、无效连接字符串。

额外参考（Docker外正常连接） #

在Docker环境外，Windows桌面的RStudio通过Windows认证可正常连接该数据库，连接代码如下：

library(odbc)
con <- dbConnect(odbc(), Driver = "SQL Server", Server = Sys.getenv("CABOODLE_DB_HOST"),  Database = Sys.getenv("CABOODLE_DB_NAME"))

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