Connecting to SQL Server Express on AWS EC2

2
votes

I have SQL Server Express installed on AWS EC2 Reserved instance, created all my database on the name instance. I can ping this remote server but my window desktop app and Management Studio from my PC cannot connect.

I did the following:

  1. Allow remote connection to SQL Server Express (remote aws server)

  2. Allow inbound rule for port 1433 (firewall on aws server)

  3. All services of SQL Server for this instance are running

  4. When I ping from my PC, I get a reply, but....

  5. I cannot connect via Management Studio from my local PC and Visual Studio (Windows desktop app)

  6. I telnet within the remote server itself on several ports including 1433 and got error message:

    Could not open connection to the host on port : connection failed.

  7. I telnet from my local PC, I got the same error

My security group setting is as follows:

TyPE               PROTOCOL    PORTRANG  SOURCE           
MS SQL             TCP         1433      0.0.0.0/0
CUSTOM UDP RULE    UDP         1434      0.0.0.0/0
RDP                TCP         3389      0.0.0.0/0
ALL ICMP-IPV4      ALL         N/A       0.0.0.0/0

I have followed several solutions provided on this forum for example but still on the deadlock.

Did anyone run into the same issue? And if so: how did you solve it?

1
sql-serveramazon-web-servicesamazon-ec2
How you are pinging the server? If that is private IP it can be that there is other machine with same IP... – Michał Zaborowski
@criket, i ping the public ip address – Jack
You've done some great troubleshooting there. Step 6 means that the SQL Server is not listening on port 1433 or it isn't running. First confirm what port it is running on as per here: mssqltips.com/sqlservertip/2495/… – Nick.McDermaid

1 Answers

4
votes

This can be a problem due to MSSQL service listening to the localhost. What you can do is to "Change the bind address of MSSQL Service to bind to the Private IP of your EC2 Instance"

To do this, please follow below steps.

  1. Open SQL Server configuration manager and Navigate to "SQL Server Network Configuration" and select "Protocols for MSSQLSERVER"on the left pane
  2. Ensure that TCP/IP protocol is enabled on the right pane
  3. Right Click the "TCP/IP protocol" and select "Properties"
  4. A pop up will open now. In that select "Protocol" tab and ensure that "Listen All" is set to "No"
  5. Go to the "IP addresses" tab for the instance, set "Enabled" to "Yes" and "Port" field corresponding to your "Private IP" of your EC2 Instance to the "1433"
  6. Click "OK" and restart MSSQL Service and try again to connect