Solved

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

Posted on 2014-04-11
13
4,338 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 62

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 79

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 62

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
 

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 62

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 62

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.

823 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