Connecting from SSMS to an EC2 SQL Instance

Question

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 !!!

Answer 1

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".

• gbn: as a side note, if you specify port then InstanceName is ignored. It can be just "123.123.123.123,19876" or 123.123.123.123\AnyOldRubbish,19876". This happens because a port 1434 call to resolve InstanceName to port isn't required.

Answer 2

I tried everything that's been posted.

But ultimately, my solution was to go into SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQL2012 (my named instance is SQL2012)

Then double-click TCP/IP, click IP Addresses tab, scroll down to IPALL, and change TCP Port to 1433.

After restarting SQL Server service, I was then able to connect with just the IP of the server.