Backup Encryption in SQL Server 2014
This time we will talk about one more improvement that SQL Server 2014 provides in terms of creating backup copies, namely, the possibility of their full encryption. The ability to protect a backup with a password so that unauthorized people could not recover from it existed from time immemorial, and those who have been dealing with SQL Server for a long time must remember the WITH PASSWORD option for the BACKUP command. However, this method did not provide strong protection, and, as noted on mssqltips , it would provide a way to protect your data . In practice, TDE, which appeared in SQL Server 2008, was applied to protect backups. the database was transparently encrypted before making a backup of it. Therefore, starting with SQL Server 2012, the PASSWORD and MEDIAPASSWORD parameters are not used when creating backups. Restoring backups created using a password remains possible.
However, data encryption and backup encryption are two differently intended scenarios. It is obvious that when a backup copy is alienated, the rule of good tone is to protect it. For example, if we transfer the base to another data center in order to exclude a leak in the process of transmission via communication channels or in some other way. However, encryption incurs overhead, and if the database is securely stored in a local data center, why encrypt it only to make a backup? Fortunately, in SQL Server 2014 it became two independent processes. Similar to data encryption, a backup can be encrypted based on a certificate or an asymmetric key. AES 128, AES 192, AES 256 and Triple DES encryption algorithms are supported.
As an illustration, I will create an encrypted backup of my favorite AdventureWorks database on the local SQL Server 2014 CTP2 and recover from it in the cloud virtual machine.
To protect the backup, you need to create an encryptor: an asymmetric key or certificate - which one then transfer to the target SQL Server where the restoration will take place. To do this, the encryptor must be exported from the source instance of SQL Server and imported into the target one. There are no problems with certificates in this regard. With asymmetric keys more difficult. Given that the BACKUP ASYMMETRIC KEY command has not yet appeared, and you cannot create a duplicate asymmetric key as opposed to a symmetric key, the only sensible way is to create an asymmetric key outside SQL Server, for example, using the sn.exe utility, to drag it inside, like CREATE ASYMMETRIC KEY ... FROM FILE = '.... snk', encrypt the backup on the source instance, create an asymmetric key on the destination instance from the same snk-file, and restore the encrypted backup. In order not to hemorrhoids with asymmetric keys, in this example we will use a certificate, since ideologically it is the same public / private key pair.
Create a server certificate that will be used to encrypt the backup.
')
Since we did not specify any ENCRYPTION BY, this means that the certificate will be protected by the master key of the database, which is actually required. Only certificates signed by the master key are suitable for encrypting backups. If you protect the certificate with a password, for example (ENCRYPTION BY PASSWORD = 'Complicated password'),
when I try to encrypt the backup, the error Cannot use certificate 'TestCert' pops up, it is not protected by the database master key.
An encrypted backup, like a regular one, can be created traditionally on disk or in Azure Storage. In order not to bother with transferring the backup file, let's use the second method, which we analyzed in the <a href=real habrahabr.ru/company/microsoft/blog/201568 note> Creating backup copies of SQL Server 2014 CTP2 in Windows Azure.
Pic1
If you have already made a backup with the same name in the same container, you can get the following error (412) This is because when you create or restore a backup copy of Windows Azure gives SQL Server an infinite lease (lease) to block exclusive access to the blob. Upon successful completion of the backup or restore process, the rent is removed. But if it fails or the network crashes or something else goes wrong, the rent remains to hang, preventing the rewriting of the backup blob or its deletion. The PowerShell script to remove a blob with an active lease is provided here . I will do easier. Since there is nothing more in the backup container, I will delete and re-create the container. If the container is recreated with the same name, you need to keep in mind that Windows Azure will take a couple of minutes to figure out that the name has been released.
The same can be done in the SSMS GUI:
Pic2
Pic.3
Upon execution of the Script 2 command in the ordered container, a blob with a backup is formed:
Pic.4
Of course, encryption of backups is fully applicable when creating a backup copy of a database, not into the Cloud, but into a traditional local file. To do this, the first line of the backup database command (Script 2) should be changed to
This file should be seen from the instance of SQL Server where we plan to restore it. In our case, this is SQL Server on a virtual VM. You can copy the backup file to it, or upload it to Azure Storage, dividing the backup database ... to url into two steps: backup database ... to disk and then loading the backup into the blob container. Download can be done by hand from Visual Studio 2012 | 2013 (Server Explorer), on which you first need to install the Azure SDK .
Pic.5
However, it must be borne in mind that the restore database ... from url command (direct restoration of the database from Azure Storage) can only be done if this backup was also made in Storage. If a backup was made to disk and then transferred as a cloud blob, then on the destination server it will first need to be turned into a file and restored as a restore database ... from disk.
Now, regardless of the method of creating a backup, we go to the destination SQL Server, in our case installed on a virtual machine in Windows Azure. This can be done via remote desktop or connect to it from a local SSMS, as described here .
Pic.6
Where 5555 is a public TCP port at the end point of the cloud virtual machine, corresponding to 1433
Fig.7
pre-open in the Windows Firewall cloud VM
Fig.8
We repeat on it the creation of a credit for the Azurovsky settlement, similar to the first part of Script 2:
and trying to recover
Let me remind you that if this backup was made to disk and then loaded into Azure Storage, it must again be presented as a bak file and execute restore database AdventureWorks from disk.
In both cases, we get, naturally, an arc out, because the backup is encrypted
In addition to the backup to the destination server, you need to transfer a secret with which it was encrypted. For certificates, unlike asymmetric keys, backup / restore certificate commands are provided. As in the case of TDE , the backup certificate on the source instance must be exported along with the private key, otherwise an error will pop up:
We transfer the .cer and .pvk files to the destination server and create a certificate on it to restore from the backup. Since the virtual is fresh, you first need to create a master key DB master. The password with which it is protected has nothing to do with the master key passwords on the source server. In order not to be distracted by the story, which keys / certificates and how to transfer from SQL Server to SQL Server when transferring protected content, I recommend the article Migrating SQL Server Databases that use Database Master Keys .
Then we repeat the command to restore the database and see that now it is completed successfully:
Fig.9
However, data encryption and backup encryption are two differently intended scenarios. It is obvious that when a backup copy is alienated, the rule of good tone is to protect it. For example, if we transfer the base to another data center in order to exclude a leak in the process of transmission via communication channels or in some other way. However, encryption incurs overhead, and if the database is securely stored in a local data center, why encrypt it only to make a backup? Fortunately, in SQL Server 2014 it became two independent processes. Similar to data encryption, a backup can be encrypted based on a certificate or an asymmetric key. AES 128, AES 192, AES 256 and Triple DES encryption algorithms are supported.
As an illustration, I will create an encrypted backup of my favorite AdventureWorks database on the local SQL Server 2014 CTP2 and recover from it in the cloud virtual machine.
To protect the backup, you need to create an encryptor: an asymmetric key or certificate - which one then transfer to the target SQL Server where the restoration will take place. To do this, the encryptor must be exported from the source instance of SQL Server and imported into the target one. There are no problems with certificates in this regard. With asymmetric keys more difficult. Given that the BACKUP ASYMMETRIC KEY command has not yet appeared, and you cannot create a duplicate asymmetric key as opposed to a symmetric key, the only sensible way is to create an asymmetric key outside SQL Server, for example, using the sn.exe utility, to drag it inside, like CREATE ASYMMETRIC KEY ... FROM FILE = '.... snk', encrypt the backup on the source instance, create an asymmetric key on the destination instance from the same snk-file, and restore the encrypted backup. In order not to hemorrhoids with asymmetric keys, in this example we will use a certificate, since ideologically it is the same public / private key pair.
Create a server certificate that will be used to encrypt the backup.
use master if exists (select 1 from sys.certificates where name = '') drop certificate create certificate with subject = ' ' ')
Since we did not specify any ENCRYPTION BY, this means that the certificate will be protected by the master key of the database, which is actually required. Only certificates signed by the master key are suitable for encrypting backups. If you protect the certificate with a password, for example (ENCRYPTION BY PASSWORD = 'Complicated password'),
when I try to encrypt the backup, the error Cannot use certificate 'TestCert' pops up, it is not protected by the database master key.
An encrypted backup, like a regular one, can be created traditionally on disk or in Azure Storage. In order not to bother with transferring the backup file, let's use the second method, which we analyzed in the <a href=real habrahabr.ru/company/microsoft/blog/201568 note> Creating backup copies of SQL Server 2014 CTP2 in Windows Azure.
if exists (select 1 from sys.credentials where name = '') drop credential create credential with identity= 'bakstorage' , secret = '< , >' backup database AdventureWorks to url = 'http://bakstorage.blob.core.windows.net/container1/AdventureWorks2.bak' with credential = '' , format, compression, stats = 10 , encryption (algorithm = aes_256, server certificate = ) Script 2Pic1
If you have already made a backup with the same name in the same container, you can get the following error (412) This is because when you create or restore a backup copy of Windows Azure gives SQL Server an infinite lease (lease) to block exclusive access to the blob. Upon successful completion of the backup or restore process, the rent is removed. But if it fails or the network crashes or something else goes wrong, the rent remains to hang, preventing the rewriting of the backup blob or its deletion. The PowerShell script to remove a blob with an active lease is provided here . I will do easier. Since there is nothing more in the backup container, I will delete and re-create the container. If the container is recreated with the same name, you need to keep in mind that Windows Azure will take a couple of minutes to figure out that the name has been released.
The same can be done in the SSMS GUI:
Pic2
Pic.3
Upon execution of the Script 2 command in the ordered container, a blob with a backup is formed:
Pic.4
Of course, encryption of backups is fully applicable when creating a backup copy of a database, not into the Cloud, but into a traditional local file. To do this, the first line of the backup database command (Script 2) should be changed to
backup database AdventureWorks to disk = 'c:\Temp\AdventureWorks.bak' ... This file should be seen from the instance of SQL Server where we plan to restore it. In our case, this is SQL Server on a virtual VM. You can copy the backup file to it, or upload it to Azure Storage, dividing the backup database ... to url into two steps: backup database ... to disk and then loading the backup into the blob container. Download can be done by hand from Visual Studio 2012 | 2013 (Server Explorer), on which you first need to install the Azure SDK .
Pic.5
However, it must be borne in mind that the restore database ... from url command (direct restoration of the database from Azure Storage) can only be done if this backup was also made in Storage. If a backup was made to disk and then transferred as a cloud blob, then on the destination server it will first need to be turned into a file and restored as a restore database ... from disk.
Now, regardless of the method of creating a backup, we go to the destination SQL Server, in our case installed on a virtual machine in Windows Azure. This can be done via remote desktop or connect to it from a local SSMS, as described here .
Pic.6
Where 5555 is a public TCP port at the end point of the cloud virtual machine, corresponding to 1433
Fig.7
pre-open in the Windows Firewall cloud VM
Fig.8
We repeat on it the creation of a credit for the Azurovsky settlement, similar to the first part of Script 2:
if exists (select 1 from sys.credentials where name = N'') drop credential create credential with identity= 'bakstorage' ... and trying to recover
restore database AdventureWorks from url = 'http://bakstorage.blob.core.windows.net/container1/AdventureWorks.bak' with replace, stats = 10, credential = 'AzureStorageCredential' Script 4Let me remind you that if this backup was made to disk and then loaded into Azure Storage, it must again be presented as a bak file and execute restore database AdventureWorks from disk.
In both cases, we get, naturally, an arc out, because the backup is encrypted
Msg 33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint '0...'.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
In addition to the backup to the destination server, you need to transfer a secret with which it was encrypted. For certificates, unlike asymmetric keys, backup / restore certificate commands are provided. As in the case of TDE , the backup certificate on the source instance must be exported along with the private key, otherwise an error will pop up:
Msg 15507, Level 16, State 30, Line 5
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
backup certificate to file = 'c:\Temp\BackupCert.cer' with private key (file = 'c:\Temp\BackupCert.pvk', encryption by password = 'Abra@Cadabra') Script 4We transfer the .cer and .pvk files to the destination server and create a certificate on it to restore from the backup. Since the virtual is fresh, you first need to create a master key DB master. The password with which it is protected has nothing to do with the master key passwords on the source server. In order not to be distracted by the story, which keys / certificates and how to transfer from SQL Server to SQL Server when transferring protected content, I recommend the article Migrating SQL Server Databases that use Database Master Keys .
use master create master key encryption by password = 'Passw0rd1' if exists (select 1 from sys.certificates where name = N'') drop certificate create certificate from file = 'c:\Temp\BackupCert.cer' with private key (file = 'c:\Temp\BackupCert.pvk', decryption by password = 'Abra@Cadabra') Script 5Then we repeat the command to restore the database and see that now it is completed successfully:
restore database AdventureWorks from disk = 'd:\Temp\AdventureWorks.bak' with move 'AdventureWorks2012_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdvnetureWorks_Data.mdf', move 'AdventureWorks2012_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdvnetureWorks_Log.ldf', replace, stats = 10 Script 6Fig.9
Source: https://habr.com/ru/post/210982/
All Articles