In this article, I will discuss backup encryption application internals, how this feature is applied with the latest versions of SQL, the importance of security, how to recover/restore the certificate and the database in case of system/database failure, asymmetric key and EKM provider, and give step-by-step examples of the process of demonstrating the encryption and the recovery of a backup. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability-now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms! They need to be implemented not just in the production environment, but within our backup solution as well.Įarlier versions of SQL Server had a limitation on this security feature we had to use third-party solutions to encrypt and compress the backup files. Security best-practices are in place in order to secure the metaphorical soul. What good, then, is it to lock the doors of your own equipment, when the soul has already departed? If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware.

Data security has to be thought of from a different perspective now. The data literally resides on someone else’s infrastructure. While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

Terabytes of data, millions of rows the entire business depends on this - a database administrator’s responsibility is as massive as it sounds.

Backup master key sql server