Category: Security

Matthew McGiffen shares some thoughts:

We looked earlier at the various places our data lives – on disk, in memory, in-transit across the network. A good strategy will protect all of these locations – sometimes with multiple layers. Exactly how you go about doing that for your applications may vary – but as long as you are on SQL Server 2016 or higher there is a default strategy that you should consider. This combines a number of the available SQL Server features to provide the best protection.

This story might look a bit different for Azure SQL Database (though you can still use Always Encrypted there) and will look very different on Azure Synapse Analytics dedicated SQL pools, which don’t have Always Encrypted at all.

Comments closed

Matthew McGiffen gives us a high-level overview of what’s available in terms of encryption in SQL Server:

SQL Server has had the native ability to encrypt data since SQL Server 2005. This included functionality that could be used to encrypt individual items and columns of data as well as the Transparent Data Encryption (TDE) feature which was available with the enterprise edition of SQL Server and could be used to encrypt all data where it is stored on disk. We then didn’t see significant additions to the features available for encryption until 2016 when Microsoft added Always Encrypted for column encryption. In SQL Server 2019 Microsoft made TDE available in standard edition, and also in SQL Server 2019 the ability to use enclaves was added to Always Encrypted to improve the available functionality for interacting with encrypted data. Finally in SQL Server 2022 further enhancements were made to the set of functionality available when working with Always Encrypted with enclaves.

Click through for a table and some details.

Comments closed

Matthew McGiffen talks regulation:

One of the reasons you may be considering encryption is due to the relevant data protection regulation: either because the regulation specifies that data should be encrypted or because of the large potential penalties where there is a data breach. Some US companies have been hit by fines in the hundreds of millions of dollars following data breaches, so we are talking large sums of money. In Europe the largest fines so far (under the GDPR) have been related to misuse of personal data or consent (750 million euros is the highest I am aware of), but there have been fines of up to 30 million euros for data breaches. In the case of a breach, you could also be sued by individuals whose data has been accessed or by class action.

Read on for more thoughts on the topic.

Comments closed

Matthew McGiffen thinks through the benefits of encryption:

On the face of it, this is a very obvious question with a very obvious answer. We want to prevent data from falling into the wrong hands. In practice, it gets a little more complicated.

Exactly what types of attacks do you wish to be protected against? It’s good if we make sure our data is encrypted where it is stored on the disk, but that doesn’t help us if an attacker gains direct access to write queries against the database. We might encrypt data held in columns, but does that still protect us if the unencrypted data is being passed back across the network to our application and an attacker is intercepting our network traffic?

I did a Ctrl-F for “compliance” and didn’t see anything. Nor for checking boxes to keep regulators off our backs. It seems Matthew is going for the good answers here.

Comments closed

Matthew McGiffen lays down a stake:

You can see that a small majority of DBAs who follow Brent on Twitter and respond to polls use some form of at-rest encryption. I guess that means that in the larger population the majority aren’t encrypting their data at all.

There are a number of reasons why that might be the case, the only good one is that your database doesn’t hold any personally identifiable information (PII) or sensitive data which will certainly be true in some cases but not in most.

Read on for more of Matthew’s thoughts and do check out his new book. My copy’s supposed to arrive today.

1 Comment