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