Troubleshooting Database Mirroring Error 1418 Updated
Last night I was helping someone with a database mirroring setup problem via email. It has been a long time since I came across a new cause for the infamous 1418 error for database mirroring. This time the error was accompanied by an error in the SQL Log on the mirror partner that said teh following:
Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not yet valid. State 104.
How could a certificate that had just been created be invalid already? My first thought was that the certificate had been created from an old script with a hard coded EXPIRY_DATE in the past. I quickly dismissed that idea because I knew the error would be different if the certificate had expired. It seemed that the certificate would have had to be created with a hard coded START_DATE in the future. I doubted that was happening though, so I turned to my good friend Bing to see if this was a known error. I didn’t get very many hits, but I struck gold with the first result I checked: http://social.msdn.microsoft.com/forums/…... In this thread, Remus Rusanu (blog|@rusanu) explains that the error is caused by time zone differences between where the certificate is created and where it is being used. In short, if the date on your server is earlier than the start date (if not specified, start date = create date) of the certificate, you get an error stating it is not yet valid.
The solution is to simply specify a hard coded START_DATE of one day prior.
The master post for troubleshooting database mirroring error 1418 to show this problem as well.