Why SQL Server connection fails, when I change the port from dynamic to static, in SQL Server Configuration Manager, for my named instance of SQL Server, but I do not restart the SQL Server instance?

Article
12/25/2014

Introducing the problem scenario

Recently I had to investigate the following scenario.

SQL Server named instance is configured to listen on a dynamic port.
We change the port to static one, other than the one SQL Server is currently listening on, in SQL Server Configuration Manager.
After the change, we do not restart the SQL Server Service.
We expected the existing connection will not fail. In other words we will still be able to continue connecting to the SQL Server the same way as before, until the SQL Server service is restarted.

We find that, the application starts failing to connect. Both .NET application and SQL Server Management Studio, shows the same symptom.

You may get the following error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

Environment Details

Client: client.fqdn.com

Server: xxxxx.fqdn.com

SQL Server Instance: xxxxx\SQL2012

Listening port before change 61111 (dynamic).

Changed to static port: 51000

It sure prompted when I clicked on apply, that the changes will not apply until we restart the SQL Server Service.

Test Scenario 1: Change the port to static port, from SQL Server Configuration Manager, do not restart SQL Server Service, connect from C# application remotely.

Code snippet I used for testing:

string cnStr = @"server=xxxxx\sql2012;database=TestDB;trusted_connection=sspi;";
for (; ; ) {

using (SqlConnection cn = new SqlConnection(cnStr))
using (SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn))

{

cn.Open();

System.Diagnostics.Debug.WriteLine("Connected: SPID: {0}", cmd.ExecuteScalar());

}

System.Threading.Thread.Sleep(10000);

}

Error message we get from application:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"}

Took a netmon trace on the server.

Trace shows the activity on the browser port 1434

udp.Port==1434

1906 2:12:26 PM
12/16/2014 17.0452530 client.fqdn.com xx.xx.xx.xx SSRP SSRP:Windows stub parser: Requires full Common parsers. See the "How Do I Change Parser Set Options(Version 3.3 or before) or Configure Parser Profile (Version 3.4)" help topic for tips on loading these parser sets. {UDP:624, ESP:308,IPv4:296}

1907 2:12:26 PM
12/16/2014 17.0455763 client.fqdn.com xx.xx.xx.xx SSRP SSRP:Windows stub parser: Requires full Common parsers. See the "How Do I Change Parser Set Options(Version 3.3 or before) or Configure Parser Profile (Version 3.4)" help topic for tips on loading these parser sets. {UDP:625, IPv4:296}

1909 2:12:26 PM
12/16/2014 17.0460581 xx.xx.xx.xx client.fqdn.com SSRP SSRP:Windows stub parser: Requires full Common parsers. See the "How Do I Change Parser Set Options(Version 3.3 or before) or Configure Parser Profile (Version 3.4)" help topic for tips on loading these parser sets. {UDP:625, IPv4:296}

So applied filter tcp.Port==51000 and tcp.Flags.Syn==1

1914 2:12:26 PM
12/16/2014 17.1025252 client.fqdn.com xx.xx.xx.xx TCP TCP:Flags=......S., SrcPort=65113, DstPort=51000, PayloadLen=0, Seq=3642803191, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:626, ESP:308, IPv4:296}

1915 2:12:26 PM
12/16/2014 17.1025252 client.fqdn.com xx.xx.xx.xx TCP TCP:Flags=......S., SrcPort=65113, DstPort=51000, PayloadLen=0, Seq=3642803191, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:627, IPv4:296}

1980 2:12:26 PM
12/16/2014 17.6058845 client.fqdn.com xx.xx.xx.xx TCP TCP:Flags=......S., SrcPort=65113, DstPort=51000, PayloadLen=0, Seq=3642803191, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:635, IPv4:296}

2742 2:12:32 PM
12/16/2014 23.6110969 client.fqdn.com xx.xx.xx.xx TCP TCP:[SynReTransmit #1980]Flags=......S., SrcPort=65113, DstPort=51000, PayloadLen=0, Seq=3642803191, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:635, IPv4:296}

Frame Details for connection failure:

You basically get a SYN re-transmit, after the last connection attempt.

Conclusion

Seems that when the port is changed from dynamic to static, the browser service was aware of the issue, so when client first connected to the browser service, it just handed over the new port information as the browser service connection shows.

Test Scenario 2: Change the port to static port, from SQL Server Configuration Manager, do not restart SQL Server Service, connect from SSMS, running the client machine.

Observation

Connection is successful
There is no UDP connection over port 1434
SSMS tries a direct connection to old port

Netmon Trace shows Successful handshake

2256 2:09:53 PM 12/16/2014 16.4745724

sqlservr.exe xx.xx.xx.xx xx.xx.xx.xx TCP TCP:Flags=......S., SrcPort=64874, DstPort=61111, PayloadLen=0, Seq=2379066210, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:893, IPv4:635}2257 2:09:53 PM
12/16/2014 16.4754496

sqlservr.exe xx.xx.xx.xx xx.xx.xx.xx TCP TCP:Flags=...A..S., SrcPort=61111, DstPort=64874, PayloadLen=0, Seq=666085223, Ack=2379066211, Win=8192 ( Negotiated scale factor 0x8 ) = 2097152 {TCP:893, IPv4:635}2258 2:09:53 PM
12/16/2014 16.4760709

sqlservr.exe xx.xx.xx.xx xx.xx.xx.xx TCP TCP:Flags=...A...., SrcPort=64874, DstPort=61111, PayloadLen=0, Seq=2379066211, Ack=666085224, Win=517 (scale factor 0x8) = 132352 {TCP:893, IPv4:635}

Frame details for the successful connection.

And complete conversion is here.

4. Interesting to see how we bypassed the browser service connection, I wonder if the connection information was being cached.

5. So tested restarting SSMS. Now, if I try a connection, it seems I get the following error:

TITLE: Connect to Server

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

Cannot connect to xxxxxxx\sql2012.

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

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or was not
accessible. Verify that the instance name is correct and that SQL Server is
configured to allow remote connections. (provider: TCP Provider, error: 0 - A
connection attempt failed because the connected party did not properly respond
after a period of time, or established connection failed because connected host
has failed to respond.) (Microsoft SQL Server, Error: 10060)

6. The client side netmon trace for UDP communication shows:

27.2043295 74 3860 8:07:07 AM 12/17/2014 38.3465472 xx.xx.xx.xx server.fqdn.com SSRP SSRP:Windows stub parser. Select the "NetworkMonitor\Windows" profile from the parser profiles drop-down menu. {UDP:1392, ESP:1391,IPv4:320}

0.0000570 51 3861 8:07:07 AM 12/17/2014 38.3466042 xx.xx.xx.xx server.fqdn.com SSRP SSRP:Windows stub parser. Select the "NetworkMonitor\Windows" profile from the parser profiles drop-down menu. {UDP:1393, IPv4:320}

0.0012585 133 3862 8:07:07 AM 12/17/2014 38.3478627 server.fqdn.com xx.xx.xx.xx SSRP SSRP:Windows stub parser. Select the "NetworkMonitor\Windows" profile from the parser profiles drop-down menu. {UDP:1393, IPv4:320}

So, browser service already know about the change and give the new port information to the client. The information is the same in the server side trace but

7. Now client tries to connect to port 51000 and gets a reset from the client side, fails subsequently

0.0000000 90 3875 8:07:07 AM 12/17/2014 38.4063314 Ssms.exe xx.xx.xx.xx server.fqdn.com TCP TCP:Flags=......S., SrcPort=54176, DstPort=51000, PayloadLen=0, Seq=591041439, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:1399, ESP:1391, IPv4:320}

0.0000217 66 3876 8:07:07 AM 12/17/2014 38.4063531 Ssms.exe xx.xx.xx.xx server.fqdn.com TCP TCP:Flags=......S., SrcPort=54176, DstPort=51000, PayloadLen=0, Seq=591041439, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:1400, IPv4:320}

0.0005405 60 3877 8:07:07 AM 12/17/2014 38.4068936 Ssms.exe server.fqdn.com xx.xx.xx.xx TCP TCP:Flags=...A.R.., SrcPort=51000, DstPort=54176, PayloadLen=0, Seq=0, Ack=591041440, Win=0 {TCP:1400, IPv4:320}

0.5031681 66 3912 8:07:07 AM 12/17/2014 38.9100617 Ssms.exe xx.xx.xx.xx server.fqdn.com TCP TCP:Flags=......S., SrcPort=54176, DstPort=51000, PayloadLen=0, Seq=591041439, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:1410, IPv4:320}

6.0006699 62 4686 8:07:13 AM 12/17/2014 44.9107316 Ssms.exe xx.xx.xx.xx server.fqdn.com TCP TCP:[SynReTransmit #3912]Flags=......S., SrcPort=54176, DstPort=51000, PayloadLen=0, Seq=591041439, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:1410, IPv4:320}

8. So behavior is the same in both test scenarios.

Explanation

Here is the details of what happens behind the scene:

Once we change the port from dynamic to static, in SQL Server Configuration Manager, the related registry get updated.
The related registry key is, as an example, for the instance we tested against: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
Browser Service, being a separate service, is notified (becomes aware) about the change.
When an application or SSMS (essentially client provider/driver SNI layer), tries to connect the next time, it contacts the browser service, which hands over the new port information for the named instance.
Client (driver/provider SNI layer) for the application, tries to connect to the new port of the SQL Server. However, SQL Server does not listen to the port (there is no end point with the new port number), hence the connection fails.

The behavior is by design, in the SQL Server Releases. SQL Server is not aware of the change, until the Service is restarted, so the warning is appropriate. The browser service, being a separate service is not directly related to SQL Server, however it looks into the registry changes that happen through SQL Server Configuration Manager.

Comments

To hide the machine information, I either erased or renamed the client/server name or IP addresses involved in my repro scenarios, I reproduced the issue with two of my machines.

Partager via