CREATE certificate MyBackupCertįrom file = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyBackupCert.cer' Here is the command I use, noting that I must include the password I entered when creating the certificate using makecert. To do this, we use the CREATE CERTIFICATE command, giving paths to the files. CREATE MASTER KEY ENCRYPTION BY PASSWORD = this is complete, we can load the certificate. We create the master key with a password, which you need to be sure is saved and protected. This is similar to the process used when enabling TDE. Before I do that, I need a master key in the master database. Once the files were there, I can connect to my SQL Server 2017 instance and load the certificate. I'll put these in the /data folder for me instance. While I use SSMS to run the code, the database server has to read in the files itself. The next step for me was to copy both these files to a place my SQL Server can access. This creates a certificate, as you can see here:Īnd the files exist in the (older) VM I used to create a certificate. The command I used is: makecert -sv "c:\EncryptionPrimer\MyBackupCert.pvk" -pe -a sha1 -b "" -e "" -len 2048 -r -n CN="SQL Backup Certificate" c:\EncryptionPrimer\MyBackupCert.cer This is an older utility, but it makes generating the two certificate files very easy. To get my external certificate, I will use the makecert.exe utility from the Windows SDK. Not all certificates meet these requirements, so ensure whatever certificate you wish to use does so.
There are numerous third parties that you can contact to create certificates, as well as lots of software that will achieve this task. I will not cover the myriad of ways that you can get a certificate for use with SQL Server. Please be sure you protect your certificates and passwords carefully or your backup encryption may not be as valuable as you expect. It is easy to make a mistake in securing and rotating your certificates.
Note: Certificate and key management is a complex task. We will use a certificate supplied by a third party as opposed to a self generated certificate inside SQL Server. In this article, we will look at the native backup encryption available in SQL Server 2014 and above. SQL Server provides a few ways to encrypt backups, and there are numerous third party tools, such as SQL Backup, that perform encryption as well. As security becomes more and more important to data professionals, one of the more basic things a DBA or sysadmin must do is protect the backup files for their databases.
0 kommentar(er)
