Database Mirroring will be suspended and will get error Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

Issue:-

Database Mirroring will be suspended and will get error Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

Description:-
This issue appears when after configuring Mirror SQL database has been encrypted with TDE certificates on the Principal Server and user do not restore certificate to Mirror Server. 

Error of Mirror Server:-

-------------------------------

2015-08-25 10:05:37.220    spid32s       Error: 33111, Severity: 16, State: 3.

2015-08-25 10:05:37.220    spid32s       Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

2015-08-25 10:05:37.220    spid32s       Error: 1454, Severity: 16, State: 1.

2015-08-25 10:05:37.220    spid32s       Database mirroring will be suspended. Server instance 'ServerName1' encountered error 33111, state 3, severity 16 when it was acting as a mirroring partner for database 'databaseName'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

2015-08-25 10:08:29.190    spid32s       Error: 33111, Severity: 16, State: 3.

2015-08-25 10:08:29.190    spid32s       Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

2015-08-25 10:08:29.190    spid32s       Error: 1454, Severity: 16, State: 1.

2015-08-25 10:08:29.190    spid32s       Database mirroring will be suspended. Server instance 'ServerName1' encountered error 33111, state 3, severity 16 when it was acting as a mirroring partner for database 'databaseName'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Error in Principal Server:-

-------------------------------

2015-08-25 09:09:52.100 spid23s      Error: 1453, Severity: 16, State: 1.

2015-08-25 09:09:52.100 spid23s      'TCP://ServerName1.na.msmps.net:5022', the remote mirroring partner for database 'DatabaseName', encountered error 33111, status 3, severity 16. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remov

2015-08-25 09:21:17.040 spid23s      Error: 1453, Severi2015-08-25 09:21:17.040 spid23s      'TCP://ServerName1.na.msmps.net:5022', the remote mirroring partner for database 'prod_docgen', encountered error 33111, status 3, severity 16. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remov

2015-08-25 09:22:01.310 spid23s      Error: 1453, Severity: 16, State: 1.

2015-08-25 09:22:01.310 spid23s      'TCP://Servername1.na.msmps.net:5022', the remote mirroring partner for database 'DatabaseName', encountered error 33111,ty: 16, State: 1.

 status 3, severity 16. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remov

Problem:-

The issue arises when someone create a the encryption master key and certificate on the Principal server and set the encryption on for a the required database.

Then user sometime forget  backed the certificate from principal Server  and  does not restore the same key to the Mirror server by mistake.

In Principal Server, you executed below command.

USE Master ;

go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

go

CREATE CERTIFICATE TestSQLServerCert

WITH SUBJECT = 'Certificate to protect TDE key',
EXPIRY_DATE = '2015-04-23';

go

-- Create a database to be protected by TDE.

 BACKUP CERTIFICATE TestSQLServerCert

TO FILE = 'E:\SQLCert\TestSQLServerCert'

WITH PRIVATE KEY 

(

    FILE = 'E:\SQLCert\SQLPrivateKeyFile',

    ENCRYPTION BY PASSWORD = 'rt@40(FL&dasl1'

);

go

USE <DatabaseName>;
GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

ALTER DATABASE <DatabaseName> SET ENCRYPTION ON;

GO

After executing above command, on Principal, Mirroring will get suspended and when you try to resume mirror, will get error in error log of Mirror Server like :-

2015-08-25 10:08:29.190    spid32s       Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

Resolution: - 

In order to solve this issue, we need to restore the encryption certificate on the Mirror Server.

- Move or copy the backup of the server certificate and the private key file from the Principal Server to the same location on the Mirror  server. 

-- Do not use file extensions

-- Create a database master key on the destination instance of SQL Server. 

Command 1:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO

-- Recreate the server certificate by using the original server certificate backup file. 
-- The password must be the same as the password that was used when the backup was created.

Command 2:
CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'E:\SQLCert\TestSQLServerCert'
WITH PRIVATE KEY 
(
    FILE = 'E:\SQLCert\SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);

GO

After executing Command  1 and Command 2,  you will see database mirroring will resume. 

Happy Troubleshooting.