Confidential data should stay… confidential

In October 2023 the Copenhagen based chain of real estate agents, EDC, was hit by a ransomware attack. The hackers behind the attack – the Russian group “Black Basta” –demanded a ransom of 6 million US dollars. The criminals stole 2.5 TB of data including thousands of scanned passports, driver licenses and other means of providing identity. About 100,000 social security numbers with accompanying names and addresses was included in the stolen data. The data has now been published because EDC refused to pay the ransom.

As often before, the attack was initiated by a phishing mail. Unfortunately, I think that no matter how much periodical phishing training we conduct, we cannot guarantee that no one in the organization will ever be fooled by a real phishing mail at some point. There is no way around better fencing in our data – especially sensitive data as personal data (GDPR) and IP from sensitive research.

A significant amount of business data is stored in SQL Server databases and recently I discussed with one of our senior database architects how to better protect data in SQL databases.

Transparent Data Encryption (TDE) is a valuable security feature for safeguarding SQL Server databases. It encrypts data at rest, ensuring that if physical media (such as drives or backup tapes) is stolen, the data remains protected. However, it’s essential to recognize its limitations:

• All or Nothing: TDE encrypts the entire database; there’s no option to encrypt only specific data subsets.

• Access Control: TDE doesn’t restrict data access through the database engine; data access remains unchanged.

• Transport Encryption: TDE doesn’t automatically encrypt data in transit; separate configuration is needed for encrypting data connections.

“Lawfulness of processing”

The General Data Protection Regulation (GDPR), Article 6 addresses the lawfulness of processing personal data. Specifically, it outlines conditions under which processing is considered lawful. One of these conditions pertains to restricting access to personal data solely for legitimate business purposes. In other words, administrators should not have the ability to access personal data out of mere curiosity; rather, a clear business justification must be established for such access, as outlined in GDPR, Article 6.

Encrypt and decrypt data at client side

SQL Server’s Always Encrypted feature is a robust security mechanism that protects sensitive data, which maintains separation between data owners and administrators.

Key points include:

• Client-Side Encryption: Always Encrypted encrypts specific table columns at the client side, ensuring that the encryption key remains unknown to the database. The client-side encryption is transparent to the applications. The Always Encrypted-enabled driver installed on the client computer automatically encrypts and decrypts sensitive data in the client application.

• Data Ownership and Confidentiality: Only data owners with access to the encryption keys can read the data, preventing unauthorized access by administrators or cloud operators.

• In-Use Encryption: Data remains encrypted even when it’s in use within the SQL Server’s memory.

• Certificates are Column Encryption Keys, which are used to encrypt data in encrypted columns and Column Master Keys, which protect Column Encryption Keys. Column Master Keys must be stored in a trusted key store outside of the database system, such as Azure Key Vault, Windows certificate store, or a hardware security module.

Always Encrypted, while a powerful security feature, is not without its considerations. It demands that encrypted database columns be created with specific encryption directives. Additionally, there are limitations on how SQL queries can be constructed within the application. Furthermore, it’s essential to recognize that this encryption inherently introduces a CPU overhead, particularly on the client side. As the saying goes, ‘there is usually no such thing as a free lunch’.

Limitations when using Always Encrypted

When using Microsoft SQL Server Always Encrypted, there are some important limitations that developers should be aware of:

• Most data types are supported, but there are a number of special data types that are not supported. Please refer to the current Microsoft documentation for a current list of data types.

• In general, Always Encrypted allows for limited confidential queries on encrypted data. These queries involve equality comparisons (e.g., point lookup searches or equality joins) and these require the encryption type to be deterministic.

In fact, for safety reasons Microsoft recommends combing TDE and encrypting data connections with TLS 1.2 with Always Encrypted:

• TDE will encrypt the full database file and thus will ensure that the whole database file including backups are encrypted. The downside to this is that the backup cannot be compressed.

• TLS 1.2 will ensure that all data in transport is encrypted which has become the good recommendation for all data communication including between internal system components.

• Always Encrypted will further ensure that sensitive data is always encrypted, also to users logging into the database with administrative rights.

For further reading and more technical detail, I can recommend Microsoft’s blog posting “Transparent data encryption or always encrypted?” where TDE and Always Encrypted are discussed in detail.