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.
My Latest Projects:
- DBA Security Advisor: Secure your SQL Server instances against security risks.
- In-Memory OLTP Simulator: Easily benchmark SQL Server’s In-Memory OLTP Engine against your custom workload.
- Artemiou SQL Books: Download my latest free books on SQL Server.
- Artemiou Data Tools: See my latest software projects.
Recommended eBooks on SQL Server: