SELECT name, pvt_key_encryption_type, subjectĪs we can see, we’ve got a certificate named BackupCert that was encrypted using the DMK. Just to check that our certificate has successfully been created… WITH SUBJECT = 'Backup Encryption Certificate' Now that you’ve got a DMK, you can go ahead and create the certificate that we’re going to use to encrypt the backup. Obviously, the password can be any strong password that you choose. In the case of backups, the certificate used to encrypt them must firstly reside in the master database and secondly, be encrypted by the master database’s DMK.ĬREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D4t4b4s3' Certificates can be encrypted using either a password or a DMK. If we want to encrypt our backups, the first thing that we’re going to want to do is create a certificate to encrypt the backup with.īefore we can create a certificate, we’re going to need to create a Database Master Key (DMK) in the master database. So how do we go about encrypting our backups?

They could even just open the files with a hex editor read the data, it’s really not a difficult thing to do.īy default, SQL Server makes absolutely no effort to encrypt or in any way protect our backups. Wherever they are, what happens if someone manages to get hold of our backup files? They can easily just restore them onto a server where they have permissions and voila, all our super secret data is now theirs. We’re a pretty security minded lot aren’t we?īut what about our backups? Most of the time they’re just sitting on, either a local disk on the server itself or some share somewhere. We’re careful that all our passwords conform to crazy complexity rules. We all go to great lengths to make sure that our databases are secure (or at least I really hope that we do), we make sure that only authorised users have access and then only to the databases and levels that they need.

Sql backup master key