Cannot access an instance of SQL Server 2008 R2 remotely

Question

There is a very mysterious problem: P

I have a server configured with a static IP address. I installed SQL Server 2008 R2 with an additional instance (ITAPP). Now that I am using IP to access SQL Server, follow these steps:

Client machine:

sqlcmd -S XXX.XXX.XXX.XXX -U sa -P mypass 

Connected successfully ....

But when I use:

 sqlcmd -S XXX.XXX.XXX.XXX\ITAPP -U sa -P mypass 

HResult 0xFFFFFFFF, Level 16, State 1 SQL Network Interfaces: Error Locating Server / Instance [xFFFFFFFF].

Sqlcmd: Error: Microsoft SQL Native Client: An error occurred while establishing a connection to the server.

When connecting to SQL Server 2005, this failure may be caused by the fact that, by default, SQL Server does not allow remote connections.

Sqlcmd: Error: Microsoft SQL Native Client: Login timed out.

Even on the same computer (where SQL Server is installed) using SQL Server Management Studio. I turned off my firewall on both machines, even allowed all protocols for ITAPP (shared memory, named pipes, TCP / IP), also set Allow Remote connection to true.

Another thing, when I use 127.0.0.1\SQLITRAX to connect on the server machine, it connects immediately.

Please help me in this mess :)

Answer 1

possibly because it is a named instance on the remote server.
The named instance does not use the standard SQL Server 1433 TCP port, only port 1433 is used by default (without a name).
Any other "named" instance simply listens on another port.
Therefore, you should check the SQL-Server configurator on which the TCP port is listening, and then inform the client about the connection to this port.
Say your named instance is listening on port 12345, then the client should connect using the following command

 sqlcmd -S XXX.XXX.XXX.XXX,12345 -U sa -P mypass 

when you specify the TCP port, you do not need the instance name.
In my opinion, instance name is a way for SQL Server services to find the TCP port that this named instance is listening on.
But for this, your client must have access to those other services that resolve the name of the instance of SQL Server (it may be the SQL / SQL Server Agent browser, but I'm not sure)

Update
Here is a screenshot that shows where to set the TPC port for the instance of SQL Server named.

So, on which TCP port does your named instance of SQL Server appear?

Answer 2

SQL Server Viewer must be available to be able to connect to named instances.

See this article for more information on what it does and which ports open in your firewall.

This technical article provides a convenient script for opening all the necessary ports.