(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)
The scenario:
You have an application (most likely this will happen with a webapplication) that connects and interacts with a server and
all of a sudden there is no access to it. It seems to be gone because you get the following error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
…
This is strange since the server is up and you can connect to it from another client, and you know that you have set it up with SAC (Surface Area Configuration)
to allow remote connections on TCP/IP.
Even more strange is that after some time you can again connect to it.
It might even be that the error shows around a particular time of the day or month, perhaps when people come back from lunch or when they are paying their bills at the end of the month.
What is going on?
The clue is to read further into the error message and notice the following:
(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)
The most common reason for this is that the application is NOT using connection pooling.
You all know how pooling works, but if you do not, the short version is that opening and closing connections is expensive.
So when you use connection pooling and the connection is closed, it is not really closed.
Instead it is put into a pool and when a new connection is opened, a new connection is not created, instead it is picked from the pool and 'reactivated'
Now, if you do not use connection pooling, then a new connection is created and when it is closed, it is disposed and not put into the pool.
Why is this important?
Well, when a connection is opened a socket (or port) is opened on the client for communication with the server.
Now, when the connection is closed, the port is not closed, instead it goes into what is called TIME_WAIT in order to wait for potentially late or misrouted packages etc.
The default for the port to be in TIME_WAIT is 240 seconds (4 minutes), and during that time, the port is not available.
This means that a new connection can’t use this port during the TIME_WAIT state, and a new one will be opened.
If you are running your application on a Windows 2003 install, the ports that can be used are 1024 to 5000, which means that roughly 4000 ports available.
See more at:
"TCP TIME-WAIT Delay"
https://msdn2.microsoft.com/en-us/library/ms819739.aspx
So, how does this tie together?
In the scenario above, imagine that you have 1000 employees or users, and they all come back from lunch, they sit all down and they use the web application
in order to bring up 4 records each.
The retrieval of each record takes 1 second. Now, if you use the information above and do the math you will understand that 1000 users * 4 connections will
open up 4000 ports on the machine that runs the web application, which is the limit. Now all these ports will be in the TIME_WAIT state for 4 minutes
and during this time, no new connections can’t be opened and you will get the error above.
After the 4 minutes have passed the ports are released and the users can once again connect.
However, if using connection pooling, this will not occur since the connection is not closed and therefore it will not put the port into TIME_WAIT.
On a .Net application, pooling should be ON by default, but it could have been disabled (or set to false) in the connection string.
So, simply have a look at the connection string and change it accordingly:
// Pooling false = fails
string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=false";
// Pooling true = works, this is the default but included here for clarity
string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=true";
Or if the application is not a .Net app, instead it uses, for example VBScript, it will not use pooling.
Some notes.
- This commonly happens when deploying the app to go live, since when you test, you rarely use 4000 connections in a very short time.
- It only happens when there is a lot of activity in a short period of time, when there is less activity; the ports have time to be released.
- It would rarely happen on a standalone application since one user would rarely use 4000 connections in a very short time
- You could tweak the max number of ports to be allowed and/or reduce the number or seconds for the TIME_WAIT, but use connection pooling if possible.
Below is a demo application, run it as it is, after 4000 iterations, you will get the exception.
Then open a command prompt and type "netstat -aon" (no quotes) and you will see a lot of the following:
TCP localip:port remoteip:port TIME_WAIT 0
Or you can have a look at the port using the TCPView software, see:
"TCPView for Windows"
https://technet.microsoft.com/en-us/sysinternals/bb897437.aspx
it will also show a lot of ports in TIME_WAIT.
Now use the connection string with pooling set to 'true', run it again, it will work (and it will most likely be quicker since
there is no creation and disposal of the connection, it just goes to 'sleep' for a while).
REPRO:
static void Main(string[] args)
{
// Need to have the tcp: prefix if server and client is on the same machine, otherwise shared memory is used.
// Pooling false = fails
string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=false";
// Pooling true = works, this is the default but included here for clarity
//string cs = @"Data Source=tcp:<your server>;Integrated Security=True;Pooling=true";
try
{
for (int i = 0; i < 8000; i++)
{
SqlConnection conn = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("SELECT @@Version", conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (i % 100 == 0)
Console.WriteLine("\nConnections opened #: {0} -> {1}", i.ToString(), reader[0].ToString());
}
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
Hope this makes sense.
So, to avoid this error. Make sure that you use connection pooling.
If this is not possible, then you can tweak the following keys in the registry, however, I would recommend against doing this unless you really have to
since it may cause unexpected behaviors.
HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\TcpTimedWaitDelay
"TcpTimedWaitDelay"
HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPorts
"MaxUserPort"
"Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled"
https://support.microsoft.com/default.aspx?scid=kb;EN-US;328476
Connection pooling:
".NET Framework Developer's Guide -> Using Connection Pooling with SQL Server"
https://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
".NET Framework Developer's Guide -> Connection Pooling for the .NET Framework Data Provider for SQL Server"
https://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx