Users may not be able to connect remotely to SQL server using TCP/IP protocol


Symptoms


When using Microsoft SQL Server 2005 or SQL Server 2008 you may see one or more of the following symptoms:

  • Only the users that have CONTROL SERVER permission (for example members of syadmin fixed server role) are able to connect via TCP/IP. Users who do not have this permission cannot connect remotely via TCP/IP protocol either using Windows or SQL Server authentication.
  • Note: You will notice that the elevated user connections only show up in sys.dm_exec_sessions Dynamic Management View (DMV) but not in sys.dm_exec_connections view.
  • Both local and remote connections using Named Pipes protocol as well as local connections using shared memory protocol continue to work fine. 

Additionally the following messages are logged in the SQL Server Errorlog file:

  • At  SQL Server start up:
  •  
    Error: 26023, Severity: 16, State: 1.
    Server TCP provider failed to listen on [ 'any' <ipv6> 1963]. Tcp port is already in use.
    Error: 9692, Severity: 16, State: 1.
    The Service Broker protocol transport cannot listen on port 1963 because it is in use by another process.
     
  •  For failed logins:

SQL Server 2005:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'MyDomain\TestAcc'. [CLIENT: ...]

SQL Server 2008:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'MyDomain\TestAcc'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

Cause


The error occurs when you configure a TCP endpoint for Service Broker using the same port that the SQL Server instance is configured to use. You can obtain the list of endpoints by executing the following query

select * from sys.tcp_endpoints

Note: As explained in Books Online topic on sys.tcp_endpoints, this view does not contain information on the ports and protocols that SQL Server instance is currently configured to use. To find that information use SQL Server Configuration manager .

Resolution


Method 1: Drop the endpoint that is causing the problem using the DROP ENDPOINT command.

For example to drop an endpoint named TestEP you can use the following command :

DROP ENDPOINT TestEP 

Method 2: Alter the endpoint to use a different port using the ALTER ENDPOINT command. 

For example to alter an endpoint named TestEP to use a different port you can use the following command :

ALTER ENDPOINT TestEP as tcp (listener_port=1980)

More Information


Similar issues may also occur with other TCP endpoints like those created for Database mirroring and the error messages at SQL Server startup will change accordingly.