Udostępnij za pośrednictwem

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?

  • Artykuł

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

  1. Connection is successful
  2. There is no UDP connection over port 1434
  3. 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.