These days it’s only a matter of time before our firewalls or access controls are breached. Then what?
In a multi-tiered security strategy, data encryption is a critical next line of defense. Data encryption is also mandated by a number of widely applicable regulations, including PCI-DSS and GDPR.
Fortunately for SQL Server users, Microsoft has developed a range of capabilities that enable you to encrypt database files at rest, in transit, in backups, when being accessed and even “always.” This post will give you an overview the five types of encryption that SQL Server offers.
SSL Transport Encryption For Data In Transit
Similar to how web applications secure traffic between the server and the browser, you can configure SQL Server to use Secure Sockets Layer (SSL) to encrypt data in transit between the server and the client.
This capability is available across all supported versions and all editions of SQL Server. It’s a great way to thwart so-called “man in the middle”/proxy attacks because it makes network traffic almost impossible to read.
For more information, including how to install a certification a SQL Server to support SSL, see this Microsoft Support page.
Backup Encryption
SQL Server allows you to encrypt SQL backups at the whole file level. Available for both Standard and Enterprise editions in SQL Server 2014 and newer, this is a powerful security measure as it protects backups even at offsite locations.
This feature gives you a choice of encryption algorithms and can use either a certificate or asymmetric key. You can also use backup encryption and Transparent Data Encryption (TDE, see below) at the same time (but with separate certificates/keys).
For more information on implementing cell-level encryption, see this Microsoft Docs page.
Transparent Data Encryption (TDE)
Available since SQL Server 2008 for Enterprise editions only, TDE lets you encrypt SQL data “at rest” within the files on disk. This protects the physical media—including the entire database, log files and any backups or snapshots—from being read in the event of unauthorized access to the media. (Data in unencrypted files can be accessed simply by restoring the files to another server.)
TDE is “transparent” in the sense that no changes are required for client or server applications to use a TDE-encrypted database. When SQL Server mounts an encrypted data file, it uses a database encryption key (DEK) to decrypt the data during use and then re-encrypts it again before writing it back to the file. A limitation of this approach is that data encrypted with TDE is unencrypted and potentially vulnerable both in memory and over the network.
For more information on implementing TDE see this Microsoft Docs page.
Cell-Level Encryption
SQL Server’s Cell-Level Encryption lets you encrypt specific columns within a database that contain sensitive data; e.g., credit card numbers, social security numbers, passwords, etc. This feature is available in all SQL Server editions.
Unlike with TDE, when data encrypted with Cell-Level Encryption is selected in a query, it remains encrypted by default, even in memory. However, all it takes to decrypt it is a call to the decryptbykey function in the query. Further, the need to use a function call for decryption means code changes in existing applications and queries.
For more information on implementing cell-level encryption, see this Microsoft Docs page.
Always Encrypted
Always Encrypted is a new (since SQL Server 2016) way to do column-level encryption for client applications using up-to-date data libraries. Always Encrypted encrypts data transparently at the client via the data connection layer, with no code changes required (though it requires a driver on client systems).
Data thus secured remains encrypted in transit, in memory and at rest—making it the only option for securing data from misuse by even the most privileged SQL users, like sysadmins. This makes it a good choice for applications that require separation of data owners and managers.
However, since SQL Server isn’t doing the encrypting, many functions won’t work with this approach. In particular, you can’t sort, index, look for string fragments or do calculations on data that’s encrypted.
For more information on implementing cell-level encryption, see this Microsoft Docs page.
In Conclusion
Microsoft has a strong focus on security, and this is evident in the wide range of options available to SQL Server users. But every organization’s needs are different, and there is no one-size-fits-all approach to implementing SQL Server encryption. Your SQL Server version and licensing scenario, application requirements, business processes, performance needs, regulatory environment and security risk profile will all impact your path to implementing encryption.
For expert guidance on how best to implement encryption in your database environment, including help with configuration and key management, contact Buda Consulting.
