SQL Server enumeration returns SQL Express but not MSDE

  • Article

I have recently came across the following problem: a customer was using EnumAvailableSqlServers() API from SQL Server 2005 SMO to enumerate installed SQL Servers.  The customer's box contained both an installation of SQL Server 2005 SQL Express edition and an installation of SQL Server 2000 MSDE edition.  (after resolving a separate issue I'll describe in a separate blog) The API returned the SQL Express but not the MSDE instance.  Wonder why? 

The enumeration is done by querying the SQL Browser service on the box - a new service in SQL Server 2005 - which was installed together with the SQL Express instance.  By default SQL Browser returns all SQL Server 2005 instances, including SQL Express.  However, to preserve backward compatibility with SQL Server 2000 it returns only those SQL Server 2000 instances that have network protocols enabled, and the MSDE instance had them disabled (default for MSDE). 

If you want to hide a SQL Server 2005 instance from being enumerated you can use an explicit configuration flag instead of relying on the network protocol settings - HideInstance.  If set the instance will be excluded from enumeration regardless of network protocol settings. 

To set HideInstance for a SQL Server 2005 instance, e.g. SQLEXPRESS:

- open SQL Server Configuration Manager. 
- right-click Protocols for SQLEXPRESS
- choose Properties
- set HideInstance to "Yes"
- press OK. 

Peter Gvozdjak

Software Design Engineer, SQL Server Protocols

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

Comments