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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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 semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
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.
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.