Solved

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

Posted on 2014-04-11
13
4,148 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
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 26

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 300 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 61

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
 
LVL 78

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 300 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 61

Assisted Solution

by:btan
btan earned 200 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 61

Assisted Solution

by:btan
btan earned 200 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 300 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 61

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 Syntax Error 5 24
Database Containment - Benefits 6 25
using t-sql EXISTS 8 23
Auditing in Azure SQL Database 3 28
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now