The "Maximum number of concurrent connections" setting in SQL Server and ways to overcome the error message "A connection was successfully established with the server, but then an error occurred during the pre-login handshake."

The Maximum Number of Concurrent Connections Setting in SQL Server

by

Among its plethora of available and useful settings, SQL Server has a setting that is called “Maximum number of concurrent connections“. This setting by default is set to 0 (zero), which means “unlimited” concurrent connections and it is generally advised not to change it.

In case  you changed this setting there is the risk to lock yourself out of your SQL Server instance because there is the possibility that other connections (if we are talking about an active database environment with client connections) might take over the available connection slots, thus keeping you out of the SQL Server instance.

This article has two purposes:

  1. To present the theory behind the “Maximum number of concurrent connections” setting,
  2. To help to overcome an unpleasant situation where this setting might have been changed and you locked out yourself from accessing your SQL Server instance.

The theory behind the Maximum Number of Concurrent Connections setting in SQL Server

The “Maximum number of concurrent connections” setting in SQL Server specifies the maximum number of simultaneous user connections on a SQL Server instance. The key word in the previous sentence is “simultaneous”.

As this relevant super cool Microsoft Docs article says, “the actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware…”

For example, SQL Server 2017, allows a maximum of 32,767 user connections.

How to overcome a possible issue

Generally, you are advised not to play with the “Maximum number of concurrent connections” setting in SQL Server, especially if you are not exactly sure of what you are doing. If you misuse this setting, then there is the possibility to lock yourself out of your SQL Server instance.

In case you experience problems connecting to your SQL Server instance due to the above setting, you might get error messages similar to the below:

Error connecting to ‘SQL Instance Name Goes Here’.

——————————
ADDITIONAL INFORMATION:

Failed to connect to server [SQL Instance Name Goes Here]. (Microsoft.SqlServer.ConnectionInfo)

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

OK, this looks bad. Let’s see what we can do about it.

First, don’t panic and let’s try to find a solution together 🙂

Second, let’s try to set the “Maximum number of concurrent connections” setting back to its default value which is 0 (zero = unlimited concurrent connections).

Apparently we cannot do that via SQL Server Management Studio right now, so let’s see other possible options we can try.

Prior to any other options – Prerequisites
  1. Stop all services related to your SQL Server instance (i.e. SQL Server Agent service, SQL Full-text Filter Daemon Launcher, etc.) including the actual SQL Server service (Database Engine).
  2. Start only the actual SQL Server service and proceed directly and try any of the below options.
Option 1: SSMS 

Try to log in to SQL Server using SSMS. If successful, within SSMS, right-click on the instance, select the “Connections” tab, set the “Maximum number of concurrent connections” setting back to 0 (zero = unlimited) and restart the SQL Server instance along with starting back all related services.

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

Option 2: SQLCMD

If Option 1 didn’t work, you can try the good old command line using the SQLCMD tool. So, open a command prompt and try to connect to SQL Server:

If your Windows user has sysadmin access on the SQL Server instance you can establish a trusted connection using the command:

sqlcmd.exe -E -S instanceNameGoesHere

If you use a username and password to connect to your SQL Server instance as sysadmin, you can try the below command:

sqlcmd -S instanceNameGoesHere -U userNameGoesHere -P passwordGoesHere

If you managed to connect to your SQL Server instance using one of the two SQLCMD commands above, then you can try executing the below script:

EXEC sp_configure 'show advanced options', 1;  
GO
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'user connections', 0
GO
RECONFIGURE WITH OVERRIDE
GO
exit

Screenshot example

The "Maximum number of concurrent connections" setting in SQL Server and ways to overcome the error message "A connection was successfully established with the server, but then an error occurred during the pre-login handshake."

If you get no errors, after the above, you just restart your SQL Server, start all other related services (which used to be running in the first place) and you are good to go!

Optionally, if you didn’t have ‘advanced options’ enabled in the first place, you can disable them back using the T-SQL statement:

EXEC sp_configure 'show advanced options', 0;  
GO
RECONFIGURE WITH OVERRIDE

 

Option 3: DAC and SSMS

If Options 1 and 2 didn’t work, you can try connecting to your SQL Server instance using DAC. DAC stands for Dedicated Administrator Connection and it is a special connection for SQL Server System Administrators (sysadmin). You can follow the instructions in this MS article on how you can try connecting to your SQL Server instance using DAC and SSMS. If you manage to connect, like in Option 1, within SSMS, right-click on the instance, select the “Connections” tab, set the “Maximum number of concurrent connections” setting back to 0 (zero = unlimited) and restart the SQL Server instance along with starting back all related services.

If all the above fail

In the case where all the above failed and you still cannot connect to your SQL Server instance, you can try restarting the instance again (just the Database Engine) and at the very moment your SQL Server instance is up and running, you can try with any of the provided options.

Moral of the story

The above story has a moral: never, ever, play with settings you are not sure about their use and the possible consequences their misconfiguration might create for your SQL Server instance.

Drop me a line if you would like to further discuss this!

 

Featured Online Courses:

 

Related Error Messages and ways to Resolve them:

 

 

Check our eBooks on SQL Server!

Check out our latest software releases!

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



Views: 13,387