0

Env: SQL Server 2012

I am just curious to see if there a different procedure when doing backup and restore if the columns are encrypted?

The columns in the tables would be encrypted using this method:

  1. Create the database master key (DMK)
  2. Create a self-signed certificate
  3. Create a symmetric key
  4. Encrypt the column data
  5. Query the encrypted data
jarlh
  • 42,561
  • 8
  • 45
  • 63
Jerry
  • 127
  • 1
  • 9
  • [Column Encryption](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017) are not available in version 2012 AFAIK, it is a feature added in version 2017. – Igor Sep 04 '18 at 19:36
  • @Igor you can use encryption in 2012. You store them as a `varbinary`. The document explains how to do it in 2017, but it doesn't say it isn't available in prior versions (because it is). – Thom A Sep 04 '18 at 19:39
  • @Larnu - do you have a link? Maybe I am thinking of [Always Encrypted](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017)... – Igor Sep 04 '18 at 19:41
  • 1
    @Igor one way would be to select a different version of SQL Server on the web page ;) https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2014#TsqlProcedure . It's possible in 2012 as well (I have encrypted columns on our 2012 instance). I honestly can't remember when it was implemented, but I think it might have been as early as 2005. I imagine 2012 idnt in the drop down as it's out of support now (only extended). – Thom A Sep 04 '18 at 19:46
  • 1
    Might be Always Encrypted you are thinking of @Igor, but that came with 2016 enterprise, or for all from 2016 sp1. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017 hope that all helps. :) – Thom A Sep 04 '18 at 19:48
  • 1
    @Larnu - But you can't filtering/sort that encrypted data though can you? I *believe* that was added with the `Always Encrypted` feature. – Igor Sep 04 '18 at 19:49
  • @Igor no, I don't believe so either (that you can filter on an encrypted column). – Thom A Sep 04 '18 at 19:51
  • Obviously, without the keys your database is reduced to a pile of unreadable blobs, which means you must devise and test a procedure for backing up all associated keys (and storing them securely, and preferably not along with the database itself). [See also](https://stackoverflow.com/q/41032092/4137916). Unlike regular database backups, though, these backups only have to happen when you create or change keys, which is rare. – Jeroen Mostert Sep 04 '18 at 19:54
  • Maybe we went offtopic here. So, when refreshing ,does the keys gets transferred over as well ? What about certificates? – Jerry Sep 05 '18 at 13:11

0 Answers0