Java 如何使 MS SQL Server 可用于连接？

How can I make MS SQL Server available for connections?

提问by Eric Wilson

I'm trying to connect to MS SQL Server (running on my machine) from a Java program. I'm getting the following long winded exception:

我正在尝试从 Java 程序连接到 MS SQL Server（在我的机器上运行）。我收到以下冗长的异常：

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

When I check "Properties" and click "View Connection Properties" in the Object Explorer of MS SQL, I find that the "Server is Unavailable." This seems possibly related to the exception message.

当我在 MS SQL 的对象资源管理器中检查“属性”并单击“查看连接属性”时，我发现“服务器不可用”。这似乎可能与异常消息有关。

How can I make the server available?

如何使服务器可用？

Edit:

编辑：

I am using SQL Server 2008, and I have now enabled TCP/IP, and restarted my instance. I am still told that "Server is unavailable."

我使用的是 SQL Server 2008，我现在启用了 TCP/IP，并重新启动了我的实例。我仍然被告知“服务器不可用”。

Any other ideas?

还有其他想法吗？

回答by Chad

Open up Sql Server Configuration Manager. ( Start | Programs | Whatever version of sql server | Configuration Tools)

打开 Sql Server 配置管理器。（开始 | 程序 | 任何版本的 sql server | 配置工具）

Browse down to 'SQL Server Services' and restart your instance. However you could do this in Management Studio by right clicking on the instance and selecting restart.

向下浏览到“SQL Server 服务”并重新启动您的实例。但是，您可以在 Management Studio 中通过右键单击实例并选择重新启动来执行此操作。

If your restart fails then, check out Computer Management | Event Viewer | Application and look for sql server events. it will record successful and error messages here.

如果重启失败，请查看计算机管理 | 事件查看器 | 应用程序并查找 sql server 事件。它将在此处记录成功和错误消息。

回答by Jon Skeet

Depending on the version of SQL Server you're using, it may well be set up only to use named pipes by default.

根据您使用的 SQL Server 版本，它很可能被设置为默认情况下仅使用命名管道。

You need to enable TCP/IP connections to the server. If you can tell us which version of SQL server you're using, we can give more precise instructions for how to enable TCP/IP.

您需要启用与服务器的 TCP/IP 连接。如果您能告诉我们您使用的是哪个版本的 SQL 服务器，我们可以提供有关如何启用 TCP/IP 的更准确的说明。

回答by John Sansom

Before messing about with connections, first check that the SQL Server Service is actually running.

在处理连接之前，首先检查 SQL Server 服务是否实际运行。

You can do this by either using the SQL Server Configuration Manager (located in the configuration tools folder) or in the standard services console in the Windows control panel.

您可以使用 SQL Server 配置管理器（位于配置工具文件夹中）或在 Windows 控制面板的标准服务控制台中执行此操作。

Once you have checked the service is up and running, you need to ensure that SQL Server has been configured to allow remote connections.

检查服务是否已启动并正在运行后，您需要确保 SQL Server 已配置为允许远程连接。

See below for an explanation on how to do this:

有关如何执行此操作的说明，请参见下文：

http://support.microsoft.com/kb/914277

http://support.microsoft.com/kb/914277

回答by Anthony

First off, check that the sql server service is running. If you're using SQL 2005 or 2008, check Configuration manager (2008) or Surface are configuration tool (2005) to make sure the TCP/IP protocol is enabled and TCP/IP connections are allowed. With SSE(express) these are off by default, which would cause your problem. Also just in case you're running multiple instances, you may need SQL browser service running. If this is the case, you should be able to connect object explorer by using (local) as the server address, since this will use a local/shared memory connection.

首先，检查 sql server 服务是否正在运行。如果您使用的是 SQL 2005 或 2008，请检查 Configuration manager (2008) 或 Surface are configuration tool (2005) 以确保启用 TCP/IP 协议并允许 TCP/IP 连接。使用 SSE(express) 这些默认情况下是关闭的，这会导致您的问题。另外，以防万一您正在运行多个实例，您可能需要运行 SQL 浏览器服务。如果是这种情况，您应该能够通过使用 (local) 作为服务器地址来连接对象资源管理器，因为这将使用本地/共享内存连接。

回答by Bruce

As usual, check the obvious: 1. See if your router has ports 1433 and 1434 enabled (these are the defaults for IP/UDP communication) 2. Ensure that if you have a firewall in place on either the client or the server, that said ports are not blocked (this is the one that was my "gotcha"). 3. Go into Sql Configuration Manager and under Sql Server Network Configuration, ensure that TCP/IP is enabled. Also, in the same program under Sql Native Client 1.0 (if installed) that it, too has TCP/IP enabled.

像往常一样，检查明显的： 1. 查看您的路由器是否启用了端口 1433 和 1434（这些是 IP/UDP 通信的默认值） 2. 确保如果您在客户端或服务器上安装了防火墙，所说的端口没有被阻止（这是我的“陷阱”）。3. 进入 Sql 配置管理器，在 Sql Server 网络配置下，确保启用了 TCP/IP。此外，在 Sql Native Client 1.0（如果已安装）下的同一程序中，它也启用了 TCP/IP。

A cheap and sleazy way to ensure that all of your connection information is working is to create a .udl file (anywhere on your system), fill in the appropriate information and hit "Test Connection". If there is something wrong there, you'll get a (fairly) informative message as to the problem. If you don't know about .udl files, right click in Windows explorer (right side pane) to create a "New Text File". Type any acceptable name but change the extension from .txt to .udl. You will be asked to confirm the extension change, answer yes. Once created, click the file (or double click if you don't use single click) to open it. There are 4 tabs. The first allows you to select the provider. For Visual studio use the Sql Native client, for any other program try the standard Sql Server Provider. On the second tab you can fill in the server name, sql server user name and password (if used), etc. Then test the connection. One other item to note - if you are using Windows Authentication, ensure that those users have been added to the Server Logins.

确保所有连接信息都有效的一种廉价而廉价的方法是创建一个 .udl 文件（系统上的任何位置），填写适当的信息并点击“测试连接”。如果那里有问题，您将收到有关问题的（相当）信息丰富的消息。如果您不了解 .udl 文件，请在 Windows 资源管理器（右侧窗格）中右键单击以创建“新文本文件”。键入任何可接受的名称，但将扩展名从 .txt 更改为 .udl。您将被要求确认扩展名更改，回答是。创建后，单击文件（如果不使用单击，则双击）将其打开。有 4 个选项卡。第一个允许您选择提供者。对于 Visual Studio，请使用 Sql Native 客户端，对于任何其他程序，请尝试使用标准的 Sql Server Provider。在第二个选项卡上可以填写服务器名称、sql server 用户名和密码（如果使用）等，然后测试连接。需要注意的另一项 - 如果您使用的是 Windows 身份验证，请确保这些用户已添加到服务器登录名中。

回答by Julian de Wit

From 2005 and up the SQL server browserservice has te be running.
That one fooled me many times.

从 2005 年起，SQL 服务器浏览器服务开始运行。
那个骗了我很多次。

回答by ptg

I ran into this problem as well. The MSKB article applies to SQL server 2005.

我也遇到了这个问题。MSKB 文章适用于 SQL Server 2005。

As the "SQL Server Surface Area Configuration" tool has been dropped in lieu of "Facets" - this wasn't obvious to me.

由于“SQL Server Surface Area Configuration”工具已被删除以代替“Facets” - 这对我来说并不明显。

I resolved this by setting the TCPAll port and enabling the relevant IP.

我通过设置 TCPAll 端口并启用相关 IP 解决了这个问题。

---

---

Steps

脚步

1. Open the Sql Server Configuration Manager (Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools)

2. Expand SQL Server Network Configuration -> [Your Server Instance]

3. Double click TCP/IP

   • Under Protocol:

     Ensure Enabledis Yes

   • Under IP Addresses:

     Scroll to the bottom and set the TCP Port under IPAll, (1433 by default)

     Find the IP address you want to connect to and set Enabledand Activeto Yes

1. 打开 Sql Server 配置管理器（开始 -> 程序 -> Microsoft SQL Server 2008 -> 配置工具）

2. 展开 SQL Server 网络配置 -> [您的服务器实例]

3. 双击 TCP/IP

   • 根据协议：

     确保启用为是

   • 在IP 地址下：

     滚动到底部并在 IPAll 下设置 TCP 端口，（默认为 1433）

     找到您要连接的 IP 地址并将启用和活动设置为是

回答by Amin

I am using SQL Server 2012 and have had same issues.

我正在使用 SQL Server 2012 并且遇到了同样的问题。

Please note when you activate TCP/IP there are 2 options:

请注意，当您激活 TCP/IP 时，有 2 个选项：

• Active - YES- If you just click yes on this it wont work
• Enabled- click yes on this too. I did not pay attention to this and was wondering why its was not working, wasting a lot of time in the process

• Active - YES- 如果你只是点击是，它不会工作
• Enabled- 也点击是。我没有注意这一点，想知道为什么它不起作用，在这个过程中浪费了很多时间

You can test listening ports with this:

您可以使用以下方法测试侦听端口：

netstat -an

回答by Gene

Here are my screenshots. If you can't read the words, then download the image or copy and paste the image into Paint.

这是我的截图。如果您看不懂文字，请下载图像或将图像复制并粘贴到画图中。

Inside Glassfish:

玻璃鱼内部：

"General" Tab:

“常规选项卡：

"Additional Properties" Tab

“其他属性”选项卡

URL= jdbc:sqlserver://localhost;databaseName=NETEAV

URL= jdbc:sqlserver://localhost;databaseName=NETEAV

Inside Microsoft SQL Server Management Studio:

Microsoft SQL Server Management Studio 内部：

回答by Jeeveshwar D

Same error observed while connecting to SQL Server 2014.

连接到 SQL Server 2014 时观察到相同的错误。

I logged in the sql server to ensure that the correct port number was set.
For my case the Port number was not set for the sql instance.

我登录了 sql server 以确保设置了正确的端口号。
对于我的情况，没有为 sql 实例设置端口号。

Hence I removed the Port number from the connection string url, then I was able to connect .

因此，我从连接字符串 url 中删除了端口号，然后我能够连接 .

Anybody facing this issue, can try this way

任何遇到此问题的人都可以尝试这种方式