Freigeben über

How best to connect to a default instance of SQL Server listening on a non-standard port with a firewall enabled.

  • Artikel

We had a case recently where the Default instance of SQL Server was listening on a non-default port.

Any guesses what happened when the client tried to connect to the Default instance using TCP? Reading KB 823938 will provide a clue but doesn't spell it out. The relevant portion of the KB reads:

"If a default instance of SQL Server is listening on a port other than port 1433, you must either define a server alias name or change the global default port by using the Client Network Utility."

The client data access library realized we were connecting to a Default instance, bypassed SQL Browser port resolution, and tried connecting directly to 1433 then failed. This led to a series of questions about data access, port resolution, and firewall behavior when SQL is not listening on 1433. The main question we focused on was, “What is the best way to connect to a default instance of SQL that is not listening on port 1433?” Rather than let good research go to waste, we're posting the results here. 

The environment: We’re troubleshooting a connection failure with the following error message: “Named Pipes Provider: No process is on the other end of the pipe.”

Default SQL instance listening on non-default port 1533

Protocol enabled in SQL: TCP only

Protocol Order on the client: 

1.) TCP

2.) Named Pipes

Both Firewalls are OFF.

 

When we attempt to connect to the SQL Server by specifying the default server name...

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>osql -S treyx86 -U sa -P nnnnn

 

We get the following error message...

[SQL Server Native Client 10.0]Named Pipes Provider: No process is on the other end of the pipe.

[SQL Server Native Client 10.0]Communication link failure

[SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

 

This error makes sense and is interesting for two reasons. As mentioned above, we're connecting to the Default instance so we're not sending a UDP request to the SQL Browser service to find out the port - we're going straight for 1433 and failing. So why the Named Pipes reference? That's due to the protocol rollover logic mentioned in KB 328383. If TCP connectivity fails, then SNAC will attempt to use the next protocol in the list, assuming it is enabled.

Scenario 1: What if we keep the same settings above but also enable SQL Server to accept a Named Pipes connection?

The result is a successful connection. In a network trace, you will see three SYN's paired with an ACK/RESET response before the Named Pipe connection starts in frame 410. Port 445 is used for SMB directly over TCP/IP and marks the start of the Named Pipes connection process. 

Also, pay attention to the ACK/RESET response from the server. If you look at the TimeDelta column, you’ll see we very quickly get back the ACK/RESET response with the firewall off. In Scenario 2 we don’t get an ACK/RESET response and it is the cause of a delayed connection. 

clip_image002

 

Scenario 2: This time we turn the server firewall ON and open our SQL port (1533). 

What happens here? Our port is open, so TCP should work assuming the protocol is enabled. But we saw in Scenario 1 that port resolution does not happen when connecting to the Default instance, so opening the port won't matter here. We know Named Pipes worked in Scenario 1 so will it work now with the firewall enabled (and proper NP ports opened)? The answer is maybe.

The protocol rollover to Named Pipes will still happen, but the TCP connection failure takes much longer in this case. With the firewall enabled, port 1433 is "stealthed" and the SYN request goes unanswered as shown in the trace below. The explanation is given in KB 170359. Each unanswered SYN is followed by another SYN request, but the period between these request doubles (TimeDelta column below). In the trace, no response is received to frame 211 so the client sends another SYN after three seconds, then another after 6 seconds, etc. Note, this is different from Scenario 1 where the absence of a firewall allowed the server to return ACK/RESET. With the firewall on we get back no response at all. This process will continue up to the count of TcpMaxDataRetransmissions or until the process cancels the attempt. Most data access components have a default connection timeout of 15 seconds so in this scenario, SNAC gives up on the connection before we can start the Named Pipes connection.

clip_image004

 

To finish out the "maybe" answer, this would have worked had the connection timeout value been 22 seconds or greater. Three failed SYN requests starting with a 3 second timer equals 21 seconds (3 seconds + 6 seconds + 12 seconds). When we set the "-l" param in OSQL to extend the connection timeout to 22 seconds then we can successfully connect.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>osql -S treyx86 -U sa -P nnnnn -l 22

1> select session_id, Protocol = left(net_transport, 10), AuthScheme = left(auth_scheme, 10) from sys.dm_exec_connections where session_id = @@SPID

2> go

session_id Protocol AuthScheme

----------- ---------- ----------

          51 Named pipe SQL

 

So how do you connect when running a default instance on a non-standard port when a firewall is blocking 1433? 

The recommendation in KB 823938 to create an alias is the best bet. You could also change what the data access client uses for the default port, but that change would be global to all applications on the workstation when connecting to *any* SQL Server, so an alias is preferred between the two options in KB 823938.

We also see Named Pipes succeed as long as you have the File & Print sharing ports open. If you’re doing simple, single-hop passing of credentials, then Named Pipes is a viable option. One item to be aware of is that the protocol order on the client may specify TCP before Named Pipes, and connections may timeout in the TCP process before giving Named Pipes a try. This is easily remedied by changing the protocol order or tweaking the TCP “retry” properties.

Another option is to force the port in the connection string inside your code which will require compilation and redeployment. This will require a change in all applications (SSIS package, DSN, custom applications, local SSRS reports etc) installed in your client machine.

But setting up an alias to force communication on the non-standard TCP port takes little time and code will not need recompiling, so this is our recommendation. 

Author : Enamul(MSFT), SQL Developer Technical Lead , Microsoft and Trey(MSFT), SQL Developer Engineer , Microsoft

Reviewed by : Azim(MSFT), SQL Developer Technical Lead , Microsoft

Comments

  • Anonymous
    April 28, 2010
    The comment has been removed
  • Anonymous
    April 29, 2010
    The comment has been removed
  • Anonymous
    May 09, 2015
    Good