Category: Security

Matthew McGiffen puts the keys behind lock and key:

In this quick post we’re going to look at an additional step you should take to secure your TDE databases. This is a step you won’t find in any other documentation on setting up TDE in SQL Server that I’ve seen, so it probably bears a little explaining.

Matthew has solid advice, though if someone does get the Local Admin credentials (e.g., through a spearphishing attack), that person could still eventually gain access to the underlying data. I get that at some point, things will break, so it’s mostly a matter of being aware what can cause that break and minimizing the likelihood of it occurring.

Comments closed

Matthew McGiffen enables transparent data encryption on a database:

Transparent Data Encryption (TDE) is one of the easiest ways of encrypting your data at rest. In the previous posts we looked at what TDE is and how it works.

In this post we go through the steps of setting TDE up. You can set up TDE when you first create a database (which we will look at in this post), or you can apply it to an existing database (which we’ll cover in posts I’ve got coming up). In the latter case, once TDE has been enabled it will encrypt your existing data in the background. In either case the steps are the same.

Read on to see what you need to do.

Comments closed

Matthew McGiffen explains one area of limitation with transparent data encryption:

TDE encrypts data stored on the file system, so it should be pretty clear that we are trying to protect ourselves from an attacker who gets access to our files. You would be right in suggesting that shouldn’t be allowed to happen. Access controls should be in place to prevent inappropriate access. The reality though is that sometimes we get hacked and someone is able to work around our access controls. Sometimes backup files are stored offsite with a different organization where we do not control the access. That is why we have encryption – encryption is an extra line of defense. TDE offers no protection however against individuals who have direct access to query the database.

Let’s say someone does get access to our files – does TDE mean we are still sufficiently protected?

My problem with TDE is something Simon McCauliffe wrote about a few years back (Wayback Machine link because the actual site went down in 2020): if you have root-level access to the server, you can ultimately get access to all of the keys to break TDE. I suppose the level of effort involved is high and that will mitigate the risk, but it’s always there.

2 Comments

Lee Markum continues a series on SQL Server security. Part 2 looks at sp_DBPermissions:

Again, the internet is awash with scripts and options so let me give you a place to start.

Kenneth Fisher’s sp_DBPermissions

From the comments in the stored procedure, “This stored procedure returns 3 data sets. The first data set is the list of database principals, the second is role membership, and the third is object and database level permissions.”

Part 3 reviews security options in sp_Blitz:

Open source provides a lot of fantastic scripts and software. One of the more popular ones is the First Responder Kit. In that collection of scripts is sp_Blitz. This script is great because it provides a lot of information. The script results can also be problematic, because it provides so much information. On my little SQL Server I use for testing scripts and writing demos, sp_Blitz returned 68 rows across 8 priority levels or categories of issues. Again, wonderful if you want to see all the things! But, what if you’re trying to focus on a particular type of issue, like security? What do you do then?

Comments closed

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

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