I love movies. I don’t know if I qualify as a movie buff, but I know I have spent a good chunk of my lifetime watching films. One of my favorite films is The Magnificent Seven. (We won’t talk about the awful remake; I like to pretend it never happened.)
The Magnificent Seven is based upon the Kurosawa masterpiece The Seven Samurai. The two movies share the same plot: bad guys threaten a village, villagers hire good guys to protect them, bad guys come back, fighting ensues, and happy ending for most.
These days I can’t help but think of the parallels between that plot and the current struggle we face with data security. Consider that we have the bad guys (let’s call them adversaries). These adversaries want our data. It is up to us, the villagers (i.e., data professionals) to find and deploy proper data security measures to guard against data loss or theft. The adversaries come back, the good guys fight back, and there is a happy ending for most.
Today there seems to be a never-ending supply of data breaches and leaks. It is trivial to build and deploy applications. As a result, data security, privacy, and quality all come a distant second to building shiny new apps that upload pictures of cats or to help us identify a hot dog.
I want to help in the struggle against the adversaries. I’ve identified seven data security measures you can use. These ‘Seven Samurai’ will protect against all three possible attack vectors: data at rest, data in use, and data in motion.
Let’s have a look at how the Seven Samurai protect us from each.
Data at Rest
Data at rest refers to data stored in files on disk. Examples are database files, database backups, or applications such as Excel spreadsheets.
Protection for this data includes traditional methods of permissions, firewalls, and anti-virus programs. Those methods are not enough when it comes to physical database files. Should an adversary make their way past, they may be able to copy data files and backups to restore data on their own servers.
To protect data at rest we will use the following Samurai: Transparent Data Encryption (TDE), BitLocker, and Backup File Encryption.
Transparent Data Encryption (TDE)
TDE allows for real-time I/O encryption and decryption of both the data and transaction log files. Encryption happens at the database page level. The pages are encrypted before written to disk, and decrypted when read back into memory. This method also means that the database backup file cannot be restored to a server unless you have the necessary certificate.
BitLocker
BitLocker is a built-in tool to Windows that allows for you to encrypt your data volumes even when Windows is not running. If an adversary was to gain access to your physical server or hard drives they would not be able to remove the disk and access the data by attaching it to a different server.
Backup Encryption
Backup Encryption is a feature for SQL Server that allows you to encrypt the contents of the backup file. This encryption does not need TDE enabled. Just as with TDE, if you don’t have the corresponding certificate you will not be able to restore from the backup file.
Data in Use
Data in use refers to data accessed by those in need. Unfortunately, data in use is more vulnerable than data at rest because those that need the data often end up sharing that data with people that shouldn’t need it in any way. Social engineering is one method an adversary will use to access data in use. But an adversary can also access a spreadsheet on a USB drive that left behind on a bus.
To protect data in use we will use the following Samurai: Dynamic Data Masking (DDM) and Row Level Security (RLS).
Dynamic Data Masking (DDM)
DDM allows for you to limit exposure of sensitive data by applying a mask to columns at the end of a query operation. The performance impact for applying the mask is minimal, because it happens at the end. DDM is a great way to help avoid issues that arise when users take their work home at night but forget a spreadsheet on the bus.
Row Level Security (RLS)
RLS allows for you to create security policies that will filter data as it is returned from the underlying tables. The user has no knowledge that any filtering took place. When combined with DDM, RLS gives you the opportunity to reduce your risk that data in use will fall into the wrong hands.
Data in Motion
Data in motion requires special protection. An adversary can use methods such as a man-in-the-middle attack to access your data in motion. The best way to protect data in motion is to use encryption methods for your sensitive data.
To protect data in motion we will use the following Samurai: Secure Sockets Layer (SSL) and Always Encrypted (AE).
Secure Socket Layers (SSL)
SSL allows for you to encrypt data that is transmitted between a client and the database server. SSL will force the client and the server to authenticate the identity of each other using a “handshake”. After the handshake, the connection becomes encrypted and data can be transmitted in a secure manner.
Always Encrypted (AE)
AE takes place at the column level, encrypting the data such that only the ciphertext is ever stored on disk or brought into memory. That means AE will protect both data in motion *and* at rest, making AE my favorite Samurai. Data encryption and decryption happens only at the client. The only data transmitted between the client and database server is ciphertext.
Summary
Data is the most critical asset that any company owns, it’s time we treat it as such.
At any minute of the day, an adversary has opportunities to access your data. With each interaction your end users have, internally and externally, your surface area of attack increases. The process of accessing, using, storing, and sharing data must be made secure. Data security needs to be a primary focus for every person and company.
There you have it, the Seven Samurai of data security for SQL Server. Individually each has its own strength. Together they form a defense that is strong enough to defeat most any adversary that tries to access data that is not theirs to access.
