Hide an instance in SQL Server 2008

[Gina Ocaña]

Hello everybody:

i have a sql server 2008 standard edition with service pack 1 installed over
a windows server 2008 SO.

I have an instance with name SK8.
I have to do a hardening in sql server 2008 and i configured the option to
hide the instance in SQL Server Configuration Manager i set this value to
true.
When i set the hide value to true, the remote connectios with sql server
studio management stop to working to SK8 INSTANCE.

"An error has occurred while establishing a conneciton to the server. when
connecting to SQL server 2005, this failure may be casued the the fact that
under the default settings SQL server does not allow remote connections.
(provider: SQL Interfaces, error: 26- Error locating server/instance
specified)

Could you help me, please

[Erland Sommarskog]

Gina Oca�a (Gina...@discussions.microsoft.com) writes:
> i have a sql server 2008 standard edition with service pack 1 installed
> over a windows server 2008 SO.
>
> I have an instance with name SK8.
> I have to do a hardening in sql server 2008 and i configured the option to
> hide the instance in SQL Server Configuration Manager i set this value to
> true.

I will have to admit that this option is unknown to me. Exactly where
did you set it?

> When i set the hide value to true, the remote connectios with sql server
> studio management stop to working to SK8 INSTANCE.
>
> "An error has occurred while establishing a conneciton to the server.
> when connecting to SQL server 2005, this failure may be casued the the
> fact that under the default settings SQL server does not allow remote
> connections. (provider: SQL Interfaces, error: 26- Error locating
> server/instance specified)
>
> Could you help me, please

As I said, I don't know of the option, but didn't you get exactly
what you asked for? You hid the instance, and now it remote connections
cannot find it. Again, since I don't know about this option, I will
have to guess what it does, but conceivably it does one of two:

1) Completely hides the instance for all outside connection.
2) Hides the instance name to be exposed by the Browser service.

In the latter case, direct connection on the TCP/IP port should still
work, for instance

SERVER,4711

In this case you should configure the service to always use the same
port.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Gina Ocaña]

Hello Erland,

Thanks for your answer
For hide the named instance i followed this steps:

Open SQL Server Configuration Manager -> SQL Server Network Configuration
-> SK8 (Named Instance) -> right clic -> Properties -> shows a window with
name ->Protocols for SK8 Properties -> In General Tab -> Hide Instance i set
this value to True.

Then, a pc with windows xp with sql server 2008 console instaled tried to
connect to sk8 instance and get the error.

Could you explain me in more details about the ports that you said it??

Thanks

Greetings,
Gina

"Erland Sommarskog" wrote:

> .
>

[Erland Sommarskog]

Gina Ocaña (Gina...@discussions.microsoft.com) writes:
> Thanks for your answer
> For hide the named instance i followed this steps:
>
> Open SQL Server Configuration Manager -> SQL Server Network
> Configuration -> SK8 (Named Instance) -> right clic -> Properties ->
> shows a window with name ->Protocols for SK8 Properties -> In General
> Tab -> Hide Instance i set this value to True.
>
> Then, a pc with windows xp with sql server 2008 console instaled tried to
> connect to sk8 instance and get the error.
>
> Could you explain me in more details about the ports that you said it??

OK, I found the setting. Since I don't know exactly what it does, guess
what I did? I pressed the question mark in the upper-right corner:

Prevent the SQL Server Browser Service from exposing this instance of
the Database Engine to client computers that try to locate the instance
by using the Browse button. In the case of named instances on the
server, to connect, client applications must specify the protocol
endpoint information. For example, the port number or the named pipe
name, such as tcp:server,5000. For more information, see Connecting to
the SQL Server Database Engine.

Which exactly what I suspected in my previous post.

I would suggest that you repeat my exercise, and then follow the link
in the last sentence.