SQL Server Backup – Handling Always Encrypted Feature

Downsides of Transparent Data Encryption compared to Always Encrypted:

• Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable
• Whole database only
• All data is encrypted the same way

• Backup compression can take longer and be counter-productive

  • Well, actually, there are some improvements here in SQL Server 2016 that defy what we've typically known about trying to compress encrypted data - it's much better than previous versions, but presumably still worse than only encrypting a handful of columns (untested)
• tempdb also inherits encryption – stays even after disabling TDE
• Requires Enterprise Edition
• Data always accessible to sysadmin

Always Encrypted addresses all of these issues in part or in full:

• Data is protected at rest, in motion, and in memory - much more control over certs, keys, and exactly who can decrypt data
• Can be just a single column
• Encryption type is a choice:
  • Can use deterministic encryption to support indexes and point lookups (say, SSN)
  • Can use random encryption for higher protection (say, credit card number)
• Since it's not database-wide, backup compression isn't necessarily affected - of course the more columns you encrypt, the worse luck you'll have
• tempdb is uninvolved
• As of SQL Server 2016 Service Pack 1, Always Encrypted now works in all editions
• Data can be protected from sysadmin (but not sysadmin AND Windows security/cert/key admins, in other words you can separate responsibility as long as those two groups don't collude)

There is a limitation, though, and that is that not all drivers and applications can deal with the encrypted data directly, so in some cases this will require updating/changing drivers and/or modifying code.

Regarding your first question, which version of SSMS, are you using? If it's older than 17.0, I recommend trying the newest version (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).

Based on your description, I suspect you are using SSMS 16.x. The likely root cause of this problem is that the objects in your database have external dependencies (on objects in other databases) or have broken dependencies. In SSMS 16.x, the Always Encrypted wizard does a comprehensive validation of all dependencies in the entire database, including objects not dependent on that tables that you are trying to encrypt. We have fixed this problem in SSMS 17, by limiting the validation only to the tables containing the columns you are encrypting and the wizard does not error out anymore when encountering a validation error. Instead, it completes encryption and produces a warning, allowing you to investigate/address the issue later.

Regarding your second question - you need to designate a primary key column for each table.