Event ID: 28047

Question

Hello All,

I have this in my event log:

Event ID: 28047
Database Mirroring login attempt failed with error: 'Connection handshake failed.  Ther certificate used by this endpoint was not found:  Certificate expried.  Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoint: State 85.

I did not know that these certificates could expire, if that is the case.

My Principal server has a status of (Principal, Disconnected) for all the databases
My Mirrored server has a status of (Mirror, Disconnected/In Recovery) for all the databases.
I use no witness server.  I handle failover using high performance.

Any help would be appreciated.

Answer 1

Same issue already discussed in the thread on overcoming the certificate expired and creating a new one.
http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/6175b219-68ee-4486-bc5c-4939541f4bcb
After doing steps explained here , you may have to restart your mirroring.

Also this link,
http://www.sql-server-performance.com/faq/database_mirror_certificate_expired_p1.aspx

---

Thanks, Leks

Answer 2

i have on additional piece of info.  i created a test database and attempted to mirror it.  i was unsuccessful i got the following error.

the server network address "TCP://erdsreg4clu01.erds.xxx.gov:5024" can not be reached or does not exist.  check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

in my opinion there is nothing wrong with my mirror server.  physically it is located somewhere else but i can rdp to it without any issues at all.

any help appreciated.

Answer 3

Lekss,

Thanks for the info.  The document I used to create the certificates and the endpoints and how to establish the mirroring gave no indication that the certificates when created could expire.  I find that incredible.  Do you know if there is anyway to confirm or verify that the certificates indeed expired?

Roger

Answer 4

Lekss,

Thanks for the info.  The document I used to create the certificates and the endpoints and how to establish the mirroring gave no indication that the certificates when created could expire.  I find that incredible.  Do you know if there is anyway to confirm or verify that the certificates indeed expired?

Roger

The certificates has to be created with a expiry date , if that parameter was not mentioned - then the default expiry data is 1 year from the date of creation.

---

Thanks, Leks

Answer 5

i have on additional piece of info.  i created a test database and attempted to mirror it.  i was unsuccessful i got the following error.

the server network address "TCP://erdsreg4clu01.erds.xxx.gov:5024" can not be reached or does not exist.  check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

in my opinion there is nothing wrong with my mirror server.  physically it is located somewhere else but i can rdp to it without any issues at all.

any help appreciated.

The error 1418 is a generic error that occurs when a db mirroring session couldn't be craeted.There are numerous good blog articles on this error,
http://blogs.msdn.com/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx
http://blogs.technet.com/josebda/archive/2007/06/11/misleading-error-1418-message-in-sql-server-database-mirroring.aspx
http://blog.sqlauthority.com/2007/04/22/sql-server-fix-error-1418-microsoft-sql-server-the-server-network-address-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-reissue-the-command/
http://geekswithblogs.net/SabotsShell/archive/2008/05/17/sql-server-2005-database-mirroring-setup-1418-error.aspx

---

Thanks, Leks

Answer 6

Leks,

Thanks again for you help.  The steps you provided are helpful but it my attempts to correct this I am first doing this in my maintenance environment.  I feel as if more steps are needed.  Such as I need to perform ALTER DATABASE <database_name> SET PARTNER OFF on the mirrored side. Also, as I've created a NEW certificate, don't I also need to associate the certificate with the user account that I created  for login?

Answer 7

Hi ,

Follow this MSDN example http://msdn.microsoft.com/en-us/library/ms188712.aspx for configuring DB mirror with certificates , all steps are explained.

You can run ALETR DATABSE dbname SET PARTNER OFF on either of the server (principal / mirror server) , which will remove mirror configurations on both the endpoints(server).

---

Thanks, Leks

Answer 8

I'm greatful for the help you have provided me.  Do you know if a more detailed procedure exists anywhere for altering the endpoints with a new certificate?

Answer 9

You're Welcome .

Hi ,

Apparently REMUS has an excellent blog entry on this ,
Have a look and let me know if this helpful or still need any more clarifications,
http://rusanu.com/2008/10/25/replacing-endpoint-certificates-that-are-near-expiration/

---

Thanks, Leks