连接到 SQL ServerConnecting to SQL Server

下载下载 ODBC 驱动程序DownloadDownload ODBC Driver

本主题讨论如何创建与 SQL ServerSQL Server 数据库的连接。This topic discusses how you can create a connection to a SQL ServerSQL Server database.

连接属性Connection Properties

有关 Linux 和 Mac 上支持的所有连接字符串关键字和属性, 请参阅DSN 和连接字符串关键字和属性See DSN and Connection String Keywords and Attributes for all the connection string keywords and attributes supported on Linux and Mac

重要

当连接到使用数据库镜像(有一个故障转移伙伴)的数据库时,不要在连接字符串中指定数据库名称。When connecting to a database that uses database mirroring (has a failover partner), do not specify the database name in the connection string. 应发送 use database_name 命令连接到该数据库,然后再执行查询 。Instead, send a use database_name command to connect to the database before executing your queries.

传递给Driver关键字的值可以是下列值之一:The value passed to the Driver keyword can be one of the following:

  • 安装该驱动程序时使用的名称。The name you used when you installed the driver.

  • 已在用于安装驱动程序的模板 .ini 文件中指定的该驱动程序库的路径。The path to the driver library, which was specified in the template .ini file used to install the driver.

若要创建 dsn, 请创建 (如有必要) 并编辑文件 ~/.odbc.ini (.odbc.ini在主目录中), 以使用户 DSN 只能供当前用户或/etc/odbc.ini系统 dsn (需要管理权限) 使用。以下示例文件显示了 DSN 所需的条目:To create a DSN, create (if necessary) and edit the file ~/.odbc.ini (.odbc.ini in your home directory) for a User DSN only accessible to the current user, or /etc/odbc.ini for a System DSN (administrative privileges required.) The following is a sample file that shows the minimal required entries for a DSN:

[MSSQLTest]  
Driver = ODBC Driver 13 for SQL Server  
Server = [protocol:]server[,port]  
#   
# Note:  
# Port is not a valid keyword in the odbc.ini file  
# for the Microsoft ODBC driver on Linux or macOS
#  

你可以选择指定协议和端口来连接到服务器。You can optionally specify the protocol and port to connect to the server. 例如, Server = tcp: servername , 12345For example, Server=tcp:servername,12345. 请注意, Linux 和 macOS 驱动程序支持的唯一协议是tcpNote that the only protocol supported by the Linux and macOS drivers is tcp.

若要连接到静态端口上的命名实例,请使用 Server=servername,port_numberTo connect to a named instance on a static port, use Server=servername,port_number. 在 17.4 版之前,不支持连接到动态端口。Connecting to a dynamic port is not supported before version 17.4.

可以选择将 DSN 信息添加到模板文件并执行以下命令,以将其添加到 ~/.odbc.iniAlternatively, you can add the DSN information to a template file, and execute the following command to add it to ~/.odbc.ini :

  • odbcinst -i -s -f template_fileodbcinst -i -s -f template_file

您可以使用isql测试连接来验证您的驱动程序是否正常运行, 也可以使用以下命令:You can verify that your driver is working by using isql to test the connection, or you can use this command:

  • bcp INFORMATION_SCHEMA 输出的输出数据-S -U -Pbcp master.INFORMATION_SCHEMA.TABLES out OutFile.dat -S -U -P

使用安全套接字层 (SSL)Using Secure Sockets Layer (SSL)

可以使用安全套接字层 (SSL) 加密与 SQL ServerSQL Server的连接。You can use Secure Sockets Layer (SSL) to encrypt connections to SQL ServerSQL Server. SSL 通过网络保护 SQL ServerSQL Server 用户名和密码。SSL protects SQL ServerSQL Server user names and passwords over the network. SSL 还会验证服务器的标识以抵御中间人 (MITM) 攻击。SSL also verifies the identity of the server to protect against man-in-the-middle (MITM) attacks.

启用加密可提高安全性,但会降低性能。Enabling encryption increases security at the expense of performance.

有关详细信息, 请参阅加密连接 SQL Server使用未验证的加密For more information, see Encrypting Connections to SQL Server and Using Encryption Without Validation.

无论 EncryptTrustServerCertificate的设置如何,服务器登录凭据(用户名和密码)都始终处于加密状态。Regardless of the settings for Encrypt and TrustServerCertificate, the server login credentials (user name and password) are always encrypted. 下表显示了 EncryptTrustServerCertificate 设置的效果。The following table shows the effect of the Encrypt and TrustServerCertificate settings.

TrustServerCertificate=noTrustServerCertificate=no TrustServerCertificate=yesTrustServerCertificate=yes
Encrypt=noEncrypt=no 不检查服务器证书。Server certificate is not checked.

在客户端和服务器之间发送的数据没有加密。Data sent between client and server is not encrypted.
不检查服务器证书。Server certificate is not checked.

在客户端和服务器之间发送的数据没有加密。Data sent between client and server is not encrypted.
Encrypt=yesEncrypt=yes 检查服务器证书。Server certificate is checked.

在客户端和服务器之间发送的数据已加密。Data sent between client and server is encrypted.

SQL ServerSQL Server SSL 证书中使用者公用名 (CN) 或使用者可选名称 (SAN) 中的名称(或 IP 地址)应与连接字符串中指定的服务器名称(或 IP 地址)完全匹配。The name (or IP address) in a Subject Common Name (CN) or Subject Alternative Name (SAN) in a SQL ServerSQL Server SSL certificate should exactly match the server name (or IP address) specified in the connection string.
不检查服务器证书。Server certificate is not checked.

在客户端和服务器之间发送的数据已加密。Data sent between client and server is encrypted.

默认情况下,加密的连接会始终验证服务器的证书。By default, encrypted connections always verify the server's certificate. 但是, 如果你连接到具有自签名证书的服务器, 还可以添加选项 " TrustServerCertificate对受信任的证书颁发机构列表绕过证书":However, if you connect to a server that has a self-signed certificate, also add the TrustServerCertificate option to bypass checking the certificate against the list of trusted certificate authorities:

Driver={ODBC Driver 13 for SQL Server};Server=ServerNameHere;Encrypt=YES;TrustServerCertificate=YES  

SSL 使用 OpenSSL 库。SSL uses the OpenSSL library. 下表显示了 OpenSSL 的受支持的最低版本和每个平台的默认证书信任存储位置:The following table shows the minimum supported versions of OpenSSL and the default Certificate Trust Store locations for each platform:

平台Platform 最低的 OpenSSL 版本Minimum OpenSSL Version 默认证书信任存储位置Default Certificate Trust Store Location
Debian 10Debian 10 1.1.11.1.1 /etc/ssl/certs/etc/ssl/certs
Debian 9Debian 9 1.1.01.1.0 /etc/ssl/certs/etc/ssl/certs
Debian 8.71Debian 8.71 1.0.11.0.1 /etc/ssl/certs/etc/ssl/certs
OS X 10.11、macOS 10.12、10.13、10.14OS X 10.11, macOS 10.12, 10.13, 10.14 1.0.21.0.2 /usr/local/etc/openssl/certs/usr/local/etc/openssl/certs
Red Hat Enterprise Linux 8Red Hat Enterprise Linux 8 1.1.11.1.1 /etc/pki/tls/cert.pem/etc/pki/tls/cert.pem
Red Hat Enterprise Linux 7Red Hat Enterprise Linux 7 1.0.11.0.1 /etc/pki/tls/cert.pem/etc/pki/tls/cert.pem
Red Hat Enterprise Linux 6Red Hat Enterprise Linux 6 1.0.0-101.0.0-10 /etc/pki/tls/cert.pem/etc/pki/tls/cert.pem
SuSE Linux Enterprise 15SuSE Linux Enterprise 15 1.1.01.1.0 /etc/ssl/certs/etc/ssl/certs
SuSE Linux Enterprise 11、12SuSE Linux Enterprise 11, 12 1.0.11.0.1 /etc/ssl/certs/etc/ssl/certs
Ubuntu 18.10、19.04Ubuntu 18.10, 19.04 1.1.11.1.1 /etc/ssl/certs/etc/ssl/certs
Ubuntu 18.04Ubuntu 18.04 1.1.01.1.0 /etc/ssl/certs/etc/ssl/certs
Ubuntu 16.04、16.10、17.10Ubuntu 16.04, 16.10, 17.10 1.0.21.0.2 /etc/ssl/certs/etc/ssl/certs
Ubuntu 14.04Ubuntu 14.04 1.0.11.0.1 /etc/ssl/certs/etc/ssl/certs

你还可以在使用Encrypt SQLDriverConnect进行连接时使用选项在连接字符串中指定加密。You can also specify encryption in the connection string using the Encrypt option when using SQLDriverConnect to connect.

调整 TCP Keep-alive 设置Adjusting the TCP Keep-Alive Settings

从 ODBC 驱动程序17.4 开始, 驱动程序发送保持活动状态的数据包并在未收到响应时对它们进行重新传输的频率是可配置的。Starting in ODBC Driver 17.4, how often the driver sends keep-alive packets and retransmits them when a response is not received is configurable. 若要配置, 请将以下设置添加到中odbcinst.ini的驱动程序部分, 或添加到中odbc.ini的 DSN 部分。To configure, add the following settings to either the driver's section in odbcinst.ini, or the DSN's section in odbc.ini. 使用 DSN 进行连接时, 驱动程序将使用 DSN 的部分中的设置 (如果有);否则, 或者, 如果只使用连接字符串进行连接, 它将使用的驱动程序部分中odbcinst.ini的设置。When connecting with a DSN, the driver will use the settings in the DSN's section if present; otherwise, or if connecting with a connection string only, it will use the settings in the driver's section in odbcinst.ini. 如果这两个位置中不存在该设置, 则驱动程序将使用默认值。If the setting is not present in either location, the driver uses the default value.

  • KeepAlive=<integer>控制 TCP 通过发送 keep-alive 数据包来尝试验证空闲连接是否仍保持不变的频率。KeepAlive=<integer> controls how often TCP attempts to verify that an idle connection is still intact by sending a keep-alive packet. 默认值为 30 秒 。The default is 30 seconds.

  • KeepAliveInterval=<integer>确定在收到响应之前分隔保持活动传输的时间间隔。KeepAliveInterval=<integer> determines the interval separating keep-alive retransmissions until a response is received. 默认值为 1 秒。The default is 1 second.

另请参阅See Also

在 Linux 和 macOS 上安装 Microsoft ODBC Driver for SQL ServerInstalling the Microsoft ODBC Driver for SQL Server on Linux and macOS
编程指南Programming Guidelines