Hiding and Showing a SQL Instance on the Network

When attempting to hide or display an instance of SQL Server over a network, you have a number of different method for doing this.

I found this  particularly problematic when trying to locate a remote SQL server located on a colleague’s workstation. As such, I felt it worthwhile to record and enumerate the different method for doing so.

The scenario was that I was attempting to connect through SSMS (Management Studio) and was getting the error message “Cannot connect to [database name]’

SQL Server Properties

  1. In Management Studio, click on the server root, and select properties.
  2. From the resulting dialog, select the ‘Connections’ page.
  3. Ensure the checkbox “Allow remote connections to this server” is checked.

SQL Server Configuration

  1. Run SQL Server Configuration Manager (located in within the SQL shortcuts under the windows start menu)
  2. Navigate to “SQL Server Network Configuration” and then “Protocols for SQL2008R2”
  3. Ensure TCP/IP is “Enabled”
  4. Right-click on “Protocols for SQL2008R2” and select “Properties”
  5. Ensure “Hide Instance” is set to “no”

 SQL Browser Service

  1. Navigate to the Service Control Manager through control panel (or run “Services.msc”)
  2. Ensure the “SQL Server Browser” service is started. (It may be best to have the Startup Type as “Automatic”

Now, hopefully, you should be able to see the SQL Server instance over the network and connect to it (assuming you have permissions to do so!)

Thanks for the info provided on Stackoverflow in the following articles:

Make MSSQL 2008 Instance invisible (nonpublic)

SQL Server Browser showing only hostname, not instance name

 

 

Leave a Reply