6

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

I've always assumed they do the same thing and have have hidden the server as a backup in case the SQL Browser service ever gets turned on but cannot find any documentation confirming this.

Thanks in advance!

4

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

To hide an instance of SQL Server, you need to set HideInstance flag to YES.

enter image description here

When you hide an instance, it will indicate that sql server browser should NOT respond with information about this server instance. Note that hidden instances support only shared memory connections. TCP/IP is allowed (as long as the port is 1433 or you specify) [Thanks to @AaronBertrand for highlighting this].

SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. This enables client applications to browse for a server, and helps clients distinguish between multiple instances of the Database Engine on the same computer.

Refer : Hide an Instance of SQL Server Database Engine and Why listing all of your SQL Servers does not always work ?

6
  • Thanks much Kin. I do have 3 server instances that are hidden; however, I can reach them via TCP/IP. I can confirm that by connecting to the instance from my local machine or a DSN using "ServerName,1433". Is that being translated on the server to shared memory somehow? I believe that that is true only if you turn off all TCP/IP and Named Pipes. – PseudoToad Jul 8 '15 at 15:18
  • To be honest, I dont really see a need to hide an instance. Just dont give access to people if you want to keep it in isolation. If you specify port number, then its going to use TCP/IP. I have never hidden an instance. Good question though and I learned something new that I never looked at. – Kin Shah Jul 8 '15 at 15:33
  • 3
    @Kin you should correct the bold statement toward the end of the post - TCP/IP is also allowed (as long as the port is 1433 or you specify). Basically, hiding an instance just prevents routine external connection attempts from "finding" a named instance (I haven't tried in SQL Server 2000 in years, but I don't think it works for a default instance on port 1433). Turning off the SQL Server Browser just does that for all named instances. Also see mssqltips.com/sqlservertip/2057/… – Aaron Bertrand Jul 8 '15 at 16:04
  • 1
    @AaronBertrand thanks for suggestion and yes, I just tested it for default 1433 and it will work. I have edited my answer. – Kin Shah Jul 8 '15 at 16:10
  • 4
    @Gizmo No, why would I? You use firewalls to protect servers, not obfuscation or partially functional gimmicks. – Aaron Bertrand Jul 8 '15 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.