Sql And Me

You can browse available SQL Instances on network by choosing "<Browse for More..>" from Server Name drop-down list in "Connect to Server" dialog box in Management Studio:

As you can see from the screen shot, all of my instances are visible on the network. If I want to hide this information from people on my network, it can be done easily. SQL Server allows us to hide an instance from being listed on the network. This can be done using SQL Server Configuration Manager.

For example, If I need to hide my Developer Edition instance SQL01:

1. Launch SQL Server Configuration Manager

2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties

3. Set "Hide Instance" value to "Yes" from the drop-down list:

4. The Instance needs to be restarted in order for changes to take effect:

Once the instance is restarted it will not longer appear in the "Network Servers" list:

Hiding an Instance using T-SQL:

The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :

EXEC master..xp_instance_regwrite

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',

      @value_name = N'HideInstance',

      @type = N'REG_DWORD',

      @value = 1

      – 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:

DECLARE @getValue INT

EXEC master..xp_instance_regread

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key=

N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',

      @value_name = N'HideInstance',

      @value = @getValue OUTPUT

SELECT @getValue

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe