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.
always-encryptedsql serversql-server-2016
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.
Best Answer
The documentation states, in the opening paragraph:
It is implied that only encrypted columns are encrypted. Therefore, the database, logs, and backups are not entirely encrypted.
When you setup Always Encrypted, you specify which columns will be encrypted; only the data for those columns is encrypted. The data is encrypted prior to being sent to SQL Server, so the data is encrypted inside the data file, the log file, and inside any backups taken of the database. Data in the columns that have not been encrypted by Always Encrypted is visible in plain text inside the .mdf, .ldf, and (perhaps compressed) inside any backup files of the database.
Transparent Database Encryption will encrypt the entire database; the .mdf, the .ldf, and any backups taken of the database will be entirely encrypted. The SQL Server instance has a copy of the decryption key used to start the database when the instance is started, or when the database is restored or attached.