Huge thanks to everyone who responded to my invitation to blog on Encryption and Data Protection for this month’s T-SQL Tuesday.
I got what I hoped for, which was a wide(ish) interpretation of the topic. Posts from those who are clearly advocates of encryption, and from those who have scepticism around the encryption approaches that some people take.
We’ve also got discussions about the importance of basic data governance, and the protection of intellectual property.
We’ve got useful advice and warnings of gotchas.
In the order that the posts came in, here are the contributions:
Rod Edwards https://www.sqlrod.com/post/tsql-tuesday-tde-behaviour-and-status-reporting-a-curious-case
Rod demonstrates how the GUI and DMVs can report misleading information about the state of encryption when something has gone wrong, and how to interpret them correctly. He also shows how enhancements in SQL Server 2019 help us out.
Rob takes an overview of encryption, but makes the important point that nothing is foolproof. There is always a weak point. He also stresses the importance of having trustworthy staff – particularly those with high levels of access.
Jiri discusses how developers of Software products can protect their intellectual property when releasing their SQL Server code and data to run on customer’s infrastructure where you can’t control who can access code and data. As Jiri states, there is no perfect solution.
Andy Yun (better know to many as SQLBek) https://sqlbek.wordpress.com/2023/10/10/t-sql-tuesday-167-tde-and-data-protection/
Andy takes a hard-swipe at Security Theatre – where we do things to check boxes on security checklist, but achieve little or nothing in the process, I’ve certainly seen a lot of that in my time. He’s also not a fan of TDE! I agree with a lot of what he says, I still think there is a place for TDE – but the scenarios it protects you from are pretty limited. If you really want to protect your data you need to consider a more complete solution with at-rest encryption as just one layer.
Deborah talks about the importance of knowing your data and I’d agree this comes above all else in protecting it. If you don’t know what you store or where you store it, how can you manage and protect it effectively? Knowing your data is the first step in good data governance.
Chad reminds us of how TDE interacts with tempdb, and the impact that can have on servers where you have a mixture of encrypted and unencrypted databases.
Jeff talks about how good security is a layered approach and how the human factor is the biggest threat. He gives a good approach to protection which is to deal with the “low hanging fruit” and move up from there.
Rob discusses how encryption can seem complex and daunting, but how in reality it is now easier than ever to encrypt your data. He refers to a talk he gave where he was able to demo all the main forms of encryption with practical examples and code in under an hour.
Thanks again everyone!