If you’re like me, you take advantage of the default backup paths in SQL Server. It makes my code cleaner, and if I need to move the instance somewhere else, all my code works. No pathing issues.
A certificate backup might look like this for me:
USE master ; go BACKUP CERTIFICATE SteveCert TO FILE = 'SteveCert' WITH PRIVATE KEY ( FILE = 'SteveCertPrivateKeyFile', ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd' ); GO
If I run this, and immediately go to my backup folder, sorting by the last modified date for files, I see this:
No certificate backup file. What happened?
The answer is actually documented, and you should be aware of this. In the BACKUP CERTIFICATE page, it says this: “The default is the path of the SQL Server DATA folder. “
That’s interesting, and it makes sense to me. This folder is more likely to be secured than the backup folder, where developers and who knows who else may have access to the folder. By limiting it in the data folder, you provide a little obfuscation, perhaps more protection, and you force the administrator, the DBA, to get the files.
However the files are also ACL protected. If I go to my data folder, I see the files.
If I select the certificate and CTRL+C (copy) it, and then go to the backup folder and try a CTRL+V (paste), I get this:
The service account has permissions to this file, not administrators by default. This action invokes the UAC command to require me to make a conscious decision to make this copy.
Of course, I can just provide a path to make sure I can find the file.
BACKUP CERTIFICATE SteveCert TO FILE = 'c:\SQLBackup\SteveCert' WITH PRIVATE KEY ( FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile', ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd' ); go
Whatever you do, make sure you backup your certificate files and keep them safe. If they go, you do lose data.
Filed under: Blog Tagged: backup, encryption, security, sql server, syndicated