enumerate MSDE instances on Network

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Elmo Watson - 26 Oct 2004 22:23 GMT

I use SQL DMO - .ListAllAvailableServers - - this is fine, except it only
retrieves SQL Servers - not any instances of MSDE on the network.

As I understand it - MSDE runs on Port 1434 instead of 1433, so they don't
broadcast.....

Is there anyway to add instances of MSDE with our without DMO?

Reply to this Message

Andrea Montanari - 27 Oct 2004 11:07 GMT

hi Elmo,

> I use SQL DMO - .ListAllAvailableServers - - this is fine, except it
> only retrieves SQL Servers - not any instances of MSDE on the network.
[quoted text clipped - 3 lines]
>
> Is there anyway to add instances of MSDE with our without DMO?

MSDE default instances alway run on TCP/IP 1433 (if TCP/IP protocol is used
at all)... named instance do not run on TCP/IP 1434 but a server service
runs on UDP 1434, redirecting all connections to the dyanamically assigned
port used by each named instance... this when dynamic port management has
been set
if static port option has been set, then UPD 1434 is no longer needed but an
alias has to be configured for each remote pc (via Client Network Utility
[cliconfg.exe]) to map the remote server to the static assigned port

but I do think the problem is due to the fact MSDE installs by default
disabling network protocols "in toto", allowing local connections only...
this can be modified both at install time, passing the
DISABLENETWORKPROTOCOLS=0
parameter to the setup.exe boostrapper installer, and/or later, after
installing the instance, using Server Network Utility (svrnetcn.exe),
re-enabling the desired network protocols...

this can be done via SQL-DMO too, like

Dim sMultiString As String
sMultiString = "np tcp"

Dim oReg As SQLDMO.Registry2
Set oReg = objServer.Registry
On Local Error Resume Next
oReg.SuperSocketList = sMultiString
lErr = Err.Number
sErr = Err.Description
On Local Error GoTo 0

Set oReg = Nothing

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Reply to this Message

Elmo Watson - 28 Oct 2004 16:48 GMT

I tried several of your suggestions - - still no further along, though I've
learned quite a bit more.....tried (after removing MSDE) - installing MSDE
with DISABLENETWORKPROTOCOLS=0. But - it didn't work. Still no broadcast.

Does Server Network Utility get installed with MSDE? (can't tell - I also
had SQL Server installed on my test machine, previously). Anyway - I tried
using it, selecting the MSDE instance - adding TCPIP to the protocols -
restarting the services - and it still didn't broadcast - - -

I'm not a great DMO guru - so I'm not exactly sure how your code works -
can't fgure out how objServer is assigned.....can you further go into a
little more detail concerning it?

Thank you very much.

> hi Elmo,
> > I use SQL DMO - .ListAllAvailableServers - - this is fine, except it
[quoted text clipped - 36 lines]
>
> Set oReg = Nothing

Reply to this Message

Andrea Montanari - 28 Oct 2004 17:47 GMT

hi Elmo,

> I tried several of your suggestions - - still no further along,
> though I've learned quite a bit more.....tried (after removing MSDE)
[quoted text clipped - 12 lines]
>
> Thank you very much.

just a little disgression about server elisting....
SQL-DMO exposes the ListAvailableServer method of the Application object for
enlisting SQL Server instances over the lan....

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.

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.

regarding the code... I apologize for it's partialilty...
objServer is just SQLDMO.SQLServer object... that's to say

Dim objServer As SQLDMO.SQLServer as you probably did yourself...
connect to the desired server, then execute the registry modification via
the SQLDMO.Registry2 instantiated variable, using it's .SuperSocketList
method...
please restart the service after modifying that kind of registry settings...

Server Network Utility (svrnetcn.exe) is provided with MSDE installation
too, while Client Network Utility (cliconfg.exe) is provided by MDAC
installation

regards

Signature

Reply to this Message

Elmo Watson - 29 Oct 2004 01:13 GMT

I've tried it both ways (using Coding you gave/below) and using the Server
Network Utility (enabling TCPIP) - - and no broadcasting - - here's the code
I used (no errors/stopped and then restarted service):
Dim sMultiString As String
sMultiString = "np tcp"
Dim objServer As SQLDMO.SQLServer
Set objServer = New SQLDMO.SQLServer

objServer.Connect "computerName-c\NewServer", "myUID", "MyPWD"

Set oReg = objServer.Registry
On Local Error Resume Next
oReg.SuperSocketList = sMultiString
lErr = Err.Number
sErr = Err.Description
On Local Error GoTo 0
MsgBox lErr & "/" & sErr
Set oReg = Nothing

BTW - SP3 is installed

I must be doing something wrong - - any ideas?

> hi Elmo,
> > I tried several of your suggestions - - still no further along,
[quoted text clipped - 79 lines]
>
> regards

Reply to this Message

Andrea Montanari - 29 Oct 2004 11:45 GMT

hi Elmo,

> I've tried it both ways (using Coding you gave/below) and using the
> Server Network Utility (enabling TCPIP) - - and no broadcasting - -

what kind of OS are on?
apart from instance enlisting, is it available on the lan from remote
clients?

Signature

Reply to this Message

Elmo Watson - 29 Oct 2004 15:58 GMT

Using Win2K pro - - for this particular test - the MSDE is on the same
computer that I'm running the ListAllAvailable Servers on - for now - just
using the sample that came with SQL Server for that.

Haven't tried remotely. I haven't had to worry about that with SQL Server,
since SQL Server broadcasts to all, even the existing computer - - I guess
I'll try that next
- but shouldn't it be available/seen, even if it is the same computer (on
the network)?

> hi Elmo,
> > I've tried it both ways (using Coding you gave/below) and using the
[quoted text clipped - 3 lines]
> apart from instance enlisting, is it available on the lan from remote
> clients?

Reply to this Message

Andrea Montanari - 30 Oct 2004 16:31 GMT

hi Elmo,

> Using Win2K pro - - for this particular test - the MSDE is on the same
> computer that I'm running the ListAllAvailable Servers on - for now
[quoted text clipped - 5 lines]
> - but shouldn't it be available/seen, even if it is the same computer
> (on the network)?

does
c:\>osql.exe -L
enlist the instance?

perhaps you should rely on alternative APIs like
http://www.sqldev.net/misc/EnumSQLSvr.htm and/or
http://www.sqldev.net/misc/OleDbEnum.htm
I do think it all depends on LAN shadowing... at the moment, in my scenario,
osql.exe -L enlist an MSDE instance on a VMWare virtual machine that is not
running since a week :-|

Signature

Reply to this Message

Elmo Watson - 30 Oct 2004 17:35 GMT

Sorry, it does not

> hi Elmo,
> > Using Win2K pro - - for this particular test - the MSDE is on the same
[quoted text clipped - 10 lines]
> c:\>osql.exe -L
> enlist the instance?

Reply to this Message

VB Forum / Database Access / October 2004

enumerate MSDE instances on Network