patternsqlMinor
Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016
Viewed 0 times
encryptionlogsqlserveralwaysencryptedfilesusinganddata
Problem
When we implement the Always Encrypted feature in SQL Server 2016, do the .mdf files, .ldf files and .bak files get encrypted as well?
I would like to know if the data/log/backup files gets encrypted or not.
I would like to know if the data/log/backup files gets encrypted or not.
Solution
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 unencrypted values.
In the docs for Always Encrypted, Microsoft explains:
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine ( SQL Database or SQL Server).
That isn't great wording, but what it means is that not only is the data encrypted, the SQL Server engine doesn't even have the key to decrypt it for you.
Thus the name - as far as the SQL Server engine is concerned, the data is Always Encrypted.
In the docs for Always Encrypted, Microsoft explains:
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine ( SQL Database or SQL Server).
That isn't great wording, but what it means is that not only is the data encrypted, the SQL Server engine doesn't even have the key to decrypt it for you.
Thus the name - as far as the SQL Server engine is concerned, the data is Always Encrypted.
Context
StackExchange Database Administrators Q#187688, answer score: 4
Revisions (0)
No revisions yet.