Home / server / Questions / 165952
Accepted
Adrian Godong
Asked: 2010-08-01 15:18:02 +0800 CST

Connecting to MSSQL on EC2 from local

  • 772

Has anyone ever managed to connect to Microsoft SQL Server (preferably 2008) hosted on Amazon EC2 from Management Studio on your development machine?

I have set up the security group to allow port 1433, opened port 1433 outbound from Windows Firewall to no avail.

Error from Management Studio is '26 - Error Locating Server/Instance Specified'. I am using the associated Elastic IP address as the server name.

Tried searching 'sql-server ec2', but no similar problem found.

The server is accessible from local EC2 Management Studio.

sql-server amazon-ec2

2 Answers

  1. Is the server configured to listen on the public IP of the EC2 instance? A local EC2 SSMS would connect using Shared Memory protocol.

    Needles to say, opening a SQL Server for internet is a really bad idea. Your TDs port will be subject to constant bot scans and your ERRORLOG will fill with failed 'sa' attempts. You do have a very strong sa password, don't you? At the very leats, use a non-default port.

    • 3
  2. Best Answer

    Check list to enable connection:

    1. First of all, determine the port number that you want to access to (default is 1433 but you may want to change this. For this example, I picked 5555.)
    2. Allow connection to this port on EC2 Security Group. Add TCP/5555-5555/all.
    3. Allow inbound connection to this port on Windows Firewall. Add a new rule on that port (and you may want to specify sqlservr.exe as the program).
    4. Enable TCP/IP protocol on SQL Server Configuration Manager and set static port. To do this, go to the IP Addresses tab on the protocol property, scroll down to IPAll, blank the TCP Dynamic Ports and set the port that you want in the TCP Port property.

    You're all set.

    • 2