Re: Problem with ListAvailableSQLServers in vb 6

From: Gert E.R. Drapers (GertD_at_SQLDev.Net)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 10:28:16 +0100

If you have no network this is expected, since the enumeration is based on a
UDP (TCP) broadcast. Since this is a broadcast it normally only sees
computers in the same subnet, since most routers are configured not to pass
on UDP broadcast requests.

Besides that there is a response time issue. After the request is issued the
client only waits for a certain period of time on UDP replies from the
servers that received the UDP broadcast request.

On the local machine the API does a Registry scan, which is why local
services should show up when no network is enabled. MSDE has different
Registry entries that is why these do not show.

If you want to use the same API as SQL-DMO you need to use the
ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
the OLE-DB based API, which theoretically should provide the same results,
but you never know.

On Win98 I think they only enumerate local instances as far as I know.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.

"Allcomp" <marc@nospam.allcomp.be> wrote in message
news:41da4402$0$316$ba620e4c@news.skynet.be...
> Hello,
>
> My enabled networks protocols are Named pipes and Tcp/IP
>
> With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
> have no server). I have exactly the same error with Osql -L
> I use WinXP SP2 without firewall for my tests.
>
> With SQL Server 2000, a Win98 computer is in the list
>
> I have also seen something strange...
> I make the ListAvailableSQLServers
> If I have a return count of 0, then I use
> the object
> SQLDMO.SQLServer2
>
> Set oServer = New SQLDMO.SQLServer2
> Set ListSvr = oServer.ListInstalledInstances
> will return the local computer when no network is enabled.
>
>
> Thank you
> Marc Allard
> Allcomp
>
>
>
>
>
>
>
>
> Andrea Montanari wrote:
>> hi Marc,
>> "Allcomp" <marc@nospam.allcomp.be> ha scritto nel messaggio
>> news:41d95753$0$329$ba620e4c@news.skynet.be
>>
>>>Hello,
>>>
>>>I have got a very strange problem...
>>>
>>>In VB6, in the beginning of my application, I make a
>>>ListAvailableSQLServers to see what MSDE servers are on the network
>>>for my application.
>>>The problem is that if a computer is disconnected from the network,
>>>but has MSDE working (a laptop) then the ListAvailableSQLServers will
>>>return no server, even if there is a local server.
>>>Is there something to do to have it?
>>>
>>>PS : In VB6, I use
>>>
>>>dim ListSvr as collection
>>>Set ListSvr = SQLDMO.ListAvailableSQLServers
>>>
>>>and the count is 0
>>>
>>>I have also tried with
>>>
>>>Dim oServer As New SQLDMO.SQLServer2
>>>Dim oNameList As SQLDMO.NameList
>>>
>>>Set oNameList = oServer.Application.ListAvailableSQLServers '
>>>
>>>and oNameList.Count is still 0
>>>
>>
>>
>> can you please verify your local MSDE instance has network protocols
>> enabled
>> via Server Network Utility (svrnetcn.exe)?
>> if network protocols are disabled (default for MSDE installation), the
>> relative instance will not be enlisted in the broadcast call for server
>> enumeration..
>>
>> as regard ListAvailableServer method provided by SQL-DMO object model,
>> I've
>> found during my (little) experience, this sort of rules:
>>
>> ListAvailableServer uses ODBC function SQLBrowseConnect() provided by
>> ODBC
>> libraries installed by Mdac;
>> this is a mechanism working in broadcast calls, which result never are
>> conclusive and consistent, becouse results are influenced of various
>> servers's answer states, answer time, etc.
>>
>> Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
>> broadcast, on which SQL Servers respond (Default protocol for SQL Server
>> 7.0), while in SQL Server 2000 the rules changed, because the default
>> client
>> protocol changed to TCP/IP and now a UDP broadcast is used, beside a
>> NetBIOS
>> broadcast, listening on port 1434:
>> which is using a UDP broadcast on port 1434, if instance do not listen or
>> not respond on time they will not be part of the enumeration.
>>
>> Some basic rules for 7.0 are:
>> - SQL Servers have to be running on Windows NT or Windows 2000 and have
>> to
>> listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will
>> never
>> show up, because they do not listen on Named Pipes.
>> - The SQL Server has to be running in order to respond on the broadcast.
>> There is a gray window of 15 minutes after shutdown, where a browse
>> master
>> in the domain may respond on the broadcast and answer.
>> - If you have routers in your network, that do not pass on NetBIOS
>> broadcasts, this might limit your scope of the broadcast.
>> - Only servers within the same NT domain (or trust) will get enumerated.
>>
>> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
>> default protocol has been changed to be TCP/IP sockets and instead of a
>> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same
>> logic
>> still applies roughly.
>> - SQL Server that are running
>> - SQL Server that listening on TCP/IP
>> - Running on Windows NT or Windows 2000 or Windows 9x
>> - If you use routers and these are configured not to pass UDP broadcasts,
>> only machines within the same subnet show up.
>>
>> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to
>> have
>> .ListAvailableServer method to work properly, becouse precding release of
>> Sql-DMO Components of Sql Server 2000 present a bug in this area.
>>
>> Courtesy of Mr. Gert E.R. Drapers
>> further Information at
>> http://sqldev.net/misc.htm
>>
>> The Service Pack 3a introduced some new amenity in order to prevent MSDE
>> 2000 to be hit by Internet worms like Slammer and Saphire virus and to
>> increase security, so that Microsoft decided to default for disabling
>> SuperSockets Network Protocols on new MSDE 2000 installation.
>> Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening
>> on
>> UDP port 1434 when they are configured to not listen on any network
>> protocols. This will stop enlisting these servers.
>>
>> The latest problem has been added by Windows XP service pack 2, which
>> implements a strong protection of the local computer closing all ports
>> for
>> incoming and outgoing connections, requiring to manually open the desired
>> IP
>> port in order to allow external remote connections

Relevant Pages

  • Re: A way to disable reception of broadcast UDP?
    ... > accepting broadcast UDP is a security flaw unless the higher proto ... I know this doesn't address your question regarding the stack, ... or a connected network. ...
    (freebsd-net)
  • Re: WB, UPN networks to merge!
    ... CBS Corporation and Warner Bros. ... Entertainment Form New 5th Broadcast ... Each Company to Hold 50% Interest In The CW Television Network ... New Television Network to Utilize the Best Programming from CBS ...
    (rec.arts.tv)
  • TNT wants to be the seventh (sixth? fifth?) network
    ... TNT takes on broadcasters ... Network aims for equal advertising prices ... push is on to get the same advertising prices that the broadcast webs ... "The Closer" has been a promotional platform for other TNT originals ...
    (rec.arts.tv)
  • Re: wireless router password security
    ... But please omit the step where disabling SSID broadcast. ... trying to connect to someone else's network. ... someone to connect to my wireless router. ...
    (alt.computer.security)
  • Re: Unsecured wireless network
    ... network to access it. ... Disable broadcast SSID feature. ... The SSID is used to assign an identifier to the wireless network ...
    (microsoft.public.security)