1

When connecting to a sql server instance one has the option of browsing the network for sql instances. This used to work for (returning ~50 entries) us until we were migrated into a larger domain with a couple thousand machines, with the possibility of a few thousand sql instances. It now searches for 2-3 minutes then the list is empty.

Does anyone know if there is a timeout on how long it will search? If so, is the timeout configurable?

(I know 2-3k servers isn't useful to scroll through - but still need an answer)

2

The most likely reason for this is port 1434 UDP being blocked on the new network.

An in-depth analysis of how SQLBrowser messaging works can be found here: https://www.mssqltips.com/sqlservertip/2057/why-listing-all-of-your-sql-servers-does-not-always-work/ That article also lists other reasons for this issue, like the SQLBrowser service not running on the servers, but based on your description of the problem, it looks like the new network you are working on is isolated by a firewall that blocks UDP traffic on port 1434.

1
  • Thank you - very detailed article. I bet 1434 is indeed blocked; the network team makes improvements to security all the time which may be the cause. If I am able to resolve I'll update. Jul 13 '18 at 13:22
1

The SQL Server Browser time is not configurable

From my findings, it doesn't appear that the SQL Browser Service is configurable in terms of time. Additionally, it rides on port 1434 via the UDP protocol. A reason that you can no longer see any servers is that the network admins may have turned of UDP over port 1434. I certainly wouldn't not want anyone to broadcast spam for couple of thousand SQL Servers every time they opened SQL Server Studio. I work at a major hospital and we've seen rogue entities in China try to hack our network multiple times. As such, we're turning off ports and segmenting everything left and right.

AND, the SQL Server Browser service opens vulnerabilities on the network.

See SQL Server Browser Vulnerabilities:

https://kb.iweb.com/hc/en-us/articles/230268308-Guide-to-Microsoft-SQL-Server-Browser-Service-Access-Amplification-Issues

Barring the blocking issue, you can check to make sure that your UDP performance isn't just slow for some reason with this utility:

https://superuser.com/questions/580311/why-is-my-udp-so-slow

Now To specifically address your question

Your SQL Server Browser Service (SSBS), uses the right side of the following diagram to get a list of SQL Servers. And waits for a response--which should be about a second per server. But as you can read in the fine print, if SSBS has issues with this request it keeps pushing it's request up to the next protocol layer and the next, until to try to get a response. In my mind, this is "stack elevation" is probably what's going on with your request--because 1434 on UDP is most likely blocked or hindered in some way.

enter image description here

According to the documentation:

3.1.5.2 Waiting For Request From Client In the "Waiting For Request From Client" state, the server listens on UDP port 1434 for an incoming request. If the request is valid and understood, the server immediately sends an SVR_RESP response back to the client. The data content of the response depends on the request type.

AND

The response time for the SQL Server Browser service to can vary depending on the Data Access protocol that's being used, but Microsoft's documentation says the wait is a second:

3.2.2 Timers The SQL Server Resolution Protocol client MUST implement a timer for the amount of time to wait for an SVR_RESP message from the server when a CLNT_UCAST_INST or CLNT_UCAST_DAC request is sent. The timer mechanism that is used is implementation-specific but SHOULD<2> have a time-out value of 1 second.

But for some really boring reading here are all the caveats to the one second rule:

Browser, and SQL Server 2008 R2 Browser support sending information about instances of SQL Server 2000 and will send these tokens. <2> Section 3.2.2: Windows implements the timers for these two messages as follows:

For the CLNT_UCAST_INST request: Windows implementations that use Microsoft Data Access Components (MDAC) or Windows Data Access Components (Windows DAC) time out if no response is received within 1 second. If a valid response is received within 1 second, the response is passed to the higher layer. If the response is not valid, the process is repeated. Windows implementations that use Microsoft SQL Server Native Client time out if no response is received within 1 second. If a valid response is received within 1 second, the response is immediately passed to the higher layer. If the response is not valid, an error is passed to the higher layer.

For the CLNT_UCAST_DAC request: Windows implementations that use MDAC or Windows DAC do not support this request. Windows implementations that use SQL Server Native Client time out if no response is received within 1 second. If a valid response is received within 1 second, the response is immediately passed to the higher layer. If the response is not valid, an error is passed to the higher layer. <3> Section 3.2.2: Windows implements the timers for these two messages as follows:

For the CLNT_UCAST_EX request: Windows implementations that use Microsoft Data Access Components (MDAC) or Windows Data Access Components (Windows DAC) time out if no response is received within 0.5 second. If a valid response is received, it is appended to the results. If the response is not valid, it is discarded. The process is repeated until a time-out occurs. Windows implementations that use SQL Server Native Client time out if no response is received within the lesser of 5 seconds or the specified logon time-out (the default logon time-out is 15 seconds.) If a valid response is received, it is appended to the results. If the response is not valid, it is discarded. The process is repeated for a maximum time period of the lesser of 5 seconds or the specified logon time-out.

For the CLNT_BCAST_EX request: Windows implementations that use MDAC or Windows DAC time out if no response is received within 0.5 second. If a valid response is received, it is appended to the results. If the response is not valid, it is discarded. The process is repeated until a time-out occurs. There is no maximum time-out limit.

Windows implementations that use SQL Server Native Client time out if no response is received within 5 seconds and then each 1 second up to 15 seconds or to the specified logon time-out, if valid responses are not received within each respective interval. If valid responses are received, they are appended to the results; however, invalid responses are discarded. The default logon time-out is 15 seconds. <4> Section 3.2.5.4: Microsoft clients, such as Microsoft Data Access Components (MDAC), Windows Data Access Components (Windows DAC), or SQL Server Native Client, consider a SVR_RESP message to a CLNT_UCAST_EX type request to be improperly formatted if the RESP_DATA field is more than 4,096 bytes. 

Footnotes:

Please read to your heart's delight, about the SQL Server Resolution Protocol. This is where I retrieved much of my information.

https://winprotocoldoc.blob.core.windows.net/productionwindowsarchives/MC-SQLR/[MC-SQLR].pdf

1
  • Very thorough! Thank you! I don't know how to have two answers - I tried. Aug 3 '18 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.