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

  • Článek

This article helps you resolve the problem where you are not be able to connect remotely to SQL Server using TCP/IP protocol.

Original product version:   SQL Server
Original KB number:   2018930

Symptoms

When using Microsoft SQL Server, 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 don't have this permission can't 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 (Transact-SQL) Dynamic Management View (DMV) but not in sys.dm_exec_connections (Transact-SQL) 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 2008 and later versions:

    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 (Transact-SQL), this view doesn't contain information on the ports and protocols that SQL Server instance is currently configured to use. To find that information, see SQL Server Configuration Manager.

Resolution

  • Method 1: Drop the endpoint that's causing the problem using the DROP ENDPOINT (Transact-SQL) 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 (Transact-SQL) 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.