49 votes
Accepted

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

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 ...
10 votes
Accepted

Combining Always Encrypted AND Column level encryption in SQL Server 2016

I have yet to come across a definitive "Best Practices" list regarding multiple encryption types for SQL Server, but in your situation I would probably recommend the following: Encrypt necessary data ...
7 votes
Accepted

What are the advantages of Always Encrypted 2016 vs Encrypted Columns?

The major difference I know is Normal Column Encryption The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as ...
  • 18.4k
6 votes

How to renew or extend the CMK Certificate in Always Encrypted feature?

Always encrypted does not check for validity of the CMK certificate explicitly. Always Encrypted treats the CMK certificate purely as (PK, SK) pair. so you can use the expired cert without running ...
5 votes
Accepted

Type clash on update of always encrypted varchar in ssms 2017

My guess is that you are not checking the following from this guide, which states that you can't use DECLARE and SET separately: Declared and initialized in the same statement (inline ...
5 votes
Accepted

Can SQL Server Always Encrypted be used for temporary tables?

It appears that you have to create a CMK and CEK in tempdb. This command Works On My Machine®, and is largely copy/pasted from BOL articles. USE tempdb CREATE COLUMN MASTER KEY cmk_whatever ...
  • 34.4k
4 votes
Accepted

Clarification on SQL Server 2016 Always Encrypted

AE is intended to protect sensitive data end-to-end. That's why keys are held by the client, not server. In SQL Server 2016 granularity is at the column level. If you want to AE the entire database, ...
  • 1,435
4 votes
Accepted

Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

Does your table have a primary key or a clustered index? There is a known issue in the wizard/PowerShell that may cause it to run out of memory, if the table does not have a primary or a clustered ...
4 votes

Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

For large tables I would definitely work with T-SQL or Powershell rather than the wizard - clunky UIs that run all kinds of background things and take over-protective locks do not have a great track ...
4 votes
Accepted

Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

The documentation states, in the opening paragraph: The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the ...
  • 67.6k
4 votes

Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

The whole entire data/log file doesn't get encrypted. Only the specific fields you encrypt are encrypted, and yes, those are encrypted in the data, log, and backup files. SQL Server never sees the ...
  • 41.5k
4 votes
Accepted

Microsoft Key Storage Provider for Always Encrypted Master Key

How can I remove the "Always-Encrypted-Auto1" entry? Since there isn't a way in the SSMS GUI and I doubt you want to write your own code to find out, look in %APPDATA%\Microsoft\Crypto\Keys and ...
4 votes

Always Encrypted column validation

You are fighting against the technology that you're using. The point of Always Encrypted is that data is only decrypted on the client. A few quotes from the documentation: To successfully update ...
  • 30.9k
3 votes
Accepted

Do signed certificates from public certificate authorities add any value for always encrypted?

Certs from trusted CAs (like VeriSign) are used when you need a certificate that must be able to prove its issuer, purpose, validity, etc... Certs for data encryption like Always Encrypted typically ...
  • 1,435
3 votes

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

Simply put, TDE is data encrypted at rest (On disk) and AE is data encrypted on the wire in addition.
  • 2,136
3 votes
Accepted

Restoring encrypted DB to SQL vNext

You might be running into a bug in how SQL Server vNext CTP1.2 handles filepaths. To CREATE CERTIFICATE from a backup, you currently need to pre-pend a 'c:' and use backslashes ('\') instead of ...
  • 146
3 votes
Accepted

How do we restore database configured with Always Encrypted between environments and enable web app to encrypt/decrypt data?

See this description of how Always Encrypted works. Basically, SQL Server does not have access to the keys required to decrypt data into plain text. This work is performed by the client driver and in ...
  • 9,512
2 votes
Accepted

Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

Bulk Insert operations in the manner you are describing are not supported for encrypted column via SSMS. Please refer to this (https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-...
2 votes

What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology?

The Always Encrypted feature provides end-to-end encryption, which encrypts data both in-flight, and at rest. Data is encrypted & decrypted on the client at the driver. Data is encrypted ...
  • 15k
2 votes

Can SQL Server Always Encrypted be used for temporary tables?

I am guessing that the CEK [ColEnc1] resides in the same database that the table listofdates resides in. Try creating the CMK and CEK in tempdb table and the error should be gone.
2 votes
Accepted

Exporting certificate without private key

Then I export the certificate and import it into the machine where the SQL Server is running. That defeats the purpose of using Always Encrypted if the server that is running SQL Server also has the ...
2 votes
Accepted

Always Encrypted: How to insert or update encrypted columns?

You need to use variables for the values, so that SSMS can parameterize the query against the API that it is using. Something like: DECLARE @fname varchar(30) = 'Kula' DECLARE @ename varchar(30) =...
2 votes
Accepted

Does the Always Encrypted certificates get copied over in availability groups?

In AlwaysEncrypted, the keys are not stored on the server to begin with.
2 votes
Accepted

AlwaysEncrypted error when inserting via stored proc

So I figured out what I was doing wrong AlwaysEncrypted requires the variables to be defined in line: DECLARE @OPERATION_ID int = 4 DECLARE @PARAMETER_NAME varchar(100) = 'algorithm' DECLARE @...
2 votes
Accepted

Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?

SQL Server 2017 documentation: Always Encrypted is not supported for the columns with the below characteristics: (...) String (varchar, char, etc.) columns with non-bin2 collations (...) Source: ...
  • 1,523
2 votes
Accepted

Does AE certificate include both master and encryption keys together?

After setting up Always Encrypted, I can see a certificate in my Current User Correct, you've stated that the Column Master Key (CMK) will be created/stored there. Most likely this was done via the ...
2 votes

Always Encrypted Data Validation

Deterministic encryption will produce the same value as long as the inputs are the same. In database terms it means that: Queries can perform equality comparison on columns encrypted using ...
  • 556
2 votes
Accepted

Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?

In this join, the HR.[Employee_ID] is not encrypted, and part of the [vw_Employees] view, and the ET.[EmployeeID] is the encrypted column. Why does this equality join not work? The documentation ...
2 votes

Backup and restore SQL Server database with encrypted columns: what should I backup along with database?

You are using column encryption. This is not always-encrypted. When you backup your data base and restore it, you only need to recover the master key. Anything else will be restored through the backup ...
  • 121
1 vote

Replicate encrypted data from Sybase ASE 15.5 to Sybase ASE 15.5

Assumptions: you're referring to ASE's column-level encryption which requires a user/role to have decrypt permissions to see the actual data (as opposed to database-level encryption which merely ...
  • 2,536

Only top scored, non community-wiki answers of a minimum length are eligible