r - 无法使用 FreeTDS 连接到 Docker 容器内的 Microsoft SQL Server
问题描述
我想使用 Docker for Windows 在我的公司网络上托管 Shiny 应用程序。
如何设置 Docker、odbc.ini、odbcinst.ini、freetds.conf 或其他可能的文件,以便我的 Shiny 应用程序可以从内部 Microsoft SQL Server (2016) 数据库中查询数据?数据库服务器未在运行 Docker 容器的同一台机器上运行。
我不知道我是否需要更新版本的 FreeTDS,或者我是否错误配置了其中一个文件。我尝试使用服务器的 IP 地址而不是sql-server.host.com
所有文件,但在下面得到相同的错误消息。
$tsql -C
输出:
Compile-time settings (established with the "configure" script)
Version: freetds v1.00.104
freetds.conf directory: /etc/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
OpenSSL: no
GnuTLS: yes
MARS: no
$odbcinst -j
输出:
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
$cat etc/odbcinst.ini
输出:
[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
$cat etc/odbc.ini
输出:
[sql-server]
driver = FreeTDS
server = sql-server.host.com
port = 1433
TDS_Version = 4.2
$cat etc/freetds/freetds.conf
输出:
[sql-server]
host = sql-server.host.com
port = 1433
tds version = 4.2
R中的命令给出错误:
con <- dbConnect(odbc::odbc(),
driver = "FreeTDS",
server = "sql-server.host.com",
port = 1433,
database = "database name",
TDS_Version = 4.2)
错误:
Error: nanodbc/nanodbc.cpp:950: 08001: [FreeTDS][SQL Server]Unable to connect to data source
Execution halted
码头工人文件:
# Install R version 3.5.3
FROM r-base:3.5.3
# Install Ubuntu packages
RUN apt-get update && apt-get install -y \
sudo \
gdebi-core \
pandoc \
pandoc-citeproc \
libcurl4-gnutls-dev \
libcairo2-dev/unstable \
libxt-dev \
libssl-dev \
unixodbc unixodbc-dev \
freetds-bin freetds-dev tdsodbc
# Edit odbc.ini, odbcinst.ini, and freetds.conf files
RUN echo "[sql-server]\n\
host = sql-server.host.com\n\
port = 1433\n\
tds version = 4.2" >> /etc/freetds.conf
RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" >> /etc/odbcinst.ini
RUN echo "[sql-server]\n\
driver = FreeTDS\n\
server = sql-server.host.com\n\
port = 1433\n\
TDS_Version = 4.2" >> /etc/odbc.ini
# Install R packages that are required
RUN R -e "install.packages(c('shiny', 'DBI', 'odbc'), repos='http://cran.rstudio.com/')"
# copy the app to the image
RUN mkdir /root/shiny_example
COPY app /root/shiny_example
COPY Rprofile.site /usr/lib/R/etc/
# Make the ShinyApp available at port 801
EXPOSE 801
CMD ["R", "-e", "shiny::runApp('/root/shiny_example')"]
Docker 构建和运行命令:
docker build . -t shiny_example
docker run -it --network=host -p 801:801 shiny_example
请注意,以下 R 代码适用于我的运行 Docker 容器的 Windows 机器,我可以成功查询数据库:
library(DBI)
con <- dbConnect(odbc::odbc(),
driver = "SQL server",
server = "sql-server.host.com")
$isql -v sql-server
输出:
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect
$tsql -S sql-server
输出:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20013 (severity 2):
Unknown host machine name.
There was a problem connecting to the server
解决方案
看起来你是正确的,但可能错过了一点。我有类似的问题,但我能够解决它!
在python
+ mssqlserver
+ pymssql
+docker(unbuntu16.04 base image)
在没有修复结束的情况下,运行我的代码(使用 pymssql)给了我这个错误
Traceback (most recent call last):
File "<stdin>", line 4, in <module>
File "src/pymssql.pyx", line 645, in pymssql.connect
pymssql.InterfaceError: Connection to the database failed for an unknown reason.
我跟着创建了3个文件并复制到图像中!
myserver.orgName.com
在托管 MSsql 服务器的地方 使用。码头工人/odbcinst.ini
[FreeTDS]
Description = v0.91 with protocol v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
码头工人/odbc.ini
[myserverdsn]
Driver = FreeTDS
Server = myserver.orgName.com
Port = 1433
TDS_Version = 7.3
码头工人/freetds.conf
[global]
# TDS protocol version, use:
# 7.3 for SQL Server 2008 or greater (tested through 2014)
# 7.2 for SQL Server 2005
# 7.1 for SQL Server 2000
# 7.0 for SQL Server 7
tds version = 7.2
port = 1433
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
# A typical Microsoft server
[myserverdsn]
host = myserver.orgName.com
port = 1433
tds version = 7.3
Dockerfile 内容
RUN apt-get -y install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc
COPY freetds.conf /etc/freetds/freetds.conf
COPY odbc.ini /etc/odbc.ini
COPY odbcinst.ini /etc/odbcinst.ini
测试有效的python代码:
python
>>> import pymssql
>>> conn = pymssql.connect(server = 'myserver.orgName.com',
user = 'myusername',
password = 'mypassword',
database= 'mydbname')
工作没有错误!
推荐阅读
- django-import-export - 无法使用 django-import-export 中的 before_import 覆盖功能插入标头
- xcode - React Native OpenTok - 'OpenTok/OpenTok.h' 文件未找到
- powerquery - 电源查询中根据特定逻辑删除行
- c - 带堆栈的 C 计算器程序
- ruby-on-rails - 文章控制器中的 ActiveRecord::PreparedStatementInvalid
- reactjs - 反应生命周期组件WillReceiveProps 如何更改 getDerivedStateFromProps 然后发送 redux 动作
- c# - 为什么我的 if 语句中的比较会影响输出
- google-sheets - 将多行发送到一个基于收件人的名称
- javascript - 问大家一个问题,这是怎么分析的?
- java - 代码荧光笔插入非法字符