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

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.
ScreenFoxAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
ZberteocCommented:
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
 
btanExec ConsultantCommented:
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
Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

 
David Johnson, CD, MVPOwnerCommented:
set your firewall rules for port 1433 to only allow from the local network AND the ip address of your amazon web service.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
btanConnect With a Mentor Exec ConsultantCommented:
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
 
ScreenFoxAuthor Commented:
Thanks for all the comments. I'll work on it and feedback.
0
 
ScreenFoxAuthor Commented:
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
 
ScreenFoxAuthor Commented:
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
 
btanConnect With a Mentor Exec ConsultantCommented:
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
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
btanExec ConsultantCommented:
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
 
ScreenFoxAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.