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?

  • Just curious -- you have Ola's script taking a COPY_ONLY backup, but not the native backup? – Erik Darling Jan 26 '18 at 19:51
  • Hi Erik i just tried with @CopyOnly = 'N' and get same result; – Alex SQL Jan 26 '18 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 '18 at 20:32
  • Kin yes it's exactly the same i triple checked and recreated few times. – Alex SQL Jan 26 '18 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 '18 at 23:05
2

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

  • 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 '18 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 '18 at 17:24

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.