hi,
CTJohn23 wrote:
I recently setup mutliple instances of SQL Server Express at my office. I have 1 default instance, and two named instances. I can connect to the named instance of the default port of 1433 with Microsoft SQL Management Studio Express,
TCP/IP 1433 is usually reserved for the default instance...
however the other instances on dynamic TCP ports can not be accessed by the instance name over the internet. I have to specify the dynamic TCP port in this form: xxx.uconn.edu/SQLTEST, Port number. My current thinking is that the SQL Browser service should tell Management Studio Express what dynamic port number each SQL instance is listening to. Any ideas?
the SQLBrowser can not "work" over the internet in that way... all enlistment for instance presence are available via an UDP broadcast call on the local area, when no firewall action is involved...
don't know if the "tool" still relies on SQLBrowseConnect ODBC function, like SQL Server 7.0 and 2000 ListAvailableServer used it, an 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.
No comments:
Post a Comment