Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
1  
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. –  gbn Nov 13 '11 at 15:15
    
This I didn't know. Makes sense but it hadn't clicked before. –  Mark Storey-Smith Nov 13 '11 at 16:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.