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:
- To present the theory behind the “Maximum number of concurrent connections” setting,
- 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’.
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
- 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).
- 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 SQL Server Administration tips via live demonstrations and hands-on guides!
Check my online course on Udemy 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!
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
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
Enroll to this course on Udemy
Learn about all major new features and enhancements in SQL Server 2019 and see many live demonstrations (learn more…).
Enroll to this course on Skillshare
Enroll to this course on Udemy
In this course, you will learn all about Entity Framework, what it is, how it works, what are its available workflows and their pros and cons. Also, via comprehensive live demonstrations, we will build many apps using Visual Studio with C# and Entity Framework (learn more…)
Enroll to this course on Skillshare
Related Error Messages and ways to Resolve them:
- Operating System Error 170 (Requested Resource is in use)
- There is no SQL Server Failover Cluster Available to Join
- Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
- SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
- Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
- The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group
- … more SQL Server troubleshooting articles
Check our Online Courses:
- Essential SQL Server Administration Tips (new)
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- SQL Server Fundamentals (SQL Database for Beginners)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
Check out Artemakis’s 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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub