• Call: +1 (858) 429-9131

Microsoft SQL Server 2008 Encryption: Part l

Entrusted with a project where we have to implement data protection and privacy guidelines in accordance with international regulations we found ourselves investigating ways to implement data encryption in MSSQL database in Amazon EC2. We are deploying an application which has to deployed in all the AWS availability zones and our problems like syncing between zones are still at large as fine tune MS-SQL for the cloud.

Data security and privacy

The applications which storing sensitive information like customer information, Financial information, personal health information(PHI) etc, will have to meet certain data privacy and security acts. The Health Information Portability and Accountability Act (HIPAA) of 1996 protects health information. To make applications HIPAA compliance we can use some of new security features in SQL server 2008. MSSQL Server 2008 introduce new with security feature enhancements for powerful encryption and better key management capabilities

Data security in cloud

The data in cloud is in a shared hardware environment. Therefore data segregation is one of the major security issue. All cloud providers have their own storage management. But the security and privacy of data is again an issue because the customers don’t have much control over this storage area. They don’t know the exact location where data is stored. Customers can overcome these issues by implementing their own security features based own their application.

We had 2 issues to address:

1. Encrypt data in transit
2. Encrypt data at rest

In this one we are starting by implementing encryption of data in transit for MSSQL server. MSSQL Server uses the Secure Socket Layer(SSL) to encrypt data transfer between SQL server and applications. This encryption will ensure secure data transmission over the network. This is much more important when application and database are distributed on many AWS availability zones.

Implementation of SSL

1.Creating a self-singned cert using makecert

[text]
makecert -r -pe -n “CN=SERVER-FQDN” -b 01/01/2010 -e 01/01/2015 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp “SSL Providerl” -sy 12 c:\testssl.cer
[/text]

2.Install this cert

Copy c:\tesssl.cer into your client machine, run c:\testssl.cer from command window, select “Install Certificate”. -> click “Next” -> select “Place all certificates in the following store” –> click “Browser” -> select “Trusted Root Certification Authorities” -> select OK and Finish

3.Open SQL Server Configuration Manager

Expand SQL Server Network Configuration, right-click “Protocols for MSSQLSERVER” then click “properties”. On the “Certificate” tab select the certificate just installed . On the “Flags” tab, set “ForceEncryption” YES.

Now SSL is ready to use on server. You have modify your connection string to use SSL.

For Example

connectionString=”Data Source=db.server.com;Initial Catalog=mydb;User ID=user1;Password=pas@123;Encrypt=true;TrustServerCertificate=true”

You are Done!

Microsoft SQL Server 2008 Encryption: Part ll

In our first article we had discussed security and privacy of data in cloud and how to meet MSSQL server HIPPA(Health Information Portability and Accountability Act) compliance. We had also learn how to encrypt data in transit between MSSQL server and applications. Here you will learn how to encrypt data in rest in MSSQL using Transparent Data Encryption(TDE).

It seems that most of the security products focused their effort on securing data in transit only, using SSL/TLS. But when you build a security system that meet the international security regulations and acts, you need to encrypt data in rest also. The insecure configuration of server, operating system, firewall and network in general, will make it easier for some one to gain access to data at rest.

TDE performs real time encryption and decryption of data while writing and reading data from MSSQL. As the name implies encryption is transparent, that is no need to modify code or architecture of applications when implementing TDE.

Implementation of TDE

1. Create a master key
A master key is a symmetric key that is used to create certificates and asymmetric keys.

[sql]
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘<password>’;
GO
[/sql]

2.Create Certificate

Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:

[sql]
CREATE CERTIFICATE TDECert
WITH SUBJECT = ‘TDECertificate’
GO
[/sql]

3.Create a Database Encryption Key and Protect it by the Certificate

1.Click object explorer in the left pane of the MSSQL SERVER Management Studio
2.Right Click on the database which you want to encrypt
3.Click Tasks and Navigate to Manage Database Encryption
4. Select the encrytion algorithm (AES 128/192/256) and select the certificate you have created
5.Then Mark the check Box for Set Database Encryption On

You can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.

[sql]
SELECT [name], is_encrypted FROM sys.databases
GO
[/sql]

Important Back up the certifi cate and private key to a fi le to ensure recoverability as follows

[sql]
BACKUP CERTIFICATE TDECert TO FILE = ‘C:\TDECert.cer’
[/sql]

You are Done!!

Apache-Tomcat Load Balanced Persistent Session Setup on Amazon EC2

Although Tomcat is a good option for heavy java applications, it gives a poor performance under high pressure.The best way to solve this problem is to set up an Apache-Tomcat Load Balanced on your Amazon EC2 environment. In this case you will have more than one parallel running tomcat instances and each will be able to share the part of the traffic. Read more…

Resolving “rsync warning: some files vanished before they could be transferred” on ec2-bundle-vol in an aws ec2 instance

This article explains how to resolve some of the common errors that you may encounter when bundling an AMI ( Amazon Machine Image) of an AWS EC2 instance. It is indeed a very good practice bundling your AMI frequently as the latest updates of your app gets stored directly to S3. So even if your instance gets crashed you can launch another instance of your latest ami and get your site back live in minutes!.
Read more…

Deploying a load balanced e-commerce portal in Amazon EC2

Update: NFS should not be used as that will be a SPOF. One should use S3 or other object stores. An alternative could be multi-node GlusterFS if someone needs volumes shared across nodes.

When building an infrastructure for an eCommerce portal on Cloud, it is important to note that it should be available all the time, that it is fail safe with outages like the one we had recently in AWS EU and U.S. East Regions, survive Hardware failure or any other issues like bug in the system or deployment errors. We built an infrastructure on AWS Cloud that address all these issues with LAMP using various AWS Cloud services like EC2, S3, RDS, EBS etc. It is described in detail below:

 

Achieving High Availability & Fail over across Datacenters

Elastic Load Balancer (ELB)

The Elastic Loadbalancer ( ELB ) service provided by AWS tries to achieve the following:

(i) Spans across Datacenters: Loadbalance traffic across mulitple datacenters (AZ )thus providing high availability even if one datacenter goes down. So you should always make sure that when you launch instances under an ELB, you should launch it in different Availability zones. You can also launch instances in the same AZ but by default ELB will redirect request across multiple AZ in a Round Robin way.

(ii) Failover: ELB will periodically monitor the health of the instances and if any of the instance or monitored service ( e.g. Http ) goes down, ELB will stop redirecting requests to that instance and all the request will be redirected to the remaining number of instances registered under ELB. When the instance comes backup, it will again start redirecting requests to that instance.

(iii) Handling root domain ( apex / main domain ) and subdomains: ELB can loadbalance only those requests coming to alias / subdomain( www ). It cannot handle request coming to root domain. This is because when you configure DNS for enabling ELB, you can only set CNAME to ELB for subdomains. There are 2 reasons for this. One is when you configure ELB, you will only get a Public DNS name for the ELB like the following instead of a Public IP.

[bash]Test-1736333854.us-east-1.elb.amazonaws.com [/bash]

This is because AWS changes the Public IP of the ELB periodically for providing scalability for ELB itself. Another reason why you cannot redirect main domain request to ELB is that DNS protocol itself restricts the usage of CNAME or anything other than “A” record for a root domain. So you cannot CNAME root domain to ELB DNS name.

So for serving root domain requests with ELB , there are only work arounds like mentioned below:

a) We have to assign an elastic IP for an instance under ELB. But what if this instance goes down? Set heartbeat to switch EIP? This is a bit complicated setup as switching EIP to instances present across AZ takes time.

b)The other option is to have the root domain point to the IP addresses of the destination by configuring one or more “A” records (address records) for root domain. You can do that if you know the destination IP addresses are fixed, such as if you are using EC2 Elastic IP addresses. We wouldn’t recommend this because IP addresses will be cached at the client end for long time even if you set low value of TTL at the nameservers. This is because TTL value can also be configured at the the client end overriding the TTL value provided by the nameserver of the domain. e.g. with nscd ( Nameserver Caching Daemon) you can set the TTL value manually in its configuration file.

c) You can keep a separate web server not under ELB with a Redirect Rule for redirecting root domain requests to www. You should make sure that this webserver is highly available as well.

d) A better solution is to go for Domain Registrars ( DNS service providers ) who provide this feature of redirecting root domain requests to www. So this can be handled at the DNS itself. The DNS service provided by AWS “Route53” can be used for this ‘Zone apex’ ( root domain ) redirection.

(iv) SSL Termination

There is support for “SSL termination” in ELB which means you can use ELB to loadbalance HTTPS requests too. You just need to buy the SSL certificate and simply upload it to ELB. ELB will redirect all the HTTPS request to the backend servers. So you can make an eCommerce portal highly secure and highly available with ELB.

(v) Persistent Session

You can enable Sticky Session with ELB but the problem is users will be logged out if any of the instance / webserver goes down and ELB will redirect the subsequent requests from the same user to a different instance and it will prompt the user to login again. To tackle this there were few options we had considered –
a)You can either setup distributed failover memcached server or
b)You can use RDS for storing Session.

We went for RDS as our Session Management store since RDS is an excellent choice for Database Administration as well if you are using MySQL as the Database.

Your application must be configured to write session data to an RDS database. So when an instance / webserver goes down and when the ELB redirects the user request to a different instance, the user will not be asked to login again as all servers are reading session data from the same place that is RDS. The user won’t notice anything at all, even though they’ve now started talking to another server. We recommend using a Multi-AZ RDS instance and write session data into this. So if one of your EC2 instances goes down, the other instances will still have access to the RDS database, and likewise if an RDS zone goes down, Amazon fail this over to the second AZ internally, transparently to you and your application.

So the easiest and most reliable way to share sessions for failover on a multi-server environment is to use RDS, since Amazon handle the database layer’s failover for you.

So basically you can achieve two things by using RDS – Session management and Database Management.

 

AutoScaling

The Autoscaling service provided by AWS allows you to scale horizontally up / down with CPU usage, RAM, Disk I/O etc.

Ideally you should use a Base AMI with Autoscaling that will pull the required packages from a Centralized location like Chef Platform and code from the Version Control System or S3. You can write a startup script to run on instance bootup for this purpose. So when Autoscaling launches a new instance it will pull all the latest updated versions of the packages, code and also any other required custom configurations from a centralized location. This will also make it easier to manage all the configuration details, code updates from a centralized location using tools like Chef Platform, Version Control System or S3 respectively.

Apart from Centralised Configuration / Code management, the reason for using Base ami with Autoscaling is that it is not possible to change the ami configured with Autoscaling service dynamically.

 

Storage for Application Files

We came across lot of options for storing the application files. However you have to consider your priorities before you select a storage service for the code. Following are the points to consider for your application file storage system:

(i)Latency issues: All shared storage systems like NFS / GlusterFS / EBS / S3 etc have latency issues when compared to Instance store (Ephemeral Storage)

(ii)High availability: If you are using a shared storage service like NFS, it should never go down for the entire system to be available all the time.

(iii)Access to the code: How to get the latest code during incremental roll out of a new instance because if you are using a shared storage, it becomes difficult to gives access to the shared storage system when a new instance is launched

We went for instance store / ephemeral store that gives you better I/O performance. You can keep your own highly available SVN repository or go for publicly available Version Control Systems like GitHub. At the same time you can also keep a copy in S3 and sync to it whenever there is a code update. This will make it more redundant.

The problem with using shared storage service like NFS / GlusterFS with EBS / S3 is it becomes difficult to avoid single point failure for NFS / GlusterFS service. But if your site doesn’t have much hits and your priority becomes redundancy, you can go for mounting S3 as filesystem using tools like s3cmd and use that as a shared storage with NFS for multiple instance. The problem with S3 is that it is not intended to be used as a filesystem and there have been issues reported with speed and caching. Or you can use EBS volume for code storage if you have only a single instance serving the request. Even using NFS with EBS volumes ( with frequent snapshots to S3 ) gives better performance than using S3 as shared storage for files.

Not only does instance store gives you better performance, error rates very rare. with EBS volumes error rates are reported frequently. Recent outages with AWS EU & US East Regions shows that the down time was made worse due to increase in time taken to recover from EBS errors.

 

Code Deployment

For automating code deployment, you can configure deployment tools like Capistrano. This will become very handy when you have multiple servers to update simultaneously. Capistrano uses Ruby language and is built for Ruby code deployment but with little changes, you can automate deployment of PHP / Perl / Python / JAVA based application.

chef-deploy is another tool that comes with chef for automating code deployment. Continuous Integration tools like Hudson / Cruise Control are excellent tools when you want to automate the Build, Deployment, Test and Rollback process.

For code deployment, we follow a Release Management process where we keep a staging environment that is an exact replica of the production environment. We push code to the production environment only when it’s been completely tested in the staging environment and approved by the Release Manager. This will further reduce the errors / bugs / and downtime time caused due to the code release.

 

Database Server

We went for RDS across AZ for High availability. AWS will take care of Redundancy, Performance Optimisation, Scalability and Backup. You can avoid the hassle of managing a Database Server by using RDS. RDS is as an excellent distributed highly available Session Management System. You can also take regular backup from RDS and keep it in S3.

You can also use Master–Slave Replication setup instead of RDS. This is also a good option for achieving high availability for Database server. The challenging part will be to manually configure failover for both master and slave servers, achieving scalability with traffic, backup configuration and performance optimization with increasing load. With RDS, all these will be managed by AWS.

 

Log handling

Keep all the important logs like Application logs, Syslogs, SSH log etc in EBS volume. You can either schedule regular snapshots of these EBS volume to S3 or you can even sync these log files to an S3 bucket periodically using tools like s3sync.

 

Configuration Management

If you have more than one server or are planning to scale up in future or would like to automate a lot of administration / coding stuffs, you should definitely use one of the Open Source freely available Configuration Management tools like Chef / puppet / Cfengine

Chef is new and has default support for AWS / EC2. We use Chef extensively for managing our infrastructure in AWS. Chef provide a lot of readily available cookbooks ( recipes / roles ) for LAMP, JAVA app, Cassandra, Hadoop, Nagios etc which can be used readily ( or with minimum customization ) to automate the infrastructure setup and configuration. Chef also comes with a tool called Chef-deploy for automating deployment of code.

So using Chef along with tools like Hudson / Cruisecontrol, you can automate the entire setup from infrastructure setup to configuration management to building, deployment and testing of your application.

 

Performance

To improve performance you can implement the following:

(i)Use caching mechanisms like Memcache(DB scaling) / aiCache / Varnish.

(ii)CDN ( Content Delivery Network ) is a must if you want to provide better end-user response time. There are lot of CDN providers but we recommend AWS CloudFront or Akamai for serving static files and images. For start-up and small business, CDN might be costly but as your target audience grows larger and becomes more global, a CDN is necessary to achieve fast response times.

 

Monitoring & Alert

For monitoring, go for open source monitoring tools along with a SaaS based monitoring application.

(i)There are lot of free and open source option available in the market – Nagios, Zenoss,Zabbix etc. This can be automated with Chef in such a way that when a new server is launched in to the cluster, it will be automatically added to the Nagios list of monitored servers.

(ii)You can also use excellent SaaS based monitoring apps like Pingdom, mon.itor.us, site24x7.com etc for monitoring and alerting via email, SMS or Twitter.

(iii)Custom scripts or tools like Munin & Monit for monitoring and restarting services if it crashes.

 

Backup

You can keep copies of code in an S3 Bucket and sync it with tools like s3sync with every update. For DB Backup, in addition to automated RDS Backup, you can take periodical standard DB backups using mysqldump and store it in S3 bucket.You can also use EBS volumes for keeping replica of code and DB Backup with periodical snapshots to S3.

An important thing to note about S3 storage is it is only a Highly available Storage System. It is not backed up automatically. That means if you delete anything manually from s3, it will be forever gone unless you have manually backed it up with multiple copies in S3. So make sure that you have enough backups available in S3.

Resolving the “500 OOPS: vsf_sysutil_recv_peek” error on FTP login to an aws ec2 instance

Last day I was stuck with an error while trying to login via FTP to an ec2 instance. The ftp server configured in the instance was vsftpd. I tried to login in both active and passive mode , but was not successful. I even tried changing the ftp server installed to pureftpd and proftpd thinking whether it was an issue with vsftpd , but that too didn’t help.

The error “500 OOPS: vsf_sysutil_recv_peek” is quite common in ec2 instances with FTP. The error was due to a missing kernel module named “capability” in the instance. Compiling the module to the kernel fixed the issue !. So use modprobe to compile in the module. Read more…

Resolving the degraded instance scenario of AWS ec2

There are times when you might receive certain warning messages from amazon.

“Hello,

We have noticed that one or more of your instances are running on a host degraded due to hardware failure.

i-111111

The host needs to undergo maintenance and will be taken down. Your instances will be terminated at this point. We recommend that you launch replacement instances and start migrating to them.” Read more…