运行Synthea ETL报SSL连接错误但DQD正常运行的问题排查

使用指定连接参数可正常运行DQD，但调用connect()方法建立数据库连接时触发SSL错误，运行Synthea ETL工具时出现完全相同的报错。以下为完整运行脚本。

连接参数 #

dbms <- "sql server"
user <- "synthea_user" 
password <- "sneeker" 
server <- "localhost" 
port <- "1433"
pathToDriver <- pathToDriver
extraSettings <- ";databaseName=master;integratedSecurity=true;encrypt=false"

报错信息 #

Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect",  : 
  com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:1799f03c-5ddf-4498-9670-6fcf87f557ac

完整运行脚本 #

# local files
pathToDriver <- "D:\NACHC\SYNTHEA\DQD\resources\jar\sqlserver-jar"  # location of the mssql-jdbc-10.2.0.jre8.jar
outputFolder <- "D:\NACHC\SYNTHEA\DQD\output"                         # location where output file will be written
outputFile <- "results.json"                                              # file for results json

# database connectivity
dbms <- "sql server"
user <- "synthea_user" 
password <- "sneeker" 
server <- "localhost" 
port <- "1433"
pathToDriver <- pathToDriver
extraSettings <- ";databaseName=master;integratedSecurity=true;encrypt=false"

# database schemas
cdmDatabaseSchema <- "synthea_micro.dbo"                       # your omop instance
resultsDatabaseSchema <- "synthea_micro_dqd_results.dbo"       # instance where results will be written
cdmSourceName <- "SYNTHEA_MICRO_Test_Database"                 # a human readable name for your CDM source

# config parameters
numThreads <- 1       # number of threads to run, 3 seems to work well on Redshift
sqlOnly <- FALSE      # set to TRUE if you just want to get the SQL scripts and not actually run the queries
verboseMode <- TRUE   # set to TRUE if you want to see activity written to the console
writeToTable <- TRUE  # set to FALSE if you want to skip writing to a SQL table in the results schema

# dqd parameters
checkLevels <- c("TABLE", "FIELD", "CONCEPT")  # which DQ check levels to run
checkNames <- c()                              # which DQ checks to run, names can be found in inst/csv/OMOP_CDM_v5.3.1_Check_Desciptions.csv
tablesToExclude <- c()                         # which CDM tables to exclude?


# ---
#
# run-dqd.r
#
# Run the init-parameters.r script before running this script.   
#
# Script to run the Data Quality Dashboard (DQD)
# 
# The output of this script is a single JSON file that can then be rendered with the run-dqd.r script 
# to view the results as a web page in a browser.  
# 
# Prior to running this script you will need to download and install the jdbc driver For MS Sql Server.  
# This is entered as the pathToDriver variable below.  
#
# ---

# ---
# 
# CREATE THE CONNECTION OBJECT AND RUN THE JOB
# 
# ---

# create connection details object
connectionDetails <- DatabaseConnector::createConnectionDetails(
  dbms = dbms, 
  user = user, 
  password = password, 
  server = server, 
  port = port, 
  pathToDriver = pathToDriver,
  extraSettings = extraSettings 
)


# conn <- connect(cd)
# disconnect(conn)

# (OPTIONAL): if writing to table and using Redshift, bulk loading can be initialized
# Sys.setenv (
#   "AWS_ACCESS_KEY_ID" = "",
#   "AWS_SECRET_ACCESS_KEY" = "",
#   "AWS_DEFAULT_REGION" = "",
#   "AWS_BUCKET_NAME" = "",
#   "AWS_OBJECT_KEY" = "",
#   "AWS_SSE_TYPE" = "AES256",
#   "USE_MPP_BULK_LOAD" = TRUE
# )

# ---
#
# run the job
#
# ---

DataQualityDashboard::executeDqChecks (
  connectionDetails = connectionDetails, 
  cdmDatabaseSchema = cdmDatabaseSchema, 
  resultsDatabaseSchema = resultsDatabaseSchema,
  cdmSourceName = cdmSourceName, 
  numThreads = numThreads,
  sqlOnly = sqlOnly, 
  outputFolder = outputFolder, 
  outputFile = outputFile,
  verboseMode = verboseMode,
  writeToTable = writeToTable,
  checkLevels = checkLevels,
  tablesToExclude = tablesToExclude,
  checkNames = checkNames,
  cdmVersion = "5.4"
)

# (OPTIONAL) inspect logs
# ParallelLogger::launchLogViewer(
#   logFileName = file.path(outputFolder, cdmSourceName, 
#   sprintf("log_DqDashboard_%s.txt", cdmSourceName))
# )

# (OPTIONAL) if you want to write the JSON file to the results table separately
# jsonFilePath <- ""
# DataQualityDashboard::writeJsonResultsToTable(
#   connectionDetails = connectionDetails, 
#   resultsDatabaseSchema = resultsDatabaseSchema, 
#   jsonFilePath = jsonFilePath
# )

print("TRYING TO CONNECT USING connect(cd)")
conn <- connect(cd)
disconnect(conn)

报错核心是mssql-jdbc 10.2版本默认强制SSL加密校验，配置的encrypt=false未正确生效，同时开启integratedSecurity=true走Windows集成认证时，Java运行环境无法信任SQL Server返回的自签名SSL证书，触发证书路径校验失败。按以下步骤修复：

1. 修正连接参数
   已显式传入用户名密码的场景下不需要开启Windows集成认证，去掉integratedSecurity=true，补充跳过服务器证书校验的参数：

   extraSettings <- ";databaseName=master;encrypt=false;trustServerCertificate=true"
   

2. 修复代码变量引用错误
   脚本最后调用connect(cd)时传入的cd对象不存在，之前创建的连接详情对象名为connectionDetails，同时补全包名避免函数找不到：

   print("TRYING TO CONNECT USING connect(connectionDetails)")
   conn <- DatabaseConnector::connect(connectionDetails)
   DatabaseConnector::disconnect(conn)
   

3. 修正Windows路径转义
   R语言中单斜杠\是转义符，脚本里的本地路径全部替换为双反斜杠：

   pathToDriver <- "D:\\NACHC\\SYNTHEA\\DQD\\resources\\jar\\sqlserver-jar"
   outputFolder <- "D:\\NACHC\\SYNTHEA\\DQD\\output"
   

4. 以上修改后仍报错的话，直接将JDBC驱动降级到9.2.0.jre8版本，替换当前路径下的10.2版本驱动即可，该版本不会默认强制SSL证书校验。

先单独运行连接测试代码，确认连接正常后再跑DQD和ETL任务，减少无效等待时间。

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