Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Amazon EC2 webserver connection to external MSSQL DB.

Posted on 2013-11-25
6
Medium Priority
?
1,748 Views
Last Modified: 2014-11-12
Hi,

As you can see in this error message from our intranet I'm having problems connecting from our EC2 web server to an external MS SQL database hosted elsewhere.
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 SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have tryed: "telnet ip.address 1433" on the web server.
And get the following message: Could not open connection to the host, on port 1433: Connection failed.

Therefor I'm guessing the problem is related to the Amazon security groups or the host of the MS SQL servers firewall.
The host should have opened port 1433 for our web servers ip-address regarding to them.
If thats so I'm guessing that leaves it to our own Amazon security group settings, I have tried opening TCP port 0 - 65535 but nothing changed.
I have minimal configuration experience on firewalls.

Any help on what to do or check to bug find this problem?
0
Comment
Question by:Madsing
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:shalomc
ID: 39676415
Where is the SQL server located?

In the EC2 security group, you have Ingress (incoming) and Egress (outgoing) ports.
To connect to an external SQL outside AWS you need to allow all Egress ports.
0
 

Author Comment

by:Madsing
ID: 39676939
The SQL server is located at a local host in Denmark.

As I see its only VPC there have the ingress/egress separation.
At "EC2 > Security Groups" theres only two tabs. 1. Details, 2. Inbound.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39679953
I think by default sql server uses dynamic ports. With that setup you don't know what ports to open. What I did was first configure SQL server to use port 1433. Then you open up port 1433 on the firewall of the server. Then you open port 1433 in the EC2 security group. And then you open up port 1433 on your company firewall. You can check each step before you move on to the next.
Start with setting up port 1433 on SQL server like this:
http://support.microsoft.com/kb/823938
Then check if this works by connecting to sql server from the server itself like this:
server: (local)\SQLExpress,1433
(Change SQLExpress to your instance name of course.) If this works you know SQL Server is using port 1433.
Next step is to open up port 1433 on the firewall (Just Google for the instructions for this one). To test this you could start up another EC2 machine in the same security group and open port 1433 for the security group. You should be able to connect to the SQL Server from this machine. When this is working you should be able to connect to the SQL Server from any machine that has it's ip listed in the EC2 security group (with port 1433). If it's not working from some machines that's probably because that machine is behind a firewall that doesn't allow port 1433.

I'm accessing a EC2 SQL Server over the Internet for more than a year now, so we are going to get this working. If necessary I'll start up a new EC2 and explain all the steps needed in detail (but of course I hope I don't have to :-) ). What version of Windows and SQL server are you using in EC2.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Madsing
ID: 39695182
Hi again,

The following is our setup.
Server setup
I have no problem communicating from "my desktop" to "SQL Server", but the Amazon cloud cant access the MS SQL Server.
Thats why I'm pretty sure it's the Amazon servers firewall or the amazon security group theres blocking the communication. The ip of the Amazon server is opened for at the MS SQL server the same way as my desktop ip is added.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 1500 total points
ID: 39742004
Ah, sorry for misreading your question. I did some testing and I'm pretty sure the Amazon security groups have nothing to do with it. I created an SQL database in Azure. I added the ip of my desktop and an Amazon EC2 machine to the Azure firewall and was able to connect to the database from both my desktop and Amazon EC2 machine. I did this without changing anything about the Amazon configuration. It could be the Amazon machine's firewall is blocking the outgoing traffic. Or there is a problem with dynamic port. Azure uses a static port. To test this I suggest you create a SQL database on Azure also and then try to connect to it from both your desktop and the EC2 machine. If that works the problem is probably with your SQL Server.
Good luck solving this one, and I'm sorry I don't have an easy answer for you.
0
 

Author Closing Comment

by:Madsing
ID: 39745668
Thanks for taking the time answering my question.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question