Voice of the DBA

Tag: Always Encrypted

  • Encryption Libraries

    One of the ways we work to implement better security is through the use of encryption. I remember the early days of PGP, VPNs adding end to end encryption, IPSec becoming available, and more. I was excited to see column level encryption come in SQL Server 2005, but it was a bit complex to implement and had issues. The evolution over time with TDE and Always Encrypted were welcome additions, but we can do better.

    Handling encryption in the database can provide some protection, but really, we’d like to see an end to end solution, that ensures data is protected on the wire as well as in the db. Microsoft does a lot of research, and released a Simple Encrypted Arithmetic Library (SEAL) as open source code. This is a C++ library that developers can use to protect their communications. Last year they also released a .NET wrapper to make it easier for a wide variety of developers to use.

    There is a lot more to deal with when handling encrypted at scale. Indexing and search operations are difficult and complex, and can require lots of resources. I think that the Always Encrypted in SQL Server 2019 with Secure Enclaves is a good evolution, but it’s complex to set up, tooling is poor, and maybe more disconcerting, there are potential vulnerabilities with secure enclaves on Intel chips.

    Certainly a combination of developers being able to use something like SEAL and a linkage with a secure enclave on the server for set-based operations might be something we can look forward to in the future. I hope so, as security continues to be a challenge for many of us.

    I’d also like to see more software that is built to ensure users can’t access huge amounts of data, and perhaps easier ways to control the export of large amounts of data. We do need this capability, but we also want very few people, or no people and only a process, with rights to do so.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • High Prices for High Security

    I was excited to see the new Secure Enclave technology come to Always Encrypted (AE) in SQL Server 2019. I’ve thought that the way Microsoft implemented the AE technology in SQL Server 2016 was a start and a good step forward, but it had too many restrictions. Kind of like Availability Groups in 2012 and In-Memory technology in 2014. A good idea, but not really ready for most of us to use.

    One of the biggest restrictions in SQL Server 2016, in my opinion, was the inability to run range queries on encrypted data. These are the greater than, less than, and LIKE items that many of us need to use in applications. This made sense, since the server doesn’t know how to decrypt the data, but many applications need these queries.

    That goes away in SQL Server 2019 with Secure Enclaves. I’m looking forward to testing and working with this, but there’s one interesting limitation for me. The SQL Server computer that runs the secure enclave must meet one of these requirements. It has to run on Windows 10 or Windows 2019 Server – Datacenter Edition. There also has to be an HGS server for attestation, but this can be a WS2019 Standard Edition.

    Is it a big deal? I don’t know. Windows Server 2019 lists  with Datacenter at $6155 and Standard at $972. That means it will cost me $5,183 for Always Encrypted on my SQL Server. Not a bad price for the encryption and additional security. An HSM appliance goes for quite a bit. One in Azure is $5k + $4.85/hour and most of the enterprise appliances I’ve priced at $10k+. And you need two.

    In some sense, maybe this doesn’t matter. The cost of $5k isn’t much, especially when you consider the downside of not using encryption and having a data breech. You don’t need this on all SQL Servers, just those that need AE, and even then you can run SQL Server Standard Edition. The cost of core licensing is likely going to already be quite a bit and this is just like having to pay for a few more cores. Is this an impediment for your organization?

    At first this seemed like a burden, but the more I look at it, $5k isn’t a lot for encryption on your server. I hope this doesn’t deter organizations from adopting AE, and more importantly, I hope Microsoft continues to invest in this technology. I’d like to see multi-certificate support and the ability to easily revoke access for a compromised system while I deal with any security issues. There are other things, and we’ll see how this evolves in the future.

    Steve Jones

    Listen to the podcast at Libsyn.

  • Getting Started with Encryption in SQL Server 2016

    The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

    Length: 60-75 minutes

    Code: Github

    Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx