1

I have 2 databases in AlwaysOn with TDE enabled. My TDE cert expired and i created a new one but Ola's Backup script is still failing:

EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'DB1, DB2', 
@Directory = 'D:\SQL-Backup', 
@CopyOnly='Y', 
@CleanupTime = 120, 
@Compress='Y', 
@BackupType = 'FULL', 
@Verify = 'N', 
@CheckSum = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'DB_DEK', 
@LogToTable = 'Y'

With this error (which is an old Cert):

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0xFAF8C09F62ED7D523AE634133331690FC2FD8367'.

Using native sql backup works:

BACKUP Database [DB1] 
TO  DISK = N'D:\SQL-Backup\DB1.bak' 
WITH FORMAT, INIT,  NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DB_DEK]),  STATS = 10
GO

For this to work I had to take the DBs from AlwaysOn.

Has anyone else experienced this type of scenario? And, if so, how did you resolve it?

Improve this question
5
  • Just curious -- you have Ola's script taking a COPY_ONLY backup, but not the native backup?
    – Erik Darling
    Jan 26, 2018 at 19:51
  • Hi Erik i just tried with @CopyOnly = 'N' and get same result;
    – Alex SQL
    Jan 26, 2018 at 19:58
  • is the cert created with same name like u have in Ola's script DB_DEK with same algorithm ?
    – Kin Shah
    Jan 26, 2018 at 20:32
  • Kin yes it's exactly the same i triple checked and recreated few times.
    – Alex SQL
    Jan 26, 2018 at 21:48
  • Very interesting thing happens when I recreate the master key and all the Certs and encrypt the db. ola’s script is somehow looking for the previous cert. I tried it few times and same result even after taking full and tlog backups
    – Alex SQL
    Jan 27, 2018 at 23:05

1 Answer 1

Reset to default
2

So after upgrading to SP1 CU7 the backups started working again with TDE.

Improve this answer
2
  • Silly question, perhaps, but had you restarted your server between the start of the problem and the update? I just had a cert issue, and I know that if you change the cert, the change doesn't take effect until SQL is restarted....
    – RDFozz
    Feb 28, 2018 at 17:01
  • yes i did restart. Also not sure if this fix. [link]support.microsoft.com/en-us/help/4058175/… fixed it
    – Alex SQL
    Feb 28, 2018 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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