Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Hide an Instance of SQL Server

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:

image

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:

image

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

image

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

image

 

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! Cheers!

Reference : Vishal (http://SqlAndMe.com)


Filed under: Management Studio, SQLServer, Undocumented Functions

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.