Hello Friends,
By default, we can see every instance of SQL Server on the network. The network broadcast is what enables the instance enumeration within various management tools where you can utilize a drop down to select an instance. SQL Server Browser Service makes this more convenient.
If you want to list out all SQL server Instances in your Network use the below mentioned query:
EXEC master..XP_CMDShell 'OSQL -L'
But there are many cases, particularly in production environments, where you want to hide your instance of SQL Server or in other words, where you do not want SQL Server to broadcast its presence.
Hiding an instance is done for security purposes. This would force an attacker to scan IP ranges in an attempt to locate SQL Servers which can be easily identified & mitigated by your Netowrk Admin. Unfortunately, the option to disable the broadcast is hidden away. But you can easily do so by following the below mentioned steps:
To disable the broadcast,
Launch SQL Server Configuration Manager.
Open the Network Configuration section.
Right click on the Protocols for the instance you want.
Select Properties.
You will see a flag value labeled Hide Instance.
Set this value to Yes.
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
It’s enough to disable SQL Server Browser service for this case. But don’t forget after it, to acess to named instances you must use only : format, instead of using the name of instance.
The “-L” switch does not allow you to see every running instance of SQL Server, only those that are on the same network segment. The documentation even hints at this:
“Lists the locally configured servers and the names of the servers broadcasting on the network.”
The important word there is “locally”.
Instances on different network segments are not returned.
+1 thomas
total noob question:
Is there a SQL select command I could issue that would return the sate of the hide instance setting? I’d like to script a quick test that would query every SQL Server in my environment andfind those that are not set to YES.