Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to setup a secure ODBC connection to SQL Server over the internet

Posted on 2014-04-11
13
Medium Priority
?
5,399 Views
Last Modified: 2014-11-12
Hi All:

I have an SQL Server at my office and a virtual server in amazon. I managed to connect an ODBC client in the VPS to the SQL server, so I already came across open ports and the basic tasks.

My concern now is to prevent anyone from hacking my database or my network, but I know nothing about how to do it.

Can anyone help me out?

Thank you.
0
Comment
Question by:ScreenFox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39995279
You will have ask Amazon to setup firewall rules to only allow remote connections to your SQL server from certain IP address, your company's.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1200 total points
ID: 39995362
Well, there is also the VPC Console Wizard within Amazon that does cater for a number of different scenarios (and manages the security accordingly).

It is really a matter of managing public and private subnets. Public is obvious enough, and then you need to "hide" your backend in the private subnet.

Two options in the Amazon VPC console wizard you might be interested in : "VPC with Public and Private Subnets"  or  "VPC with Public and Private Subnets and Hardware VPN Access".

Or, if just using the cloud and no real public facing requirement...  "VPC with a Private Subnet Only and Hardware VPN Access" and then you essentially hide it all behind your own firewall.

So, there are options, a lot can be controlled via the console wizard, and suggest that would be a good place to start.

A lot of the above is covered in http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_Security.html and access to the management console (and documentation + more) is via : http://aws.amazon.com/vpc/

Have a look at the links on the left hand side for console / documentation etc... Might be worth while working through : http://docs.aws.amazon.com/AmazonVPC/latest/NetworkAdminGuide/Introduction.html as the intro with embedded links to relevant sections.

It is / can be made secure enough to allay your concerns...
0
 
LVL 64

Expert Comment

by:btan
ID: 39995533
the key concern is sql by default should not be directly accessible through internet and do understand the business needs to be secure since exposing your crown jewels. there would be proxy fronting the sql ideally to filter and shield the sql, to extend not trusting any client connection unless authorised. the secure need to be protected minimally pt to pt from client to sql or to proxy if it exist.

nonetheless,  do catch some MSDN article on configuring the FW and secure connection to the sql as below

connect sql over internet -
http://technet.microsoft.com/en-us/library/ms175483(v=sql.105).aspx

encrypt connection to sql-
http://technet.microsoft.com/en-us/library/ms191192(v=sql.105).aspx

configure FW to guard sql-
http://technet.microsoft.com/en-us/library/ms175043(v=sql.105).aspx
0
Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 39995606
set your firewall rules for port 1433 to only allow from the local network AND the ip address of your amazon web service.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1200 total points
ID: 39995637
Both previous posts are true enough...

But your network (sql server) should be part of a VPN / hidden behind a private network.

Now you have said that you have enabled ports and the basic stuff, but there is some additional consideration to the above post and that is managing and understanding end points : http://technet.microsoft.com/en-us/library/ms191220(v=sql.105).aspx including choosing non-standard port 1433.

Food for thought.

But then we don't really have a handle on what your environment / Amazon virtual server are being used for...
0
 
LVL 64

Assisted Solution

by:btan
btan earned 800 total points
ID: 39995689
best is not to have sql direct connect to internet, just didnt find that this is best approach or if not go for private line subscription and indeed VPN is needed for secure end to end, and also make sure the data is end to end encrypted (on top of the channel). In short protect data at rest, data in use and data in transit at all time.
0
 

Author Comment

by:ScreenFox
ID: 40012359
Thanks for all the comments. I'll work on it and feedback.
0
 

Author Comment

by:ScreenFox
ID: 40017144
Hi again:

I finally created a VPC using the "VPC with a Private Subnet Only and Hardware VPN Access", like Mark Wills suggested.

I managed to create a VPN between the VPC and my LAN, so now the connection from the software running in my EC2 instances and my SQL Server in my LAN will be made through the VPN.

Should I bother now about SQL connection encryption or is it already encrypted by the VPN?

Thank you.
0
 

Author Comment

by:ScreenFox
ID: 40017149
Hi again:

I finally created a VPC using the "VPC with a Private Subnet Only and Hardware VPN Access", like Mark Wills suggested.

I managed to create a VPN between the VPC and my LAN, so now the connection from the software running in my EC2 instances and my SQL Server in my LAN will be made through the VPN.

Should I bother now about SQL connection encryption or is it already encrypted by the VPN?

Thank you.
0
 
LVL 64

Assisted Solution

by:btan
btan earned 800 total points
ID: 40017166
VPN is point to point and all traffic (including the SQL connection) will route through the VPN tunnel. In other words, an IPsec VPN connection between your VPC and your corporate network helps secure all communication between the application servers in the cloud and databases in your datacenter.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1200 total points
ID: 40017939
if using Win Server 2008 R2, then the "wizards" will tend to use defaults which (with IPsec) will have Encryption: AES-CBC 128

That should be enough...

Did you go through : http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_VPN.html
0
 
LVL 64

Expert Comment

by:btan
ID: 40019041
Besides the VPN, you can encrypt connections between your application and your DB Instance using SSL.

also from faq https://aws.amazon.com/rds/faqs/

Q: Can I encrypt connections between my application and my DB Instance using SSL?

Yes, however, this option is currently only supported for the MySQL, SQL Server, and PostgreSQL engines.

Amazon RDS generates an SSL certificate for each DB Instance. Once an encrypted connection is established, data transferred between the DB Instance and your application will be encrypted during transfer. If you require your data to be encrypted while “at rest” in the database, your application must manage the encryption and decryption of data. Also note that SSL support within Amazon RDS is for encrypting the connection between your application and your DB Instance; it should not be relied on for authenticating the DB Instance itself.
While SSL offers security benefits, be aware that SSL encryption is a compute-intensive operation and will increase the latency of your database connection.
Q: How do I secure Amazon RDS DB Instances running within my VPC?

VPC Security Groups can be used to help secure DB Instances within an Amazon VPC. In addition, network traffic entering and exiting each subnet can be allowed or denied via network Access Control Lists (ACLs). All network traffic entering or exiting your VPC via your IPsec VPN connection can be inspected by your on-premise security infrastructure, including network firewalls, intrusion detection and prevention systems.
0
 

Author Comment

by:ScreenFox
ID: 40031822
Thanks for you comment, breadtan, but I'm not using Amazon RDS instances at all. My SQL Server is running in my LAN.

I configured a VPN between my LAN and my virtual private cloud (VPC) in Amazon. Now my EC2 instances connect to my SQL Server through the VPN with no special configuration in SQL Server.

Thank you all.
0

Featured Post

Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

Question has a verified solution.

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

Most of the applications these days are on Cloud. Cloud is ubiquitous with many service providers in the market. Since it has many benefits such as cost reduction, software updates, remote access, disaster recovery and much more.
In this article, we’ll look at how to deploy ProxySQL.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

721 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