Connecting from SSMS to an EC2 SQL Instance

I have an EC2 server running SQL2008 R2.

• I opened the port 1433 and successfully tested with yougetsignal.com/tools/open-ports.

• I have set the SQL Server to Accept Remote Connections.

• I have set the Dynamics Port to be 1433 on the TCP/IP dialog under the Configuration Manager.

• On Client Protocols I have Shared Memory 1st, TCP 2nd and Named Pipes 3rd.

• The server SQL Browser services is running

On the server I normally connect from SSMS using MACHINENAME\SQL2008

QUESTION:

What is the right way to connect from my local machine? Is it "IP ADDRESS\SQL2008"?

I tried various ways and it does NOT work. So I must assume it's both the way I connect and some missing settings on the server. I get the following error:

A network-related or instance-specific error occurred while establishing   
a connection to SQL Server. The server was not found or was not accessible. 
Verify that the instance name is correct and that the SQL Server is configured  
to allow remote connections. (provider: Named Pipes Provide, error: 5-Invalid  
parameter(s) found) (Microsoft SQL Server, Errror: 87)

ANSWER

Thanks Mark. I was able to connect just by appending the ",1433" at the end.

So the right answer (at least for my problem) was MACHINENAME\SQL2008,1433

Thanks again Mark !!!

If the server is installed as the default instance, you should be able to connect with DNS name or IP Address. The "\SQL2008" you've added to the address would be used to connect to a named instance called "SQL2008".

Try specify the port in your connection string e.g. "123.123.123.123,1433". Without the port specified, the client will query port 1434 initially to determine which port to use. Alternatively you could open port 1434.

SQL Server Browser Service


When SQL Server clients request SQL Server resources, the client
network library sends a UDP message to the server using port 1434. SQL
Server Browser responds with the TCP/IP port or named pipe of the
requested instance. The network library on the client application then
completes the connection by sending a request to the server using the
port or named pipe of the desired instance.

Edit: If you can connect using MACHINENAME\SQL2008 when you are RDP'd to the server, you must be using a named instance. If this is the case, your connection string with the port specified will be "123.123.123.123\SQL2008,1433".

StackExchange Database Administrators Q#7829, answer score: 6