SQL Server Login Failures Due to Exhausted Ports
When using Microsoft Access (or another ODBC application) to access data, run code or a report against a SQL Server data source, you may receive the following error:
However, other code or reports work correctly, or the code works correctly when using a smaller data set.
This may occur if the client workstation has exhausted the pool of available TCP ports for outbound connections. This will happen if the code or report makes repeated discrete connections to the database using functions such as Dlookup. Each of these functions opens a new connection to the database, which requires a TCP port chosen at random from the ephemeral port range. When the operation is completed, the connection is closed. However, the TCP port is not released immediately, and so if a large number of connections are made in a short period, the system may exhaust all the ports which are available for this purpose.
Mechanisms which may have this effect are traversing a large record set and issuing a Dlookup command for each record, or using Dlookup in the Details section of a report, which causes Dlookup to fire for every record in the report. Where possible, code should be rewritten to avoid the use of repeated discrete database lookups, but a workaround is to expand the pool of ephemeral ports.
By default, Windows 2000/XP/2003 use a range of 1025 to 5000, or 49152 to 65535 if KB951746 is installed. Windows Vista/2008 use a range of 49152 to 65535. These ranges provide for 3,976 or 16,384 ports respectively, which, due to other processes using ports, might equate to a maximum of roughly 3,900 or 16,300 connections. Thus the database operation may be able to process that many records before failing (or correspondingly less if there is more than one discrete connections being executed per record).
On Windows 2000/XP/2003, the ephemeral port range may be expanded significantly by changing the upper limit, to a maximum of 65535. This may be done by creating a DWORD value called MaxUserPort under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters. A value of 65535 decimal (or FFFF hexadecimal) would create a range of more than 64,500.
On Windows Vista/2008, the ephemeral port range needs to be specified as a starting value plus a number of ports. Open a command prompt in elevated mode and type:
netsh int ipv4 set dynamicport tcp start=2000 num=62000
(or whichever values are suitable.)
Note that because the number of ephemeral ports is finite, and fairly small (a theoretical maximum of about 65,535), this workaround does not scale to larger datasets. If the operation which is failing has more records than this, the only recourse is to alter the code to open fewer discrete connections to the database.
For a more detailed discussion on ephemeral ports, see Choosing a TCP Port for a Network Service.