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 tips like this! Get the 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!
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:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
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
- There is not enough space on the disk. (mscorlib)
- A network-related or instance-specific error occurred while establishing a connection to SQL Server
- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
- … more SQL Server troubleshooting articles
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).