Sunday, February 24, 2013

Encrypting SQL Server Databases

In corporate environments there is always the requirement/policy of data protection. Data is the most valuable asset in every organization, after its human resources of course, as it is with data where the organization’s business processes run and produce results.

SQL Server is one of the leading data platforms worldwide and as such, from SQL Server 2008 (Enterprise Edition) and later, all the necessary mechanisms for ensuring data protection are available within the Database Engine and can easily be used.

SQL Server 2008 introduced Transparent Data Encryption (TDE); a mechanism allowing the DBA to easily encrypt databases without affecting their operation as it allows full transparency to the database users and applications. Based on TDE, the DBA encrypts the database using a master key and a certificate. The user accesses the database transparently as the encryption is automatically maintained on the Database Engine-level and in the case of a potential data theft, i.e. someone illegally copies the database files (data and log files), when he tries to access the database it will not be allowed because the database is encrypted. You can find a simple example on how you can use Transparent Data Encryption in one of my older posts here.


As mentioned above, Transparent Data Encryption is available in SQL Server 2008 or later. However, if you would like to achieve database encryption in earlier versions of SQL Server, you can do it by using a special feature of Windows on the file system-level, that is Encrypting File System (EFS) which was introduced in version 3.0 of NTFS and provides file system-level encryption. EFS is available from Windows 2000 onwards. The way EFS works is transparent, yet very powerful. A high-level description of its operation is the following.


For encrypting files:
1. With a symmetric file encryption key (FEK) generated for this purpose, EFS encrypts the file.
2. Then it encrypts the FEK using the active Windows user's public key.

For decrypting files:
1. EFS decrypts the encrypted FEK using the Windows user's private key.
2. Then it decrypts the encrypted file using the decrypted FEK.


So, in the case you would like to encrypt a database in SQL Server 2005 or earlier using EFS you could achieve it as follows:

1. Log in to Windows using the user account that is used by SQL Server Database Engine.
2. Log in to SQL Server Management Studio (SSMS).
3. Detach the database from the SQL Server Instance.
4. Encrypt the database files (data and log files) on the file system-level ([File Properties] - [Advanced] - [Encrypt contents to secure data]).
5. Verify the encryption of the database files using the Windows command "cipher.exe" in the folder that contains the database files. It will return the value "E" for the encrypted files.
6. [CRITICAL] Export the Windows user's personal certificate with the private key ([Start] - [Run] – “certmgr.msc”) and store it on a safe location (i.e. removable storage).
7. Attach the database back to the SQL Server Instance.
8. If any problems, try to restart the SQL Server Instance.

Be extremely cautious when running the above procedure because in the case you change the user account that runs the SQL Server Instance and the new user is not authorized to access the database files, then you will lose access to your data. Always backup the user’s certificate/private key which can be used to decrypt the encrypted files. Always be careful when using data encryption mechanisms.

Also, if you encrypt database files and attempt to start the SQL Server service as any user other than the user that encrypted the database data/log files in the first place, the instance will not start and/or your database will be set to suspect.

A last thing you should have in mind when using EFS for encrypting SQL Server 2005 or earlier databases, is that EFS will affect the performance of SQL Server as there is an overhead whenever decrypting the underlying database files. There are many parameters that can be taken into consideration when it comes to the SQL Server performance degradation such as: the supporting storage system and its RAID levels, the OS configuration and more.   

Related Microsoft Articles:

--
My Latest Projects:

[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: