locked
Trouble Enumerating SQL server instances with SQL 2000/2005 on network RRS feed

  • Question

  •  

    Hi,

     

    We have (after several weeks of testing in all kind of environments) send out a new version of our application to several of our customers. Within days problems where drippin in; After looking for the problem on various customer situations we found a problem which I think is rather disturbing and very odd. I'll describe the situation, on which we finally managed to recreate the problem, here.

     

    In my problem I use the following configuration:

    Windows 2003 (standard edition) AD network with 2 domain controllers, multiple Windows XP workstations, some without SQL instances, some with SQL 2000 instances, some with SQL 2005 instances and even one with SQL 7 running.

    All run a 32 bit OS.

     

    Tools to reproduce:

    ListSQLSvr application (found on SQLDev.net) to enumerate the instances.

     

    Problem description:

    --------------------------------------------------

    I am running the machine called DEV001, which has SQL 2000 (instancename DRUMIS) and SQL 7.0 (has no instancename so this is the root instance) installed.

     

    In any 'normal' situation all the runnings SQL instances are visible on the network like this:

     

    Code Block

    C:\>listsqlsvr -X

    (local);Clustered:No;Version:7.00.623
    ADM002\DRUMIS;Clustered:No;Version:8.00.194
    DEV001\DRUMIS
    DEV001\DRUMIS;Clustered:No;Version:8.00.194
    DEV002\DRUMIS;Clustered:No;Version:8.00.194
    DEV002\EXPRESS;Clustered:No;Version:9.00.3042.00
    DEV002\EXPRESS
    INSADBACKOFFICE\DRUMIS;Clustered:No;Version:8.00.194
    INSADBACKOFFICE\EXACT;Clustered:No;Version:9.00.3042.00
    INSADOFFICE\WSUS;Clustered:No;Version:8.00.194
    SUP001\DRUMIS;Clustered:No;Version:8.00.194

     

     

    When I turn the SQL 2005 Browser service off on the machine called DEV002 the list looks like:

     

    Code Block

    C:\>listsqlsvr -X

    (local);Clustered:No;Version:7.00.623
    ADM002\DRUMIS;Clustered:No;Version:8.00.194
    DEV001\DRUMIS;Clustered:No;Version:8.00.194
    DEV001\DRUMIS
    DEV002
    DEV002\EXPRESS
    DEV004\DRUMIS;Clustered:No;Version:9.00.3042.00
    INSADBACKOFFICE\DRUMIS;Clustered:No;Version:8.00.194
    INSADBACKOFFICE\EXACT;Clustered:No;Version:9.00.3042.00
    INSADOFFICE\WSUS;Clustered:No;Version:8.00.194
    SUP001\DRUMIS;Clustered:No;Version:8.00.194

     

     

    Notice that the browser service might be off on DEV002, you can still see the EXPRESS instance and a new root instance has appeared (though it doesn't exist!)??

    After restarting the Browser service all is OK again.

     

    When I turn on Hide Server in the SQL 2000 TCP/IP properties (or turn it on in the registry [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\DRUMIS\MSSQLServer\SuperSocketNetLib\Tcp] "TcpHideFlag"=dword:00000001) on the DEV002 computer something real scary is happening..

    The list looks as follows:

    Code Block

    C:\listsqlsvr -X

    (local)
    ADM002
    DEV001

    DEV002
    DEV004
    INSADBACKOFFICE

    INSADOFFICE
    SUP001

     

     

    When someone has entered a database (for example the backoffice database on INSADBACKOFFICE\EXACT) the list looks as follows (for a short moment; 5 secs or so):

    Code Block

    C:\listsqlsvr -X

    (local)
    ADM002
    DEV001

    DEV002
    DEV004
    INSADBACKOFFICE\DEVELOP;Clustered:No;Version:8.00.194
    INSADBACKOFFICE\DRUMIS;Clustered:No;Version:8.00.194
    INSADBACKOFFICE\EXACT;Clustered:No;Version:9.00.3042.00
    INSADOFFICE
    SUP001

     

     

    Notice now that ALL instances are gone and no extended information is available. In the Query Analyser and in the SQL Management Studio when browsing you'll see this as well!

    When someone is accessing a database instance it appears for a few seconds again.

     

    Since our installation and applications rely on selecting a existing instance it will fail in the above situations (or at least not showing all available instances).

     

    In my opinion this is a bug somewhere!

    Note that even when the SQL Services are stopped on DEV002 (leaving the Browser service running) it still seems to block out ALL instance on the ENTIRE network!

     

    I don't mind that one INSTANCE or even the entire MACHINE is hidden from the network, but ALL instances on ALL machines??

     

    And the SQL Browser issue also worries me a bit since it does not stop the possibily to browse the SQL instances; it removes the SQL2000 instances but adds a root instance which doesn't even exist! Also the extended info is stripped.

     

    Can anyone help me solve this/advise?

     

    Also mind that in any situation there might run a lot of computers with a lot of SQL instances and I cannot tell our customers to find which machine has the SQL TCP/IP properties set to Hide...

    It even seems that in some situations SBS 2003 does the hiding automatically on Install? And if so, when and why?

     

    Regards,

     

    Albert van Peppen

    Senior System Engineer

    Insad Grafisch b.v.

     

    Tuesday, December 4, 2007 4:24 PM

All replies

  •  

    Is there really nobody who can help me with this one?

     

    Help!

     

    Regards,

     

    Albert van Peppen

     

    Monday, December 10, 2007 11:49 AM
  • Ok, I can sort of explain the strange behavior and this will help you resolve it.

     

    First I need to explain how sql browser works in conjunction with SQL Server 2000 and 2005 named instances.  This will take a bit of time to explain but will become clear once explained.

     

    Firstly SQL 2000 introduced the concept of named instances.  With SQL 2000 each instance will attempt to perform "sql browser" services.  They do this by periodically attempting to bind to UDP port 1434, if they succeed then the instance "owns" the "sql browser" service and responds to the 1434 UDP requests.

     

    Long story short -- Customers ran into various problems with this system, it's not as reliable as having a dedicated standalone service that does the UDP 1434 listening.  For example you can have 16 instances all starting and stopping and fighting over performing the work and this introduces delays and failures.

     

    Hence in SQL 2005 we decided to create a standalone SQL Browser service.  This service is the one entity that does all the work for all instances and this simplifies the whole proccess.  However if you still have SQL 2000 instances they can "fight" with SQL Browser service.  To solve this problem we introduced a special registry key to tell the SQL 2000 instances to not try to provide browser services (you need SP4 to get this feature).

     

    Ok, back to your problem after the small history lesson.

     

    You have SQL 2000 instances that may not have the proper registry keys to disable them from providing browser service.  I know this because when you turn off SQL Browser service you still see browser response.

     

    All this information is kept in the registry under HKLM\Software\Microsoft\Microsoft SQL Server key and HKLM\Software\Microsoft\MSSQLServer key.

     

    Three things you need to do:

     

    1. Investigate disabling browser for all SQL 2000 instances, get these disabled.

    2. Ensure SQL 2005 SQL Browser service is running, let it do the browser work.

    3. Clean out phantom instance from registry.

     

    Number 3 is you most likely have an old registry key for some instance that was improperly uninstalled.  This is why you see the phantom instance in browser response.

     

    You may want to enlist the help of Microsoft Support Services to get this cleaned up.

     

     

    Monday, December 10, 2007 7:55 PM
  • Hi Matt, thanks for your response.

     

    I have a bigger problem than that; The problem was reported to us by various of our customers.

    It is not really a problem that we, as ISV, can solve.

    But I (finally) could reproduce it. With the background notes you provided it is now very clear to me what the problem is.

     

    But what the bigger problem is: What do I tell my customers?

    Isn't there a hotfix or something available which the customer can download / get through Windows Update?

    It is very unlikely that we should fix this for all our customers; it will propably mean that we will recommend all users to forget about SQL 2005 an go back to SQL 2000 (although a lot of the customers already are fully over to SQL 2005).

     

    Not to mention the issues with customers running SBS 2003 or SBS 2003 R2 regarding the SQL 2000/2005 issues (which also encounter the problem as described).

     

    I have written a analyse tool which (when run as administrator) 'sniffs' the network and reports all SQL instances found on each machine (including the SQL version and wheter or not the instances are presumed 'hidden').

     

    If you can provide the exact registry key (as you described) for me I can also check to see if it is set or not.

     

    This analyse tool is part of our Support tool and is installed at the customer machine so our support department can run it remotely with the customer.

     

    Are you sure Microsoft Support Services can help me with this? (Since the problem lies with our customers configurations, not in our applications?)

     

    Regards,

     

    Albert van Peppen

     

     

     

    Tuesday, December 11, 2007 5:15 PM
  • To be honest we currently don't have a 100% accurate way to enumerate all instances on a remote machine because the scheme we use is not 100% reliable over all sub-nets.

     

    Our primary detection method uses UDP 1434 packets, which are commonly screened by firewalls, or not routed or bridged in every environment, etc...

     

    We also use a WINs style broadcast (NetServerEnum) which by default does not pass out of a sub-net.

     

    There are alternate means of detecting SQL instances if the servers are in a domain, you can query active directory and you can also sniff the SPNs created by SQL Server (for use with Kerberos).  These rely on their being a domain.

     

    What I think happens occasionally is you have a failed SQL instance install or remove, this leaves the phantom instances in the registry.  

     

    I suspect SQL 2000 instances when they provide "sql browser" service may ignore newer services because of changes in the registry format.

     

    Let me explain the registry keys:

     

    This first key is used to disable all SQL 2000 instances from providing "sql browser" services.  So it is important IF you have SQL 2005 installed, that this key is set to Yes and you enable and run the SQL Browser service.

     

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server]
    "IsListenerActive"="Yes"

     

    Also note there is another key value below this one, SsrpActiveServer.

     

    This key should be blank if you have any SQL 2005 instances on the machine and are using SQL Browser service.  This key shows the name of the current instance that is performing "sql browser" services if you have SQL 2000 instances doing the work.

     

    Under this same key is a key value named "InstalledInstances".  InstalledInstances should have a space delimited list of all the installed instances on the machine.  I have seen cases where a bad install leaves the key with a phantom instance name in it, so you should check that this key mirrors exactly the list of installed instances on the machine.  The default instance will also be listed here named "MSSQLSERVER".

     

    Now if you have a default instance, then what happens is the browser service looks under this key:

     

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]

     

    The version you see coming back in response is read here:

     

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion]
    "CurrentVersion"="9.0.1344.0"

     

    Protocol information is read here:

     

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib]

     

    Our algorithm works as follows, read InstalledInstances key to get list of instances.  Add MSSQLSERVER to the list if not found.  Next search for registry keys for these instances and if they are found then we report information about the instances. 

     

    I'll follow up with more info in a few hours I have a meeting to go to first.

     

     

     

     

     

     

     

    Tuesday, December 11, 2007 5:53 PM
  • This is very usefull info to get more insight on the problem.

    The analysing as you describe is pretty much what I already do.

    I can check the IsListenerActive entry also to make sure SQL 2000 isn't interfering..

     

    More info on this is always welcome! Smile

    And how does this behave in combination with SQL 2008 (this will become a issue very shortly i think)?

     

    Does this entry only work for SQL 2000 / SP4 or does it also work on SQL 2000 / SP3.

     

    The reason for this is because we have customers running a marked version MSDE2000. This was something Microsoft advised at the time, but now it only works agains us since the GUID isn't compatible with the standard MSEE2000 and therefore SP4 cannot installed on these systems Sad

     

    Are there other issues why SQL 2000 / SP4 is required (related to the problem that is)?

     

    I also noticed that when I make a network mapping to a particular machine that runs both SQL 2000 and SQL 2005 (both hidden Wink ) I always see the SQL 2005 instance (although it is hidden!).

    No extended info available for this instance though.

    I recon that is because the SQL 2005 browser service is never used since SQL 2000 browser service is in place here..

     

     

    Regards,

     

    Albert van Peppen

     

    Wednesday, December 12, 2007 1:05 PM
  • Yes I found there are a few inconsistencies with SQL 2005 and 2000 browser service.

     

    Yes, SQL 2000 SP4 is needed because this is when we introduced the IsListenerActive flag, older versions will not honor the flag.

     

    There is a special MSDE SP4 will that not work for the customer? 

     

    Another thing that can cause the inconsistent behavior is access rights to the registry keys.  If the running service does not have sufficient rights, then it cannot read the values of the keys.   I remember working with a customer a long time ago that had this issue.  They had 16 SQL 2000 named instances all running under different domain accounts.  Some of the domain accounts did not have access to the registry keys.  If  the instance that had the bad domain account and started performing browser service, then a different sub-set of the instances would show up.  This is why it is better to use SQL 2005 SQL Browser service, you always have one account.

     

    Wednesday, December 12, 2007 5:08 PM
  • As said, some years ago we have created a special version of MSDE 2000 whith a unique GUID for productcode. This was at the time 'a good policy for ISVs', if i recall correctly. But since the productcode differs from the original productcode of MSDE the updates cannot be installed. The only way to do so is repacking MSDE 2000 updates with our GUID productcode.

    It would be nice if we could reverse this, so it is compatible with the standard MSDE, without much problems.

    The reason at the time wast that standard only 16 instances are supported and as ISV you might want to be sure a unique instance is available and therfore using a unique GUID productcode adds a unique instance. (See also KB 314131)

    Nowaday we know this was never needed but damage already has been done (it is installed with a large numbers of our customers) And uninstalling and reinstalling the original MSDE to fix this is not a good option.

     

    Can you tell me how to detect what browser service is running (is active) on a machine?

    Or isn't there a conclusive way to detect this..

     

    The problem with registry access is clear. This can be scanned for but is in my case not a problem (as far as I can tell).

     

    BTW.

    The entry at [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion] /

    "CurrentVersion" does not return the current version! It returns the version at installation time.

    Hotfixes and service packs (eg. other buildnumbers) are not in here!

    In the "CSDVersion" (SQL 2000 at least) the correct version is found.

    Thursday, December 13, 2007 9:13 AM
  •  

    Please assist, the issue with SQL 2005 Browser and SQL 2000 Server Service is understood.

     

    Our problem is with networked 2000 instances and SQL Express. The SQL 2000 machines (Standard & MSDE) do not have SQL 2005 or Express installed only 2000. When a SQL Express computer is put on the network with the SQL Browser service running almost all SQL 2000 machines lose sight of the other SQL 2000 instances. The second the

    SQL Browser is turned off on the SQL Express box the SQL 2000 machines can see each others instances.

    It appears that the response from the SQL Express SQL Browser causes the SQL 2000 machine to stop listening for responses. Once in a while one of the SQL 2000 instances will show up with the SQL Browser active on the network and it is my belief that it is because that response made it in before the SQL browser response. Please help as this does not appear to be a recognized issue. I'm assuming there aren't many sites running as many named instances on individual machines like we do.

     

    THANK YOU!

    Friday, December 14, 2007 7:13 PM
  • Hi Eskeban,

     

    This is exactly the case with me (and my customers) although it is a bit more complicated than you state; see previous posting for more trouble in this area.

    I'm glad I am not the only one with this problem Smile

     

    I hope the problem can be solved in an easy way..

     

     

    Sunday, December 16, 2007 9:36 PM
  • Hi Matt,

     

    I hope you can also help me on my scenario.

     

    I have created a .net routine using DbProviderFactory..GetDataSource to return list of sql server instances.   I run the .net routine on a Windows XP Professional 64bit machine which has 2 instances: mymachine\SQLExpress (Express Edition) and MSSQLSERVER (Developer Edition (64bit))
     
    If SQL Browser Service is not running, my routine will return MSSQLSERVER.
     
    If SQL Browser Service is running, my routine will return mymachine\SQLEXPRESS.
     
    Do you have any idea why it won't return both instances?
     
    I have also tried ListSQLSvr and it returned different results:
     
    If SQL Browser Service is not running, ListSQLSvr will not return the 2 instances at all.
     
    If SQL Browser Service is running, ListSQLSvr will return mymachine\SQLEXPRESS.
     
     
    Thanks,
     
    Peter
    Tuesday, December 18, 2007 11:19 PM
  •  

    Hi Peter,

     

    I've had some issues with 32/64 bits instances; did you check this thread: Trouble Enumerating SQL server instances 32 bit and 64 bit mode

     

    It might help solving your issues as well Wink

     

    Regards,

     

    Albert van Peppen

     

    Thursday, December 20, 2007 7:27 PM
  • Hi Albert,

     

    I know very little about ODBC API.  But I'm not surprised that the ODBC API is not working too well since ODBC is sort of obsolete.  ListSQLSvr.exe is using the SQLBrowseConnect function of ODBC API so I can understand why it has problem.  But the problem also exists even I'm using .NET class.

     

    As of now, I have found the possible cause from this link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198078&SiteID=1

     

    I wonder how the following .NET classes obtain the list of SQL Server Instances:

    • SmoApplication.EnumAvailableSqlServers
    • DbProviderFactory.CreateDataSourceEnumerator
    • SqlDataSourceEnumerator
    • ManagedComputer (Smo.Wmi)

    From what I have read so far, I see the following ways used to detect instances:

    • UDP 1434 packets
    • NetServerEnum
    • query active directory
    • sniff the SPNs created by SQL Server
    • search local registries

    Which way does SQLBrowseConnect use?

     

     

     

    Thursday, December 20, 2007 8:04 PM
  • AFAIK SQLBrowseConnect uses the UDP packets.

    I also remember something that the .NET classes where wrapped around the ODBC layer (which to my knowledge is anything but obsolete).

    Only difference might be the native clients. But I'm not using .NET nor native client access.

     

    NetServerEnum enumerates the servers in the network (most likely also using SMB or LDAP) and then you have to figure out which servers are using what instances ???

     

    Quering AD should follow the same way I figure.

     

    Sniffing SPNs or searching local (or even remote) registries require appropriate rights, especially when scanning remote registries. Looking in the registry might give you incorrect instances; you should validate each one.

     

    And, as described in the article you mention, there is an issue when combining SQL 2005 (express or any other edition) and SQL 2000 (MSDE and any other edition).

     

    I was hoping in this thread to get more technical inside info considering this. And maybe figuring out a way to get this working anyway.

     

    But Matt has a long meeting or might be told to be silent on certain problems

    Thursday, December 20, 2007 11:13 PM
  • I understand the issues when your running Express and 2000 MSDE on the same machine but there is a seperate issue on networked machines. I captured all packets in and out of an Express box (only express installed) and a 2000 box (only 2000 installed) and could see the broadcast from the 2000 box go out properly but stop listening when the response came back from the Express box. If the Express box has the browser service off the 2000 box continues to listen and gets the reponses from the other 2000 boxes and a 2005 Standard server. I don't understand what the difference is between the response from 2005 Standard and Express. I'm out of the office but will send the responses from 2005 and Express for review. Summary: My issue is only a problem with the responses from the browser service in Express.

     

    Friday, January 4, 2008 10:34 PM
  • The situation is confusing I agree.  Perhaps I need to write up a KB or a blog about the subject.

    Thursday, January 10, 2008 7:51 AM
  • Ahh finally back from my long meeting!   Hey, I have to keep bread on the table so I had to focus to meet some deadlines, you know how it goes.

     

    Today I am experimenting with mixing SQL 2000 MSDE, SQL 2005 Express and a full blown SQL install with SQL Browser service running to see what happens when you mix the three.

     

    Ok, I agree scanning AD is less than effective for identifying sql instances.  The NetServerEnum API will tell you which machines have SQL Servers running on them but will not give you port for instance.  I am using my brain today.  So how can a remote machine determine the list of instances and ports?

     

    The "correct" way is to enable sql browser services.  This means open up UDP port 1434 on the server machine firewall to let UDP 1434 traffic through for starters.  Secondly this means enabling and running the SQL Browser service if found on the machine.   If the SQL Browser service is not found, then you are using SQL 2000 only and each individual SQL Server instance that is running will act as the sql browser service.

     

    If you have mixed SQL 2000 and 2005 services, then Microsoft recommends running the SQL Browser service.  I am saying this as well because I know it will work better.

     

    Also, if you have SQL 2000 SQL Server services running get them patched!  Earlier versions did not work well with SQL Browser service, you need at least SP2, but get latest patches for best security, etc...

     

    Now, how can you tell if UDP 1434 requests are working?  A few ways are to use Microsoft Nework Monitor 3.1 tool (free download).  Ethereal sniffer is also very good (3rd party but free as well).  I like filtering on client and server IP address and UDP port 1434 to make it easy to see just the traffic I want.

     

    Another nice gadget is TDIMon from sysinternals site and also Regmon from sysinternals site.  A key thing to note is when a new UDP request comes into the SQL Browser service it scans the registry keys to look up the instance information, this is why Regmon is a good tool, it tells you that the request came in because you see the service reading the keys.  TDIMon is hard to find anymore but it let's you know if the UDP packet came into the server but then got filtered by the firewall.  Network sniff will show this too so TDIMon is not as necessary.

     

    Ok, with UDP there are problems in general if you have bridges + routers they may filter out this traffic as well.  If you suspect this then network sniff is the way to go.  Sniff on client AND server machines simultaneously this will tell you if the packet left the client and if it got to the server. 

     

    But if everything appears to be working fine and you are seeing cases where not all the instances are showing up, this is not a network problem but clearly a problem on the server side where the process that is performing sql browser services is not serving up what you want.

     

    This can happen for several reasons.  You can eliminate many of these using Regmon.  Run regmon and if you see Access Denied going to some of the registry keys where the instances reside (under HKLM\Software\Microsoft\Microsoft SQL Server and HKLM\Software\Microsoft\MSSQLServer) then it is simply a permissions issue.  You need to ensure that all SQL Server services and the SQL Browser service have permissions to read the registry keys.  These can get out of sync if you have a bad install for example, the installer is responsible for granting permissions to these keys.

     

    Secondly, if the keys are misconfigured, then the browser service may fail to read the protocol information for each service.  This is a tougher one to understand without knowledge of how the keys work.  I explained a bit on how the keys worked in my previous post.

     

    Today I am going to install MSDE 2000, SQL Express 2005 on top of my machine with SQL Server 2008 already installed with SQL Browser service.   Then I'm going to play around with starting and stopping each one to see the resulting behavior and try to see if we have a problem here.  I'll post back it may take a few days to finish up.

     

     

     

     

     

     

     

    Monday, January 14, 2008 9:15 PM
  • Note to self.  When attempting to configure MSDE 2000 to listen on tcp-ip, use the svrnetcn.exe utility (Start | Run) to get to this. 

     

    To tell if something on the server box is performing sql browser services, run:

     

        netstat -aon | findstr 1434

     

    You should see something like below:

     

      UDP    0.0.0.0:1434           *:*                                    1972

     

    Then go look in Task Manager for process with PID 1972 (note Task Manager View | Select Columns... ensure PID is checked).  The process should either be sqlservr.exe or sqlbrowser.exe.  If it is something else, you have another problem you need to resolve, why is some random process hanging onto UDP 1434 port.

    Monday, January 14, 2008 9:35 PM
  • Ok, results of my testing thus far.

     

    Running MSDE 2000 (instance name MSDE), SQL Express 2005 (instance name SQLExpress) and SQL Server 2008 (default instance MSSQLSERVER).

     

    I shut down SQL Browser service, set each instance to only allow TCP protocol and use ports 5555, 6666, and 1433 (default instance).  I restarted all three SQL Servers.  This allows MSDE 2000 to perform browser services since the SQL Browser service is shut down.

     

    I found MSDE 2000 displayed all three services but sent along named pipe information as well even though named pipes were not enabled.   Reason for this is in SQL 2005 and later we have a reg key called Enabled that tells if the protocol is enalbed or not, the MSDE 2000 does not know about this key and instead takes the existance of the key as proof of protocol enabled.

     

    But for the tcp-ip it served up everything fine, including correct port number.

     

    Next I disabled TCP-IP for all servers and restarted all servers.

     

    Ok, no response here because MSDE 2000 checks if all external (tcp-ip and named pipes) protocols are enabled and if so then performs browser service, otherwise does not listen.

     

    Enabled tcp-ip on only the MSDE 2000 instance and restarted it.

     

    Bingo!  MSDE displays information about all three services including port information for tcp-ip, even though the protocols are not enabled.  Note for this to work you need to ensure

    HKLM\SOFTWARE\Microsoft\Microsoft SQL Server

    \IsListenerActive key value is set to No.  This key tells SQL 2000 instances whether or not to perform browser services.  If it is set to Yes, then SQL 2000 with appropriate SP will not perform browser services at all.

     

    Once you install SQL 2005 or later, this key is set to Yes by setup.

     

     

     

     

    Monday, January 14, 2008 9:52 PM
  • Hi Matt,

     

    This makes sense for multiple instances and versions running on the same computer but doesn't help with

    the issue concerning networked machines that only have one version of SQL per computer. I can't stress

    enough that SQL 2005 browsers response does not cause a problem with the MSDE 2000 computers. Only

    SQL Express is causing this issue on our network. Literally the second the 2000 boxes receive a response

    from SQL Express they stop listening for other responses. When the express browser service is off or blocked

    the 2000 boxes will continue to listen and find all of the 2000 and 2005 standard instances. All of the systems

    are on the same subnet and the problem is reproducable every time. From what I can see there is no competition

    between browsers. If it would be helpfull I can send packet captures from Ethereal to demonstrate the

    responses from sides. Why is there a difference between the 2005 Standard and Express browser service?

     

    Thanks,

    Kevin (Eskeban)

    Monday, January 14, 2008 10:46 PM
  • Ok, when MSDE 2000 is performing sql browser services, it will display information for all instances, even in some cases where the protocols are enabled in 2005 and later.

     

    But if you restart the 2005 instances, this problem goes away I found.

     

    The reason for this is SQL 2000 uses a separate part of the registry that we call the Shiloh mirror hive.

     

    You can see this by using regmon.  Set IsListenerActive key to No, then stop SQL Browser service and restart SQL 2000 instance.

     

    SQL 2000 browser will check:

     

    HKLM\Software\Microsoft\Microsoft SQL Server = M1

    HKLM\Software\Microsoft\MSSQLServer=M2

     

    1. M1\InstalledInstances to get list of instances.

    2. For each entry in list other than MSSQLServer it will look under M1\<InstanceName>\MSSQLServer\SuperSocketNetlib key for instance info.

    3. For default instance MSSQLServer it will look under M2\MSSQLServer\SuperSocketNetlib.

     

    These are different keys than SQL 2005 browser will look at.  Note that these keys are kept in sync when each individual SQL 2005 or later instance is restarted.

     

    Next if you set IsListenerActive to Yes, re-start all SQL Server instances, then start SQL Browser service you will see:

     

    1. M1\InstalledInstances to get list of instances.

    2. Look under M1\Instance Names\SQL to find the location of instance key.

    3. Look under M1\<InstanceKey>\MSSQLServer\SuperSocketNetlib for instance specific protocol info.

    4. Then perform lookup on the "Shiloh hive" keys as SQL 2000 browser does as well and merge in the result.

     

    So the 2005 algorithm is a bit different, it has an extra layer of indirection and it performs both checks.

     

    I was able to get SQL Browser to show information about SQL 2000 MSDE instance but I had to go in and manually grant permissions to these keys.  I discovered this using RegMon, I saw "ACCESS DENIED" going to these keys.

     

    Note the latest tool is Process Monitor from sysinternals, this performs same logging as Regmon.

     

     

    Monday, January 14, 2008 11:26 PM
  • Hi Kevin I'm looking into your issue as well, just responding to other stuff first.

    What patch level are your SQL 2000 servers?  You can run "select @@version" to get this.

     

    Monday, January 14, 2008 11:29 PM
  • Thanks Matt!! There is a mix of SP3a and SP4, I'll send the specific version numbers and wait for your response. Your help is greatly appreciated!

    Monday, January 14, 2008 11:32 PM
  • Here is the summary of what's on the subnet. Please note there are about

    20 systems and most are running 2000 SP3a. There is one 2005 standard

    and one 7. I'll send the express version information when I'm back in the office

    tomorrow. There are no issues with the versions below, all systems can see the other

    instances until the express browser service is on...

    Microsoft SQL Server  7.00 - 7.00.842 (Intel X86) Mar  2 2000
    Microsoft SQL Server  2000 - 8.00.194 (Intel X86) Aug  6 2000
    Microsoft SQL Server  2000 - 8.00.760 (Intel X86) Dec 17 2002
    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) May  3 2005
    Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006

    Tuesday, January 15, 2008 12:39 AM
  • One follow up question.  When you say "all systems can see the other

    instances until the express browser service is on" what specific tool are you using to list the instances?  SQL Server Managment Studio or Enterprise Manager?  Something else?

    Tuesday, January 15, 2008 12:43 AM
  • 2000 - Enterprise Manager, SQL Service Manager, OSQL, and a VB6 app (I'll need to check the code and send.)
    Please note, when the express browser service is on the methods above stop seeing the other instances but the 2005 standard box (using SQL Server Management Studio) and the express box (using sseutil - listsrv remote) will continue to find the 2000 instances.

    Tuesday, January 15, 2008 12:58 AM
  • Hi Matt,

    Here is the Express version information:

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007

    Thanks!
    Tuesday, January 15, 2008 1:58 PM
  • Hi Matt,

     

    I wonder whether you know the issue mentioned in this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198078&SiteID=1 has been fixed or not.

     

    Which .net class do you suggest me to use for enumerating SQL Server Instances? Michiel Wories has suggested me to use ManagedComputer class.  I'm using DbProviderFactory.CreateDataSourceEnumerator currently but it has the issue mentioned in that thread.  Also, it returns nothing if there is no network connection.

     

    Thanks,

     

    Peter

     

     

    Tuesday, January 15, 2008 6:46 PM
  •  

    Hi Matt,

     

    The situation as described in the initial message seems straight forward to test I would say.

    The listsqlsrv tool is also showing the issues.

     

    Also remember to fiddle around with hidden instances an stopping browser servers etc.

     

    More technical insight (examining SQL sqlsvr.exe / sqlbrowser.exe sources perhaps?) is required to get a better understanding of WHY this is failing and HOW to fix it...

     

    And as mentioned (and you've mentioned yourself) SQL 2008 is becoming an issue also

     

     

    Regards,

     

    Albert van Peppen

     

    Tuesday, January 22, 2008 2:44 PM
  • Hi Matt,

     

    Anything new on this?

     

    Thanks,

    Kevin

     

    Wednesday, January 30, 2008 3:29 AM
  • Hi,

     

    Does anyone out there able to help me (us) with this problem?

    It looks like Matt has given up

     

     

    Thanks,

     

    Albert

    Monday, March 10, 2008 3:48 PM
  • We had a similar issue with sqlserver 2005 Express in Windows 2000 professional.

    The list of instances toke about 5 minutes after restarting the service to show again correctly.

    We have run out of solutions, when a coworker reinstaled MDAC 2.8 SP1 on the machine and solved all the issues with the sqlbrowser.

     

    Hope this helps.

     

    Anibal

     

     

     

     

    Thursday, June 12, 2008 5:56 PM