2

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.

3

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.

2
  • Instance has Shared Memory and TCP/IP protocols enabled. I'm trying to use default values here first and make sure it's working. Afterwards, secure them. If it doesn't even work, I don't think anything is worth securing. – Adrian Godong Aug 2 '10 at 13:25
  • Did you open the port 1433 from EC2 firewall (in the group security settings)? – Remus Rusanu Aug 2 '10 at 20:36
2

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.

1
  • 1
    We should not recommend opening 1433 to all IPs. It should be restricted to the corporate IP range and/or AWS hosted VPN or some sort of Bastion solution. – Shane Jun 21 '18 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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