Search

Saturday, October 8, 2011

Hide an Instance of SQL Server in the network



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:


We can do this through T-SQL also:
The information to Show/Hide instance is stored in the registry. We can use extended stored procedure xp_instance_regwrite to update the registry value to Show/Hide 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.

No comments:

Post a Comment