Does SQL 2016 allow for column encryption?

• Rod at work

  SSC-Dedicated

  Points: 34056

  More actions

  March 15, 2021 at 10:06 pm

  Go to Answer

  #3858203

  I have a feeling I've asked this question before, but I can't find my having asked it previously. I'm sorry if I've already asked this question.

  We have a need to encrypt some data in the columns that they're stored in. However, we've no idea how that is done, both to store the data encrypted and to work with it in applications we write.

  I work on a team of developers. You know what we developers are like - give us a chance and we'll "roll our own" every time. However, over time I've become more interested in using what might already exist, rather than re-inventing the wheel. However, my colleagues won't wait long. They're already on their way of writing some custom routine which will store the sensitive data encrypted in the columns. They're just trying to figure out where's the best place to store the encryption key.

  However, I'd rather just use what comes with SQL Server 2016, than roll our own. If something does come with SQL 2016. So, here's the questions:

  1. Does SQL Server 2016 have some native way of encrypting data in columns, at rest?
  2. If the answer to #1 is yes, then how do we go about doing that so that sensitive data is protected, but we can still use SSMS, Azure Data Studio, customized Windows and Web apps that we write, so we can query the database?

   

  Kindest Regards, Rod Connect with me on LinkedIn.

• Ken McKelvey

  SSCoach

  Points: 18910

  More actions

  March 16, 2021 at 10:32 am

  Answer

  #3858422

  These links may be of help:

  https://www.sqlshack.com/new-features-in-sql-server-2016-always-encrypted/

  https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

• Roebuck247

  Newbie

  Points: 1

  More actions

  March 30, 2021 at 5:35 am

  #3863485

  A column encryption key is used to encrypt data in an encrypted column.

  Omegle