2

Installed OH's scripts and they run great. sqlserver 2017 standard edition in a testing/dev environment. testing before rolling out to production.

I did a test of a restoration of an encrypted, or so I thought, database (testing) on a different server (dev) to test for a DR situation (on a server with no certs). The database restored and recovered without any key or certificate or anything. I wanted to ensure it would not recover without the certificate. I wanted to prove a failure as there was no key.

When I manually ran the backup Ola script, the output says encryption=N even though I explicitly said so in the script.

The certificate on test/source has not expired. The certificate is not on the dev (destination) server (it has no certs)

When I do an encrypted backup using the MS commands, it does seem to work. It just does not seem to work with the OH solution though.

I am sure it is user error. This solution has been around for a while. Any thoughts on why the script is not encrypting the backup? I hope I have included enough info below from our fake/test database. Let me know if there is anything else I can include

my backup script:

EXECUTE dbo.DatabaseBackup 
@Databases = 'USER_DATABASES',
@Directory = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\DPH-SQL-SLI-12T\encrypted',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'BackupCertificate',
@LogToTable = 'Y',
@CleanupTime = 192

One database output from a query window (for 1 of the many dbs) when I run the above:

Date and time: 2020-05-04 11:33:05
Database: [AntiBiog]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Encrypted: No
Is accessible: Yes
Differential base LSN: 898000001143500038
Last log backup LSN: 898000001170800001
Allocated extent page count: 4472 (34.937500 MB)
Modified extent page count: 48 (0.375000 MB)

Output from the last scheduled full backup :

Job 'DatabaseBackup - USER_DATABASES - FULL' : Step 1, 'DatabaseBackup - USER_DATABASES - FULL' : Began Executing 2020-05-04 00:01:00

Date and time: 2020-05-04 00:01:01 [SQLSTATE 01000]
Server: DPH-SQL-SLI-12T [SQLSTATE 01000]
Version: 14.0.1000.169 [SQLSTATE 01000]
Edition: Standard Edition (64-bit) [SQLSTATE 01000]
Platform: Windows [SQLSTATE 01000]
Procedure: [master].[dbo].[DatabaseBackup] [SQLSTATE 01000]
Parameters: @Databases = 'USER_DATABASES', @Directory = '\\DPH-DD-SLI-001\SLI_SQL_Backups\DPH-SQL-SLI-12T\encrypted', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = 192, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'Y', @EncryptionAlgorithm = 'AES_256', @ServerCertificate = 'BackupCertificate', @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @FileName = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @StringDelimiter = ',', @DatabaseOrder =...
Version: 2020-01-26 14:06:53 [SQLSTATE 01000]
Source: https://ola.hallengren.com [SQLSTATE 01000]
     [SQLSTATE 01000]
Date and time: 2020-05-04 00:01:01 [SQLSTATE 01000]
Database: [AntiBiog] [SQLSTATE 01000]
State: ONLINE [SQLSTATE 01000]
Standby: No [SQLSTATE 01000]
Updateability: READ_WRITE [SQLSTATE 01000]
User access: MULTI_USER [SQLSTATE 01000]
Recovery model: FULL [SQLSTATE 01000]
Encrypted: No [SQLSTATE 01000]
Is accessible: Yes [SQLSTATE 01000]
Differential base LSN: 898000001090100038 [SQLSTATE 01000]
Last log backup LSN: 898000001142800001 [SQLSTATE 01000]
Allocated extent page count: 4472 (34.937500 MB) [SQLSTATE 01000]
Modified extent page count: 48 (0.375000 MB) [SQLSTATE 01000]
     [SQLSTATE 01000]

Certificate (on test/source) SELECT * FROM sys.certificates where name like 'Back%'

name    certificate_id  principal_id    pvt_key_encryption_type pvt_key_encryption_type_desc
BackupCertificate   260 1   MK  ENCRYPTED_BY_MASTER_KEY

keys on test (source): SELECT * FROM master.sys.symmetric_keys;

name    principal_id    symmetric_key_id    key_length  key_algorithm   algorithm_desc
##MS_DatabaseMasterKey##    1   101 256 A3  AES_256
##MS_ServiceMasterKey## 1   102 256 A3  AES_256

I looked in the log table and found this entry (for the lastest full backup).It looks like it is taking my parameters, but for some reason not encrypting. Odd

BACKUP DATABASE [AntiBiog] 
TO DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\DPH-SQL-SLI-12T\encrypted\DPH-SQL-SLI-12T\AntiBiog\FULL\DPH-SQL-SLI-12T_AntiBiog_FULL_20200506_000101.bak' 
WITH NO_CHECKSUM, COMPRESSION, 
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCertificate])
3
  • Any thoughts? I did find this in the log table: Looks like it sees my entry, but doesn't seem to encrypt the backup Command BACKUP DATABASE [AntiBiog] TO DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\DPH-SQL-SLI-12T\encrypted\DPH-SQL-SLI-12T\AntiBiog\FULL\DPH-SQL-SLI-12T_AntiBiog_FULL_20200506_000101.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCertificate]) May 6 '20 at 13:54
  • 1
    Please put any additional information into the question itself rather than as a comment! p.s. welcome to the forum!
    – Vérace
    May 10 '20 at 3:22
  • I’m voting to close this question because according to the OP's answer, the error doesn't exists, it was a mistake.
    – McNets
    Jun 9 '20 at 17:23
0

AH User error.

Apparently the certificate was on the server!

Dropping the cert caused the backup to fail!

My bad!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.