(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

(provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

  • Comments 2

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"

http://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"

http://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"

http://technet2.microsoft.com/windowsserver/en/library/38b8bf76-b7d3-473c-84e8-e657c0c619d11033.mspx

 

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPorts

"MaxUserPort"

http://technet2.microsoft.com/windowsserver/en/library/730fb465-d402-4853-bacc-16ba78e9fcc01033.mspx

 

"Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;328476

 

Connection pooling:

".NET Framework Developer's Guide -> Using Connection Pooling with SQL Server"

http://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"

http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

 

 

 

 

 

 

 

 

Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post
  • Hi,

    Thanks for sharing this article, this was really helpfull me.

    One more point i would like to add here, after setting pooling=true; my web site is running much faster.

    The loading time of almost all the pages has also reduced drastically after making the connection as pooling=true.

    Once againg thanks for sharing this article.

    Thanks,

    Bishnu.

  • my nokia501 getting problem with whatsapp so i uninstall the app and install it again but during mobile no. verification a massage shows  like "error 0 in socket::open" . so what is this problem and can i"ll be able to operate whatsapp again.......???????

Page 1 of 1 (2 items)