在Mac上使用isql连接到MS SQL Server数据库 [英] Connect to MS SQL Server Database Using isql on Mac
问题描述
摘要:: tsql 已连接，而 isql 未连接.
Summary: tsql connects and isql doesn't.
详细信息:我已经尝试了所有可以找到的官方安装指南和Stack Overflow问题，但没有一个能让人高兴.我正在尝试使用 pyodbc 从我的Mac(Mohave)以及最终的Xubuntu 16.04虚拟机连接到MS SQL Server数据库.在进入Python之前，我只想对连接进行故障排除.问题是，我看到的大多数文档都缺少重要的细节，例如我应该在用户名和密码中使用引号吗?".或者，我应该在用户名中包含域吗?"如果我应该在域中添加用户名，我是否应该转义反斜杠字符?"
Details: I have tried every official installation guide and Stack Overflow question I can find, but none of them produce joy. I'm trying to connect to a MS SQL Server database from my Mac (Mohave), and ultimately a Xubuntu 16.04 virtual machine as well, using pyodbc. Before getting into the Python, I just want to trouble-shoot the connection. The problem is, most of the documentation I'm seeing is missing important details such as, "Should I use quotes in usernames and passwords?" Or, "Should I include the domain in the username?" And if I should include the domain the username, "Should I escape the backslash character?"
这是我当前的设置: tsql -C 产生
Here is my current setup: tsql -C produces
Compile-time settings (established with the "configure" script)
Version: freetds v1.1.11
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 7.3
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
OpenSSL: yes
GnuTLS: no
MARS: yes
/usr/local/etc/freetds.conf 的内容是:
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = auto
# 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
# To reduce data sent from server for BLOBs (like TEXT or
# IMAGE) try setting 'text size' to a reasonable limit
; text size = 64512
# If you experience TLS handshake errors and are using openssl,
# try adjusting the cipher list (don't surround in double or single quotes)
# openssl ciphers = HIGH:!SSLv2:!aNULL:-DH
# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0
# A typical Microsoft server
[egServer73]
host = ntmachine.domain.com
port = 1433
tds version = 7.3
[my_server]
host = my_server
port = 1433
tds version = 7.3
my_server 是我要连接的服务器.命令 tsql -S my_server -U domain \\ username -P password ，在任何地方都没有引号，可以很好地连接，并显示结果
The my_server is the server to which I'm trying to connect.
The command tsql -S my_server -U domain\\username -P password, with no quotes anywhere, connects just fine, with the result
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
FreeTDS太多了.对于 ODBC 方面，首先要注意 FreeTDS 显然已经为 ODBC 设置，因为它在 tsql -C 的结果中说 unixodbc: yes.我的/etc/odbcinst.ini 文件包含:
So much for FreeTDS. For the ODBC side, first note that FreeTDS is apparently already set up for ODBC, as it says unixodbc: yes in the results of tsql -C. My /etc/odbcinst.ini file contains:
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
我的/etc/odbc.ini 文件包含
[my_server]
Description = "The Location of the EOAM_RA Database"
Driver = FREETDS
Trace = Yes
TraceFile = /tmp/sql.log
Database = my_database
Servername = my_server
UserName = domain\\username
Password = password
Port = 1433
Protocol = 7.3
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
但是，当我尝试使用任何版本的 isql -v my_server用户名密码时，我都会感到恐惧，而且无济于事
But when I try any version of isql -v my_server username password, I get the dreaded and incredibly unhelpful
[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
因此，尽管我当然希望为所有设置都具有正确的设置，以便我可以通过 isql 并最终通过 pyodbc 进行连接，但如果可以的话，这也将大有帮助有以下问题的答案:
So, while I certainly would LOVE to have the right settings for everything so that I can connect via isql and ultimately pyodbc, it would also help greatly if I could have the answers to the following questions:
- 在每个应该输入用户名的地方，都应该在引号中加上引号吗?
- 对于应该在其中输入用户名的每个地方，我都应该在域名前加上前缀吗?
- 对于需要输入用户名的每个地方，如果我确实需要包括我的域，是否应该在中间输入反斜杠?
- 对于服务器，我应该加引号吗?
非常感谢您的光临！
推荐答案
在freetds.conf文件中为SQL Server使用完全限定域名(fqdn)之后，您是否尝试过连接:
Have you tried connecting after using the fully qualified domain name (fqdn) for your SQL Server in the freetds.conf file:
[my_server]
host = my_server.<yourdomain.example>
port = 1433
tds version = 7.3
这篇关于在Mac上使用isql连接到MS SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
