SQL Server enumeration and Windows Firewall

SQL Server enumeration and Windows Firewall

  • Comments 10

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 http://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

Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • 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

  • 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?

  • Hello! Thank you for your site. I have found here much useful information.

    Thank you!

  • 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

  • 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

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

  • 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

  • PingBack from http://www.keyongtech.com/2147157-error-when-trying-to-create

  • <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>

  • SQL Server – Driver Issues Resolver

    Download the tool to remove discrepancy in SQL Server 2005 / 2008 like Missing foreach enumerators (File enumerator, Item enumerator), Missing OLE DB or Ado .Net Drivers.

    http://www.sqllion.com/2009/07/sql-driver-issues-resolver/

    Missing Foreach Enumerators

    Missing OLE DB / ADO .net drivers

    Missing Tasks or Components

Page 1 of 1 (10 items)