hide instance not working

Question

We do not appear (yes/No) for hide instance in SSCM for all SQL Server 2012 servers.

We are unable to see (yes/No) option for hide instance . Kindly assist me here .

Thanks a lot !

Answer 1

This is the procedure: Hide an Instance of SQL Server Database Engine

Is the SQL Server Browser running?

The information to hide or show the instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide or show the 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.