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!
