5 Flavors of Microsoft SQL Data Encryption
With the constant drumbeat of security vulnerabilities and breaches in the news, protecting data is, or should be, on everybody’s mind. While we know that a firewall is essential to keeping the bad guys out, we’ve also learned that most data breaches happen from behind our firewalls. Good security defenses, then, are multi-tiered. Assuming that our firewalls and permission schemes will eventually be overcome or otherwise thwarted, an essential line of defense is strong data encryption.
For static files, such as a Microsoft Word or Excel document, drive or folder encryption is a viable option. However, SQL Server uses constantly active files with complex dependencies and interactions with other systems and applications. Therefore, the SQL service itself must implement an encryption scheme or schemes. Following is a quick comparison of the 5 kinds of encryption available within Microsoft SQL Server.
SSL Transport Encryption
SSL (Secure Socket Layer) is a common encryption protocol used to encrypt data between network endpoints. It is most commonly associated with web addresses starting with “https” where the “s” indicates secure (encrypted) traffic. Using network sniffers or so-called man-in-the-middle (e.g., proxy) attacks, unencrypted traffic can be easily read. Encrypted traffic is magnitudes more difficult to read (measured in how long it takes to unencrypt without the right keys). Fortunately, web servers are not the only servers that can encrypt data in transit using SSL. SQL Server can also be set up to allow SSL encryption of its data as it travels over the network. This capability is available in all modern versions, across all editions of SQL Server.
Transparent Data Encryption (TDE)
While SSL encrypts data in motion on the network, TDE encrypts data at rest. This means that should someone manage to take a copy of your data files, they would not be able to decipher their contents without the proper keys. When a SQL Server instance mounts an encrypted data file, it uses the keys to decrypt the data as it is extracted from the file during use, and then to encrypt it again before it is written to the drive. That mean