News
Q&A with Leonard Lobel: Top New Security Features for Developers in SQL Server 2016
SQL Server expert Leonard Lobel explains exactly how Always Encrypted, Dynamic Data Masking, RLS and more can make a real difference in securing your enterprise databases, plus he shares his favorite overall new feature in SQL Server 2016.
Leonard Lobel is just one of the many Microsoft MVPs that will be speaking at Visual Studio Live! Austin 2018 this April. Lobel's sessions at the conference will focus on one of his areas of expertise, SQL Server, and we were particularly intrigued by one of his presentation titles, "New SQL Server Features for Developers," so we took the opportunity to ask him more about the topic. Here's what he had to share with us:
What do you think is the #1 most important new security feature of SQL Server 2016?
I'd have to say Always Encrypted, because it eliminates the most common reason for not migrating to the cloud. Because although SQL Server has had encryption capabilities for a long time, those capabilities have always been based on keys that, themselves, are stored in the database.
"Older encryption features only protect data at rest. But moving to the cloud means separating those who own the data from those who manage it, and also protecting data as it travels the network in the cloud. Always Encrypted addresses both these concerns."
Leonard Lobel, CTO and co-founder of Sleek Technologies
Furthermore, those older encryption features only protect data at rest. But moving to the cloud means separating those who own the data from those who manage it, and also protecting data as it travels the network in the cloud. Always Encrypted addresses both these concerns, because it is based on client-side encryption keys that are never stored in the database, which also ensures data is encrypted across the network, bi-directionally.
Can you share a bit more about Always Encrypted in SQL Server 2016?
Like I mentioned above, it's a great feature because it addresses the most important security concerns for migrating to the cloud. But you do need to keep in mind that this is a hybrid feature, with a rich client-side component. So first, to use the feature, your client needs to use a supported Always Encrypted driver. To date, this includes the standard .NET SqlClient provider, JDBC, and Windows ODBC. Other client platforms can't use Always Encrypted. You also need to concern yourself with client-side certificate management. One option to alleviate this burden is to use Azure Key Vault, which centrally manages client-side certificates. But otherwise, you're going to need to distribute the CMK (column master key) to every client that needs it, or they won't be able to encrypt/decrypt data in the database.
Why is Dynamic Data Masking (DDM) so important for developers?
Too often, developers struggle to gain access to sample data that adequately represents real data. In many cases, production data is off-limits, test samples are scarce, and developers are forced to hack together their own samples, or just work completely in the dark. DDM makes it easier for security-conscious administrators to grant SELECT permissions on a table with sensitive data to developers, as long as the sensitive columns get masked. There is zero impact on the environment, because DDM doesn't actually modify data in the database, and developers can actually work with real data.
Can you explain what you like (and anything you dislike) about RLS in SQL Server 2016?
The really nice thing about RLS is that, fundamentally, it works on a JOIN. So it performs very well, and does exactly what you need it to do -- nothing more, nothing less. But, you do need to be able to express the logic around whether or not a row is accessible inside a single inline table-valued function (TVF), because multi-statement TVFs are not supported. This is actually a good thing, because it protects against potentially disastrous performance with an ill-behaved TVF, but it can pose a challenge when your row-access logic is very complex.
Are there any security features that could go wrong if you implement them incorrectly?
DDM is very binary; it's either on or off (based on permissions), so there's little that can go wrong. RLS, on the other hand, can wreak havoc on your system if you have the slightest logic error in your RLS security predicate function. This can result in blocking users access to data they should be able to access, and worse, it can grant users access to data that they should definitely not be able to access. Finally, AE should generally work as expected; if the CMK (column master key) is available, then the client can use the feature, otherwise they can't. But you can still trip yourself up if you don't use the right encryption type. For example, if you encrypt a column using randomized (versus deterministic) encryption, then you won't be able to query or join on it.
What else do you love/hate about SQL Server 2016?
Without hesitation, I can say that my favorite SQL Server 2016 new feature is temporal data. The ability to go back in time and retrieve data as it appeared at any point is huge, and solves many difficult problems for time-sensitive data. On the downside, while there isn't anything I really hate about the product, it would sure be nice if we could write our own masking functions for DDM, and not be limited to the four built-in masking functions provided by Microsoft (default, partial, email, and random).
Join your peers at Visual Studio Live! in Austin! Find out more here!
About the Author
Becky Nagel is the former editorial director and director of Web for 1105 Media's Converge 360 group, and she now serves as vice president of AI for company, specializing in developing media, events and training for companies around AI and generative AI technology. She's the author of "ChatGPT Prompt 101 Guide for Business Users" and other popular AI resources with a real-world business perspective. She regularly speaks, writes and develops content around AI, generative AI and other business tech. Find her on X/Twitter @beckynagel.