By: Matt Wisor
Data breaches cost U.S. businesses over 1.2 trillion dollars in 2019, according to a ForgeRock Consumer Identity Breach Report cited by Cyware Hacker News. With stakes so high it is no surprise that data security is one of the top concerns expressed by executives today. Protecting sensitive data has never been more important.
Let’s take a quick look at SQL Server 2019 encryption and how the Microsoft product safeguards data using the Always Encrypted with secure enclaves feature. We begin with a brief history of Always Encrypted technology…
SQL Server 2019 Encryption: Always Encrypted
Always Encrypted was introduced in SQL Server 2016 to secure sensitive data, such as credit card numbers and Personally Identifiable Information (PII) like social security numbers. As the name implies, protected columns are encrypted at all times – not only at rest in the database, but as data travels across the network.
Decryption never occurs until data reaches the client because the client has sole access to the cryptography keys. As a result, secured columns are always shielded from cybercriminals and highly privileged users (DBAs and administrators).
Data is encrypted using two types of keys: Column Encryption Keys (CEKs) and Column Master Keys (CMKs). One or more columns are encrypted using a CEK, but storing the CEK in the database would leave it vulnerable to compromise. To solve this problem, a CMK is used to encrypt one or more CEKs and the CMK is stored externally in the Window certificate store or in an Azure Key Vault. For added security, only the encrypted CEK values are stored in the database along with metadata information used to locate the CMKs.
Cryptography keys are generated using SQL Server Management Studio (SSMS) and there are two types of encryption to choose from, Deterministic and Randomized. Deterministic encryption always produces the same encrypted value for a given plain text value, which is less secure, but enables column joins and equality comparisons. In contrast, randomized encryption generates numerous encrypted values for the same plain text value. While more secure, equality comparisons and joins are not possible due to the unpredictability of randomized encryption.
Always Encrypted with Secure Enclaves
Microsoft vastly improved upon Always Encrypted in the release of SQL Server 2019. Performance increased and the computational limitations related to client-side processing were mitigated. Such enhancements were made possible through the integration of secure enclaves.
Think of an enclave as an extension of the client that runs on the server. It is a portion of memory within the SQL Server process that is not visible nor accessible by anything else on the machine. Not the hosting process, debugger, or even the normal operating system. Any data or code in the enclave cannot be viewed from the outside. The enclave functions as a trusted region where sensitive data is processed on the server, bypassing the need to move data to the client.
Cryptographic operations carried out on the server are faster and more reliable, particularly on large amounts of data. Furthermore, server side processing provides full support for computations, such as pattern matching and comparisons.
Secure Enclave Attestation
How does the client communicate with an inaccessible enclave? This is done so through a verification process known as enclave attestation. An external attestation service authenticates the enclave to the client thereby establishing a trust. Only Virtualization-based security (VBS) enclave isolation technology is currently compatible with SQL Server 2019. The supported runtime attestation process is Windows Defender System Guard, which requires Host Guardian Service (HGS) as the attestation service.
In order for the process to work, the SQL Server host machine must be registered in the HGS service and the client needs to be set up for HGS attestation. Once the authenticity of the enclave has been verified, the client establishes a secure channel to interact with the enclave. For example, when a query requires use of the enclave for computations on protected data, the client driver passes the CEKs to the enclave through the secure channel. Then the query and any encrypted parameters are sent to the enclave for processing.
In Closing
Always Encrypted with secure enclaves provides effective data protection while leveraging server side processing for increased performance and full support of rich computations. More information about security enhancements in SQL Server 2019 may be found in the Mission-Critical Security section of Microsoft’s What’s new in SQL Server 2019 (15.x) document.
Questions?
Thanks for reading. We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, Star Wars, or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!