Always Encrypted in always on

Question

Always Encrypted in always on

Bouke Bruinsma

SSCrazy

Points: 2185
More actions
November 22, 2018 at 12:01 pm

#378654

Does someone know if AE is supported in an always on setup? Since AE is supported for Azure I would think it is since Azure SQL uses always on in the background. I can find enough resources for AO and TDE but none for AO and AE.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

robnsilver SSC-Addicted Points: 403 More actions · Answer 1

HI

Haven't tried it myself however I believe it will work. You will need to ensure the that any local/remote certificates referenced in any column encryption key are made available to all replicas nodes.

Cheers

Rob

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 2

Always Encrypted is supported with Always On Availability Groups. The only features that aren't supported are transactional or merge replication and distributed queries on linked servers. The actual decryption work is performed by the client, specifically the driver being used by the client, and the encrypted data is actually stored as binary data in the database which AG's are capable of handling. The metadata about the column encryption key and the column master key is stored in the database, however, SQL Server never decrypts the data, so the server instance itself has no requirement for access to the keys, it simply needs the metadata about them to provide the client driver when requested. This data is stored in the database, and so is always available on an AG database, regardless of the replica.

While the article doesn't explicitly state support for AlwaysOn AGs, this article has lots of useful info about AE and from the information provided you can infer support for Always On:- https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

Bouke Bruinsma SSCrazy Points: 2185 More actions · Answer 3

HandyD - Sunday, December 16, 2018 11:05 PM
Always Encrypted is supported with Always On Availability Groups. The only features that aren't supported are transactional or merge replication and distributed queries on linked servers. The actual decryption work is performed by the client, specifically the driver being used by the client, and the encrypted data is actually stored as binary data in the database which AG's are capable of handling. The metadata about the column encryption key and the column master key is stored in the database, however, SQL Server never decrypts the data, so the server instance itself has no requirement for access to the keys, it simply needs the metadata about them to provide the client driver when requested. This data is stored in the database, and so is always available on an AG database, regardless of the replica.
While the article doesn't explicitly state support for AlwaysOn AGs, this article has lots of useful info about AE and from the information provided you can infer support for Always On:- https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

Thank for the clear response. I also assumed it was supported since I cannot find anything that states it's not supported. And it makes sense that AE metadata and encrypted data is replicated like any other data in the database.