尝试使用sqlcmd（从Ubuntu命令行）连接到SQL Server时出错？ [英] Error trying to connect to a SQL Server using sqlcmd (from Ubuntu command line)?

问题描述

我发现尝试使用 sqlcmd 从Ubuntu计算机连接到 SQL Server 实例时遇到一些问题。

I am finding some problem trying to connect to a SQL Server instance from an Ubuntu machine using sqlcmd.

所以我按照以下说明安装了 sqlcmd ： https://docs.microsoft.com/it-it/sql/linux/sql-server-linux-setup-tools?view=sql-server -ver15＃ubuntu

So I installed sqlcmd as explained here: https://docs.microsoft.com/it-it/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15#ubuntu

SQL Server似乎可以访问，因为我可以通过Telnet成功连接到其IP的1433端口。

The SQL server seems to be reachable because I can successfully connect to the 1433 port of its IP via Telnet.

然后我尝试以这种方式连接到此服务器上的数据库之一：

Then I tried to connect to one of my database on this server, in this way:

./sqlcmd -S MY_SERVER_IP\ESB_WSO2_USER_DB

MY_SERVER_IP 在哪里该服务器的IP和 ESB_WSO2_USER_DB 是此服务器上数据库的名称。

Where MY_SERVER_IP is the IP of this server and ESB_WSO2_USER_DB is the name of a database on this server.

问题是我正在获取以下消息ge错误作为输出：

The problem is that I am obtaining the following message error as output:

Sqlcmd：错误：SQL Server的Microsoft ODBC驱动程序17：登录超时
到期。
Sqlcmd：错误：SQL Server
的Microsoft ODBC驱动程序17：TCP提供程序：错误代码0x2AFA。
Sqlcmd：错误：SQL Server的Microsoft ODBC
驱动程序17：与网络相关的实例建立与SQL Server的连接时，发生了特定于
的错误。
服务器未找到或无法访问。检查实例名称是否正确
以及SQL Server是否配置为允许远程连接。
有关更多信息，请参见SQL Server联机丛书。

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AFA.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

那么可能是什么问题？它可能与我的 sqlcmd 安装有关（也许有些配置错误）还是与我的连接字符串有关？还是与 SQL Server 数据库相关的内容？

So what could be the problem? Is it something that could be related to my sqlcmd installation (maybe some missconfiguration) or something related to my connection string? or something related to the SQL Server database?

该消息表明，也许SQL Server可能不允许外部连接，但是...如果可以，为什么Telnet连接可以正常工作？

The message says that maybe the SQL Server maybe is not allowing external connection but...if so...why Telnet connection works fine?

推荐答案

-S MY_SERVER_IP\ESB_WSO2_USER_DB 表示服务器的IP，实例的名称。 Windows主机可以运行多个SQL Server实例，并且当它们运行时，需要为后一个实例赋予与默认实例名称不同的名称（ MSSQLSERVER ）并在另一个实例上运行港口。因此，您可以在以后指定名称，而不是端口（如果您正在运行命名服务器）。例如， SRVSQLHost\DevInstance 将连接到服务器 SRVSQLHost DevInstance 。 code>; 不是主机上实例 MSSQLSERVER 上的数据库 DevInstance SRVSQLHost 。

-S MY_SERVER_IP\ESB_WSO2_USER_DB means the IP of the server, and the name of the instance. Windows Hosts can run multiple instances of SQL Server, and when they do, latter instances need to be given a different name to the default instance name (MSSQLSERVER) and run on a different port. As a result you can specify the name afterwards, rather than the port (if you have named servers running). For example SRVSQLHost\DevInstance would connect the to instance DevInstance on the server SRVSQLHost; not the database DevInstance on the instance MSSQLSERVER on the host SRVSQLHost.

您可以使用 -d 开关传递数据库名称。如果仅运行 sqlcmd （在Bash中，而在Powershell中，则需要使用 sqlcmd-？），您将看到输入开关：

You pass the name of the database using the -d switch. If you run simply sqlcmd (in Bash, not Powershell, where you need to use sqlcmd -?) you'll see the input switches:

:~$ sqlcmd
Microsoft (R) SQL Server Command Line Tool
Version 17.4.0001.1 Linux
Copyright (c) 2012 Microsoft. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided] 
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn] 
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

请注意，Windows Powershell上的某些开关在Bash和Linux的Powershell；

因此，对于您想要的东西：

So, for what you have you want:

sqlcmd -S MY_SERVER_IP -d ESB_WSO2_USER_DB -U YOUR_LOGIN_NAME

（I假设您需要通过 -U 开关，除非您在Ubuntu实例上配置了 kerboros，但是您没有提及它，所以我请注意， -U 的 LOGIN 必须是SQL身份验证登录名，而不是AD登录名。）

(I assume you need to pass the -U switch, unless you have configured kerboros on your Ubuntu instance, but you didn't mention it, so I assumed not. Note that the LOGIN for -U must be a SQL Authentication login, not an AD login.)

这篇关于尝试使用sqlcmd（从Ubuntu命令行）连接到SQL Server时出错？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！