October 22, 2009 at 7:40 am
Hi,
I have recently ran "sqlcmd -L c" to identify SQL instances within a domain and it has returned a number of default instance names that don't exist (these will all be SQL 2005). I didn't install SQL within this domain so these instances may have been installed then removed (all servers in this domain now only run named instances) but the sqlcmd result still recognises a couple of default instances. However, these default instances aren't listed if I browse for Network Servers using the SSMS "Browse for Servers" dialog box.
I've checked the registry for instance names (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names) and it indicates the default value is not set and I only see the named instance(s). There are no residual services for the default instances and no aliases setup using the default server name.
Any ideas on where the default reference is being read from so I can remove it?
Cheers
Jason
October 22, 2009 at 1:49 pm
Since you have no residual services and your registry is clean, have you checked WMI or attempted to changed the default port?
SQLCMD and OSql's method was to send out a Broadcast UDP packet on port 1434, with the contents of just a byte of 0x02, to which each server responds. 1434 is the default UDP port for SQL server.
In testing this I am seeing the same things as you. I have checked BOL and followed the suggestions in the article "How to reveal or cancel announcement of SQL Server on a network (Windows)" and found it to be of no use.
I have also found that i receive the same list regardless of services running, named pipes being enabled or not, removal of registry keys referring to default instance. I am exploring the wmi now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 22, 2009 at 2:53 pm
I found nothing in the WMI, other than the list of my installed instances.
Since sqlcmd checks UDP 1434 to see if there will be a response (and that can't be changed), it appears that this is the intended behavior. Even if you disable SQL Browser service, you will still get a response on that server because the app checks the port to see if SQL is listening. You won't be able to determine the instances (with browser service stopped) but simply that SQL exists on that server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 29, 2009 at 7:24 am
Thanks for spending so much time on this! I did wonder if it was intended but I've some servers that don't respond with the default instance listed - they all use dynamic port numbers and I can see SQL listening on the referenced port number as well as the referenced port number + 1 (e.g., 1256 & 1257) - again on all servers...
Will post more if I ever work this one out conclusively
Jason
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply