I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.

I cannot find any resources explaining why this happens, and I want to understand what the purpose of the collation change is.


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: Always Encrypted

Looks like it is changing in SQL Server 2019:

Since its initial release, Always Encrypted has had a restriction regarding the use of collations: non-BIN2 collations are not allowed for character string columns encrypted using deterministic encryption. This restriction also applies to enclave-enabled string columns.

The use of non-BIN2 collations is permitted for character string columns encrypted with randomized encryption and enclave-enabled column encryption keys. However, the only new functionality that is enabled for such columns is in-place encryption. To enable rich computations (pattern matching, comparison operations), you must ensure the column uses a BIN2 collation.

Configure Always Encrypted with secure enclaves

  • This is happening with a randomized encryption type. Seems like BIN2 is the right way to go no matter what though.
    – Elias
    Jun 25 '19 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.