Press "Enter" to skip to content

Category: Security

Keys and Certificates with TDE

Matthew McGiffen has a big keychain to store all of those keys:

When you first look at the encryption hierarchy for TDE in SQL Server it can be a bit daunting. There seem to be a lot of objects involved and it might not be clear why each is required. It can be tempting to skip a full understanding of all the objects and just get on with setting things up – which is relatively straightforward.

I’d encourage you not to do that and I’ll explain why. There are a lot of scenarios that might crop up in the lifecycle of a TDE protected database instance. Recovering a protected database from backup. Migrating database from one server to another. Managing high availability. The list goes on.

Remember: the bigger the keychain, the more powerful the man.

Comments closed

A Primer on Transparent Data Encryption

Matthew McGiffen continues a series on encryption in SQL Server:

TDE is referred to as a “transparent” form of encryption. What that means is that the process of encrypting and decrypting data is fully performed in the background. The queries we write to access data are unchanged whether TDE is enabled or not. So, enabling TDE has no impact on application functionality, does not require refactoring of code, and is therefore relatively easy to implement. TDE encrypts all the data in a database, so you don’t need to choose which data items to encrypt.

Read on to learn more about it, including specific items TDE does not cover.

Comments closed

Checking if Cross-Database Ownership Chaining is On

Tom Collins performs a check:

Cross-database ownership chaining is a SQL Server  security feature allowing database users  access to other database objects hosted on the same SQL server instance, in the case where database  users don’t have access granted explicitly

Tom shows us whether it is on as well as how to enable it. I’d recommend not enabling it at all and using module signing instead.

Comments closed

Encryption Recommendations for SQL Server

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

Encryption Features in SQL Server

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

Managing Azure Log Analytics Data Access

Bruno Gabrielli wants to limit data access to Log Analytics:

I am back with another important topic arising from my customers’ visits. How can I give very specific access to Log Analytics data, whether they be Security or Monitoring data?

Tricky one, isn’t it? A very simplistic answer could be: “manage your access list through IAM on the workspace”, but this is not enough. Say, for instance, that you would give scoped access to data coming from specific resources or, even more complicated, you would like that given the same resource one team can see some info and another one all the rest.

Looks complicated, but hey … good news: this is doable

Read on to learn how.

Comments closed

Encryption and Regulatory Requirements

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

The Purpose of Data Encryption

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

Roll Your Own Row-Level Security for the Serverless SQL Pool

Randheer Parmar wants row-level security:

Row Level Security is a very key requirement for most database or data lake applications. Most of the databases are having natively build row-level security but Synapse serverless SQL pool doesn’t support this inbuilt functionality. In this article, we will see how to implement it.

Row-level security has always seemed to me to be a great idea but not one I can implement because its performance cost is always too high.

Comments closed