SQL Server enumeration and Windows Firewall

  • Article

Another customer issue using SQL Server enumeration. 

The customer was using the EnumAvailableSqlServers() API from SQL Server 2005 SMO.  The local box had three instances of SQL Server installed: (a) a default instance of SQL Server 2000, (b) a named instance of SQL Server 2005 SQL Express edition, and (c) a named instance of SQL Server 2000 MSDE edition.  EnumAvailableSqlServers() was returning only the default instance of SQL Server 2000. 

Post https://blogs.msdn.com/sql_protocols/archive/2005/09/23/473339.aspx explains why the MSDE instance is not returned, so the question is:

Why is the local SQL Express missing? 

The answer turned out to be Windows firewall. 

The EnumAvailableSqlServers() method relies on a UDP broadcast to port 1434, and the UDP broadcast packet gets dropped if the Firewall is turned on.  If your security needs allow it you can grant a Firewall exception to the SQL Browser program and/or UDP port 1434.  You can change the scope of the exception to restrict it to the IP address of the computer itself. 

This story made me think that it may be worth summarizing the impact of Windows firewall on the various API for SQL Server enumeration. 

- To see a remote SQL Server the Firewall on the remote machine must not block the UDP packet to SQL Browser’s UDP port 1434. 

- To see a local SQL Server in the result of a network enumeration API the Firewall on the local machine must not block the UDP packet.  This applies to API calls like:

  • SMO’s EnumAvailableSqlServers,
  • SqlDataSourceEnumerator’s GetDataSources,
  • the default behavior of ODBC’s SQLBrowseConnect, or
  • the SQL-DMO’s ListAvailableSQLServers. 

- Seeing a local server in the result of an API explicitly requesting only the instances from the local machine does not depend on an exception in the Firewall.  This applies to calls like:

  • ODBC’s SQLBrowseConnect combined with a preceding call to SQLSetConnectAttr() SQLBrowseConnect setting the SQL_COPT_SS_BROWSE_SERVER attribute to the local machine in order to restrict the enumeration to the local machine, or
  • SQL-DMO’s ListInstalledInstances.

I hope this helps understand some of the results of these enumeration API. 

One more note: the reason the customer was getting the default instance even if the Firewall was blocking UDP port 1434 was a legacy network enumeration (showing default instances only). 

 

Peter Gvozdjak, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    January 15, 2007
    I have a local copy of sqlExpress and I am writing a program to find the Instance name of the servers on the network. With windows firewall on, it was not displaying the instance name.  Adding Firewall exceptions for SQLBrowser did not work.  Also TCP on Port 1433 and UDP on 1434 - both restricted to local network - did not work.  Finally after removing the local network restriction for the exceptions - the program was showing the instance names.  I presume that the database on my computer, is on the same network as the programm.  Any suggestions why I cannot apply the restrictions for local network only. Alan Williams

  • Anonymous
    April 17, 2007
    Hi - I've got a problem where only 1 of 2 SQL 2000 instances are showing, but they both have network protocols enabled, and neither are hidden - any suggestions?

  • Anonymous
    May 23, 2007
    Hello! Thank you for your site. I have found here much useful information. Thank you!

  • Anonymous
    September 06, 2007
    Acually i was not getting the list of server availbale on the network. But this article helps me to find out the solution. Thanx a lot

  • Anonymous
    June 06, 2008
    Hi all, I need to know how to connect to a remote sql server using a C# window application. Let me make, my issue more clear. I want to develop a application in C#, that can connect to any SQL server whether its local or remote using valid crendentials. I saw many appliations, that search automatically into LAN for SQL Server instances. But I want user to put either server name or ip address instead of searching for insatnces automatically. Please help me out. if there is any online resource regarding this, please put it in reply Thanks and regards Amit Ranjan

  • Anonymous
    June 16, 2008
    On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entity

  • Anonymous
    July 20, 2008
    It is a known bug since SQL server 7 that the browser server service is buggy and firewall incompatible especially in cluster scenarions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354534 https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296165

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2147157-error-when-trying-to-create

  • Anonymous
    March 05, 2009
    <a href= http://index1.reezina.ru >colibry im</a> <a href= http://index5.reezina.ru >��������� ������ qip</a> <a href= http://index2.reezina.ru >mip ���������</a> <a href= http://index3.reezina.ru >samsung sgh p520 armani</a> <a href= http://index4.reezina.ru >miranda ������� ���������</a>

  • Anonymous
    July 21, 2009
    The comment has been removed