SQL Server数据库连接错误

Question

我们正在尝试将一个自定义的Java大厅/工作人员管理程序与数据库连接起来。该项目包含多个模块，我们的数据库团队决定使用SQL Server作为其管理基础。

不幸的是，这证明是有问题的，导致了一系列错误，我们完全不知道如何解决。在Stack中搜索后，出现了类似的问题，但要么没有解决，只是自行“开始工作”，或通过远离SQL Server“修复”，而我们也无法这样做。

毫不拖延地，以下是问题本身。该程序可以正确操作所有数据，因为它在“测试模式”下进行了调试，在其中，数值直接被馈送到代码中，而没有使用外部数据库。真正的问题出现在把两个部分结合在一起时。

首先，localhost的连接端口存在问题。使用端口1433，我们甚至未能建立连接，即使解锁端口也没有改变。移动到1434后，我们设法建立了某种程度的连接。

如果需要，以下是有关端口1433的错误块：

The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.j256.ormlite.jdbc.JdbcConnectionSource.makeConnection(JdbcConnectionSource.java:252)
at com.j256.ormlite.jdbc.JdbcConnectionSource.getReadWriteConnection(JdbcConnectionSource.java:184)
at com.j256.ormlite.jdbc.JdbcConnectionSource.getReadOnlyConnection(JdbcConnectionSource.java:170)
at com.j256.ormlite.stmt.StatementExecutor.buildIterator(StatementExecutor.java:243)
at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:197)
at com.j256.ormlite.stmt.StatementExecutor.queryForAll(StatementExecutor.java:118)
at com.j256.ormlite.dao.BaseDaoImpl.queryForAll(BaseDaoImpl.java:239)
at controler.AdminRoomControler.getListRoomEquipmentData(AdminRoomControler.java:51)
at gui.panel.ReservationRoomPanel.reloadData(ReservationRoomPanel.java:57)
at gui.panel.ReservationRoomPanel.initialize(ReservationRoomPanel.java:82)
at gui.panel.ReservationRoomPanel.<init>(ReservationRoomPanel.java:40)
at gui.frame.ApplicationFrame.initialize(ApplicationFrame.java:58)
at gui.frame.ApplicationFrame.<init>(ApplicationFrame.java:37)
at main.Program$1.run(Program.java:22)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

当我们将端口改为1434后，我们遇到了另一个问题。由于某种原因，SQL Server响应我们的连接时出现了Prelogin握手问题，而我们绝对不知道如何解决。错误信息如下：

sty 11, 2014 5:32:02 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 0a8f83b1-ce25-48c1-8796-c2c5c7d080b7 Prelogin error: host localhost port 1434 Error reading prelogin response: Software caused connection abort: recv failed ClientConnectionId:0a8f83b1-ce25-48c1-8796-c2c5c7d080b7
sty 11, 2014 5:32:02 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 982a07f7-0cbd-417f-8bcb-47eebe6b4524 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:982a07f7-0cbd-417f-8bcb-47eebe6b4524
sty 11, 2014 5:32:02 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 9c136851-d172-4b98-8722-16941642d14a Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:9c136851-d172-4b98-8722-16941642d14a
sty 11, 2014 5:32:03 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: bc3db9f2-6d56-43bd-9145-051dc418d8b9 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:bc3db9f2-6d56-43bd-9145-051dc418d8b9
sty 11, 2014 5:32:04 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 93c0fd9c-925d-40d9-af4e-35be3f3ec0d4 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:93c0fd9c-925d-40d9-af4e-35be3f3ec0d4
sty 11, 2014 5:32:05 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: cb32d339-0c63-4549-8821-61b2818c9548 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:cb32d339-0c63-4549-8821-61b2818c9548
sty 11, 2014 5:32:06 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 5c63f908-8019-48ac-9b4e-0b9a4c29e6e6 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:5c63f908-8019-48ac-9b4e-0b9a4c29e6e6
sty 11, 2014 5:32:07 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: ce8945a8-1fe7-40b8-a7c9-331f263be084 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:ce8945a8-1fe7-40b8-a7c9-331f263be084
sty 11, 2014 5:32:08 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: a0ed799f-471e-4496-8fb5-6f2599faa45f Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:a0ed799f-471e-4496-8fb5-6f2599faa45f
sty 11, 2014 5:32:09 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: f35803a2-7e46-4163-a9db-08977c78fd63 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:f35803a2-7e46-4163-a9db-08977c78fd63
sty 11, 2014 5:32:10 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 182e2bf0-9ad6-40e6-b767-ab6bf7173929 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:182e2bf0-9ad6-40e6-b767-ab6bf7173929
sty 11, 2014 5:32:11 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 7d71a9ec-c22b-4247-8bd2-540e75470e80 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:7d71a9ec-c22b-4247-8bd2-540e75470e80
sty 11, 2014 5:32:12 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 9a739139-241f-47c4-bfe8-bb3a1cbe3c18 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:9a739139-241f-47c4-bfe8-bb3a1cbe3c18
sty 11, 2014 5:32:13 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: d53915f6-5419-4bd0-883f-5c25a12d5678 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:d53915f6-5419-4bd0-883f-5c25a12d5678
sty 11, 2014 5:32:14 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 8eaec619-d01a-4138-bb06-44cdc8a08fe9 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:8eaec619-d01a-4138-bb06-44cdc8a08fe9
sty 11, 2014 5:32:15 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 4c176172-9ff5-479a-b39a-40e026da9e16 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:4c176172-9ff5-479a-b39a-40e026da9e16
sty 11, 2014 5:32:16 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 6b9ab775-5845-4b11-ad25-9cef7b56f041 Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:6b9ab775-5845-4b11-ad25-9cef7b56f041
sty 11, 2014 5:32:17 PM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 ClientConnectionId: 04ef0517-4410-4d63-947c-d349ed4684ad Prelogin error: host localhost port 1434 Error reading prelogin response: Connection reset ClientConnectionId:04ef0517-4410-4d63-947c-d349ed4684ad
com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset ClientConnectionId:04ef0517-4410-4d63-947c-d349ed4684ad
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1789)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.Prelogin(SQLServerConnection.java:1424)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1319)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.j256.ormlite.jdbc.JdbcConnectionSource.makeConnection(JdbcConnectionSource.java:252)
    at com.j256.ormlite.jdbc.JdbcConnectionSource.getReadWriteConnection(JdbcConnectionSource.java:184)
    at com.j256.ormlite.jdbc.JdbcConnectionSource.getReadOnlyConnection(JdbcConnectionSource.java:170)
    at com.j256.ormlite.stmt.StatementExecutor.buildIterator(StatementExecutor.java:243)
    at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:197)
    at com.j256.ormlite.stmt.StatementExecutor.queryForAll(StatementExecutor.java:118)
    at com.j256.ormlite.dao.BaseDaoImpl.queryForAll(BaseDaoImpl.java:239)
    at controler.AdminRoomControler.getListRoomEquipmentData(AdminRoomControler.java:51)
    at gui.panel.ReservationRoomPanel.reloadData(ReservationRoomPanel.java:57)
    at gui.panel.ReservationRoomPanel.initialize(ReservationRoomPanel.java:82)
    at gui.panel.ReservationRoomPanel.<init>(ReservationRoomPanel.java:40)
    at gui.frame.ApplicationFrame.initialize(ApplicationFrame.java:58)
    at gui.frame.ApplicationFrame.<init>(ApplicationFrame.java:37)
    at main.Program$1.run(Program.java:22)
    at java.awt.event.InvocationEvent.dispatch(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$200(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

最后，我们遇到了一个排版问题，在文件中和数据库中列名不匹配。不幸的是，矫正这个问题导致更多的错误出现。更令人沮丧的是，有时它甚至无法检测到不匹配的错误，所以我们完全不知所措。

这是我们用来连接数据库的代码：

package database;

import java.sql.SQLException;

import POJO.Administrator;
import POJO.Equipment;
import POJO.Event;
import POJO.Guest;
import POJO.Personnel;
import POJO.Room;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.support.ConnectionSource;


public class DbManager  {

    // Proper db address should be placed here.
    private final String DATABASE_URL = "jdbc:sqlserver://localhost:1434;databaseName=IO;integratedSecurity=true;";

    private ConnectionSource connectionSource;

    public Dao<Administrator,Integer> administratorDao;
    public Dao<Equipment,Integer> equipmentDao;
    public Dao<Event,Integer> eventDao;
    public Dao<Guest,Integer> guestDao;
    public Dao<Personnel,Integer> personnelDao;
    public Dao<Room,Integer> roomDao;

    public DbManager() throws SQLException
    {
        connectionSource = new JdbcConnectionSource(DATABASE_URL);
        administratorDao = DaoManager.createDao(connectionSource, Administrator.class);
        equipmentDao = DaoManager.createDao(connectionSource, Equipment.class);
        eventDao = DaoManager.createDao(connectionSource, Event.class);
        guestDao = DaoManager.createDao(connectionSource, Guest.class);
        personnelDao = DaoManager.createDao(connectionSource, Personnel.class);
        roomDao = DaoManager.createDao(connectionSource, Room.class);
    }

    @Override
    protected void finalize() throws Throwable
    {
        connectionSource.close();       
        super.finalize();

    }
}

我们已经彻底没有任何想法了，只要不涉及从SQL Server完全重写数据库的内容，任何帮助都将是有益的。提前感谢您！

- Dennet

---

请告诉我们您正在使用的实际 Microsoft SQL Server 版本（2012、2008、2005 等）和版本（Express Edition、Standard Edition 等）。 - Gord Thompson

当然！我们正在使用带有 Service Pack 1 的 Microsoft SQL Server 2012 开发人员版。 - Dennet

要验证 SQL Server 正在侦听的端口，请参阅此处的说明：https://dev59.com/AWct5IYBdhLWcg3wKqa9#12297597。这份文档也可能会有所帮助：http://support.microsoft.com/kb/823938，它介绍了如何配置 SQL Server 以侦听特定端口。 - Gord Thompson

Answer 1

SQL Server Browser使用1434端口。确保在SQL Server上启用了TCP/IP并尝试默认端口(1433)。

请查看此帖子。

- patterned

---

这是TCP/IP。非常感谢，现在它可以工作了！啊，最简单的事情总是会让你忘记。 - Dennet

不用谢！四只眼睛总比两只好。 - patterned