It is holiday season everywhere. It means that Freeze on Change Controls. Nothing changes so nothing breaks. Also the instances my team supports have been very supportive this holiday season. Hence most of my recent posts have been inspired by the questions that I respond in the forums, not from the issues that I face. This one is also a detailed version of the answer that I had posted.
While trying to connect to an instance using SQL Server Management Studio (SSMS), it *looks* for all SQL Server instances available on the network. Similarly sqlcmd -L command lists the SQL Server instances available on the network.
Due to some reasons you may want to hide these instances so that they do not get listed in SSMS or sqlcmd. Here is how to do it on various versions of SQL Server.
SQL Server 2000
- Open Server Network Utility
- Select the properties of TCP/IP protocol
- Select the Hide Server option.
For this change to take effect, the SQL Server service needs to be restarted. This hides the instance alright but there is a drawback. The Default port gets changed to 2433 and this cannot be changed! Certainly not an good thing to try out on Production Instances.
SQL Server 2005 & above
Starting SQL Server 2005, the feature to “hide” an instance is available in SQL Server Configuration Manager.
Unlike SQL Server 2000, there is no drawback with this. However the service needs to be restarted after making this change. The instance can also be hidden through Registry.
The location of the key (MSSQL.1 in this example) changes according to the Instance Name.
It should be noted that *hiding* the instance will only prevent it from getting listed in the SSMS or in sqlcmd/osql. It does not prevent the users from connecting to it by directly entering the instance name.