August 11, 2017 at 11:47 am
I am getting a backup failure on a TDE database (SQL Server 2016 SP1 EE) in an AG
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint XXXXXXXXXXX
Msg 3013, Level 16, State 1 Line 1
Backup Database is terminating abnormally
The other databases in the AG that have TDE enabled backup fine. Anyone have an idea what might be going on?
Thanks
Gary
August 11, 2017 at 3:34 pm
Is the TDE certificate on that server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2017 at 4:14 pm
It is on the server the backup is running from there are 2 others useing the same certificate on system that are backing up correctly. If i drop the tde it backsup fine also. The cert is not on the system that the backup file is landing on. But that is the same for the ones that are working
August 15, 2017 at 9:44 am
As I understand the error, the certificate is either missing or different , can you match thumbprint from error with thumbprint in sys.cetfiicates
August 16, 2017 at 2:16 pm
The cert is not on the system that the backup file is landing on.
Do you mean the cert is not on the system you are trying to restore a backup to? The system giving you the error? This would be why then. The cert protecting the DEK for a database (and any backup of that database while it is encrypted with TDE) need to be present in order for the restore to occur. Otherwise SQL cannot access the DEK needed to decrypt the data and bring the database into the instance.
Joie Andrew
"Since 1982"
August 16, 2017 at 3:53 pm
No the error is during the backup process when it reaches 100%. The same cert on the same system is used for 2 other database on the instance and it works as expected. Once again I am not restoring only trying to backup
August 16, 2017 at 9:18 pm
Joie Andrew
"Since 1982"
August 18, 2017 at 2:40 am
This was removed by the editor as SPAM
August 18, 2017 at 2:58 am
So everyone is saying that I need to bacip the cert. restore it to same machine. Restore the DB on same machine again then re-encrypt (which it currently is) and that is the fix. All on the machine the DB is currently on
August 18, 2017 at 9:20 am
gary.mazzone - Friday, August 18, 2017 2:58 AMSo everyone is saying that I need to bacip the cert. restore it to same machine. Restore the DB on same machine again then re-encrypt (which it currently is) and that is the fix. All on the machine the DB is currently on
yes, backup the cert from the primary and restore to the secondary with the issue as prettsons has suggested.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 18, 2017 at 10:06 am
The issue is on the primary. The cert is ok all other servers in the AG. I encrypt the DB it encrypts on all servers just will no backup. This is not a restore
August 28, 2017 at 4:42 am
All the problem is not a restore issue it is a backup issue!
I am using SQL Server 2016 Enterprise SP1 CU4 is applied
I am issuing the following command:
BACKUP DATABASE [RoamBilling2] TO DISK = N'\\nycmcmdb01\DB-MSSQL-Quorum\BACKUP\Diff\RoamBilling2\RoamBilling2_backup_2017_08_28_051139_0664381.bak' WITH DIFFERENTIAL , MAXTRANSFERSIZE = 131072, NOFORMAT, NOINIT, NAME = N'RoamBilling2_backup_2017_08_28_051139_0664381', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
and I get the following result:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 252416 pages for database 'RoamBilling2', file 'RoamBilling2' on file 1.
100 percent processed.
Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0x92548AE23B5C95B7BACE8E76BF7775268707FCE8'.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
The thing is I am using the same cert for TDE on 5 other DBs and they are working fine.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply