Can't make multiple JDBC connections to SQL Server

Question

I'm trying to load test a few pure SQL query on my local SQL Server (I'm not sure this is the best way to test performance, but this would give me a rough estimate). I'm using Java's PreparedStatement and MS JDBC for SQL Server, and I instanciate one hundred Thread with a new Connection in each one.

I had a SQL Server Express edition (11.0.210) already installed. Unfortunately, Express edition won't run multiple query in parallel (as will the Pro edition), so my test wouldn't be quite usefull.

A colleague told me I could use SQL Developer 2014. I had some difficulties setting it up, so I might have forgotten something. I started using it with sysadmin (sa) account.

Making a single Connection with JDBC and querying works as expected.

Now, I'm trying to create two Connection:

String SQLSERVER_CONNECTION_STRING = "jdbc:sqlserver://localhost\\SQLDEVELOPPER:55372";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection c1 = DriverManager.getConnection(SQLSERVER_CONNECTION_STRING, USER, USER_PWD);
Connection c2 = DriverManager.getConnection(SQLSERVER_CONNECTION_STRING, USER, USER_PWD);

First I get this message on stderr:

nov. 08, 2017 10:41:00 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
AVERTISSEMENT: ConnectionID:1 ClientConnectionId: bfb1d22d-a5fb-4f74-9be8-60fb91c9d701 Prelogin error: host localhost port 55372 Error reading prelogin response: Software caused connection abort: recv failed ClientConnectionId:bfb1d22d-a5fb-4f74-9be8-60fb91c9d701

And get this error 16 times:

nov. 08, 2017 10:41:01 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
AVERTISSEMENT: ConnectionID:1 ClientConnectionId: fcd64c62-ba9a-48ee-b195-11307e7bad30 Prelogin error: host localhost port 55372 Error reading prelogin response: Une connexion existante a dû être fermée par l’hôte distant ClientConnectionId:fcd64c62-ba9a-48ee-b195-11307e7bad30

Translated: "An existing connection had to be closed by distant host" (I read it first as "An existing connection might have been closed by distant host", but as @fab's comment points out, it might be wrong)

Then this stacktrace:

Exception in thread "main" java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Une connexion existante a dû être fermée par l’hôte distant ClientConnectionId:0fa4b1cb-ae03-407a-bcc3-54a0cd85005c
    at fr.aso.sandbox.utils.SQLServerConnectionManager.getConnection(SQLServerConnectionManager.java:24)
    at fr.aso.sandbox.myComp.myComp.test.KeycopterTest.doubleConnectionThreadTest(KeycopterTest.java:78)
    at fr.aso.sandbox.myComp.myComp.test.KeycopterTest.main(KeycopterTest.java:49)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Une connexion existante a dû être fermée par l’hôte distant ClientConnectionId:0fa4b1cb-ae03-407a-bcc3-54a0cd85005c
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2397)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2384)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1884)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.Prelogin(SQLServerConnection.java:2137)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1973)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at fr.aso.sandbox.utils.SQLServerConnectionManager.getConnection(SQLServerConnectionManager.java:22)
    ... 2 more

I've found SO questions and answers about this, but for most of them were about a single connection not working.

Here, the problem occurs when I try 2 or more connections with the same user, So I have ruled out the "TCP not enabled" and "Firewall block sql server" problems.

SQL Developer version is :

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
    Jun 17 2016 19:14:09 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Because I could make multiple time the same connection on SQL Express, I believe this is a SQL Server configuration problem, but couldn't find anything related.

Did I miss something somewhere ? Is there a way to get a more explicit error ?

As it could be important to understand the error (and correct me if I'm wrong), but I believe the translation should be "An existing connection *had to be* closed by distant host" — Fab, Nov 09 '17 at 15:26
@fab Thanks ! I think you're right, I've edited the question. — Asoub, Nov 09 '17 at 17:01

Asoub · Accepted Answer · 2017-11-10T08:13:01.530

In fact there was a more detailed error in the error log:

2017-11-09 18:13:01.85 Logon       Error: 17810, Severity: 20, State: 2.
2017-11-09 18:13:01.85 Logon       Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

If you don't know where is your error log for your database, right click on it in SSMS -> properties -> Database settings, under Database default locations , "Log: path/to/your/logs/".

After looking around, can't find how to make multiple connection with sysadmin user, feels like it's not possible (even if I did on SQL Server Express edition, maybe it wasn't configured the same way or "sa" user wasn't really sysadmin, or the fact that it was single threaded prevented from actually opening multiple connections).

As a solution I'll try making a user which is not sysadmin-like.

If you end up having the problem with a non sysadmin-like user, try using sp_configure 'user connections', 0, and if you're having the original erro with a single connection check if TCP is enabled.

score -1 · Answer 2 · edited Sep 04 '19 at 01:38

-1

In your case, for testing the performance, use SQLStress app.

edited Sep 04 '19 at 01:38

Dale K

25,246
15
42
71

answered Nov 08 '17 at 10:33

Ahmad Gozin

23
3

Can't make multiple JDBC connections to SQL Server

2 Answers2