Unable to access an instance of SQL Server 2008 R2 remotely

Question

A very mysterious problem coming up :P

I have a server configured with a static IP. I have installed SQL Server 2008 R2 with additional instance (ITAPP). Now When I use the IP to access the SQL Server following things happens:

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 Specified [xFFFFFFFF].

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

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

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Even on the same machine (where the SQL Server is installed) using SQL Server Management Studio. I have put off my firewall on both machines, even allow all the protocols for ITAPP (Shared Memory, Named Pipes, TCP/IP), also set Allow Remote connection to true.

One thing more when I use 127.0.0.1\SQLITRAX to connect on server machine it connects Immediately.

Please help me out from this mess :)

Solution 1

maybe it's because it's a named instance on a remote server.
Named instance don't use the SQL Server standard Tcp-port 1433, only the default (unnamed) instance use the 1433 port.
Any other "named" instance simply listen on another port.
So you should check in the SQL-Server configurator, on which TCP port is listening, and then tell the client to connect to this port.
Say that your named instance is listening to 12345 port, 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, then you don't need the name of the instance.
In my understanding the name of the instance is a way for SQL Server services to find the TCP port that this named instance is listening on.
But for this to work your client need to be able to access those other services that resolve the instance name of SQL Server (maybe it's the Sql server Agent / SQL Server browser, but I'm not sure)

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

so, on which TCP port is your named instance of SQL Server listening?

Solution 2

The SQL Server Browser service needs to be accessible to be able to connect to named instances.

See this article for more info on what it does and which ports to open on your firewall

This technet article provides a handy script to open up all required ports

Comments

[Max]

I just updated the response with a screenshot, here is where to looks for the TCP port your server is listening on

[MacGyver]

Thanks for hint about the port. Basically it almost resolved my problem. The only thing I needed to do was creating an inbound rule for this specific port in Windows Firewall And Advanced Security wizard.