July 21, 2010 at 6:00 am
Database mirroring failed between PRIMARY and MIRROR, due to the following error. (Let me apolozige for posting 2008 section, I am working with 2005)
‘Database mirroring login attempt failed with error: ‘Connection handshake failed. The certificate used by this end point was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints’
I recreated certificate on PRIMARY and then copied on to MIRROR and tried to recreate it.
Please let me know how I can export certificate with same encryption as it is in PRIMARY to MIRROR.
I followed these methods
ON PRIMARY
Stage 1
Step1)
CREATE CERTIFICATE [SQL2005-02-Certificate_New]
WITH SUBJECT = 'SQL2005-02 Server Certificate',
START_DATE = '07/18/2010',
EXPIRY_DATE = '12/31/2030';
Step2)
BACKUP CERTIFICATE [SQL2005-02-Certificate_New]
TO FILE = 'SQL2005-02-Certificate_New.CER'
Step3)
ALTER ENDPOINT [MirroringEndPoint] FOR DATABASE_MIRRORING (authentication = certificate [SQL2005-02-Certificate_New]);
This step was successful and End point on PRIMARY uses new certificate SQL2005-02-Certificate_New
When you query sys.certificates table on PRIMARY you see following
Name certificate_idprincipal_idpvt_key_encryption_typepvt_key_encryption_type_descissuer_name
SQL2005-02-Certificate 2691 MK ENCRYPTED_BY_MASTER_KEYSQL2005-02
SQL2005-01-CertificatePublic2707 NA NO_PRIVATE_KEY SQL2005-01
SQL2005-03-CertificatePublic2718 NA NO_PRIVATE_KEY SQL2005-03
SQL2005-02-Certificate_New2751 MK ENCRYPTED_BY_MASTER_KEY SQL2005-02
copied to MIRROR:
Step 1)
CREATE CERTIFICATE [SQL2005-02-Certificate_New]
AUTHORIZATION [SQL2005-02]
FROM FILE = 'c:\temp\SQL2005-02-Certificate_New.CER';
STEP2)
ALTER ENDPOINT [MirroringEndPoint] FOR DATABASE_MIRRORING (authentication = certificate [SQL2005-02-Certificate_New]);
I was getting following error
The certificate 'SQL2005-02-Certificate_New' is not valid for endpoint authentication. The certificate must have a private key encrypted with the database master key and current UTC date has to be between the certificate start date and the certificate expiration date.
When you query sys.certificates table on MIRROR server you see following
name certificate_idprincipal_idpvt_key_encryption_typepvt_key_encryption_type_descissuer_name
SQL2005-01-Certificate 2681 MK ENCRYPTED_BY_MASTER_KEYSQL2005-01
SQL2005-02-CertificatePublic2697 NA NO_PRIVATE_KEY SQL2005-02
SQL2005-03-CertificatePublic2708 NA NO_PRIVATE_KEY SQL2005-03
SQL2005-02-Certificate_New2747 NA NO_PRIVATE_KEY SQL2005-02
Then I followed following steps
Stage 2
Already did following step1 in Stage 1
Step1) CREATE CERTIFICATE [SQL2005-02-Certificate_New]
WITH SUBJECT = 'SQL2005-02 Server Certificate',
START_DATE = '07/18/2010',
EXPIRY_DATE = '12/31/2030';
I backed up service master service KEY
Step2) BACKUP SERVICE MASTER KEY TO FILE = 'c:\keys\SQL2005-02_service_master_key_new' ENCRYPTION BY PASSWORD = 'mypassword';
Tried to backup and recreate on MIRROR
Step3) BACKUP CERTIFICATE [SQL2005-02-Certificate_New]
TO FILE = 'c:\keys\SQL2005-02-Certificate_New.CER'
WITH PRIVATE KEY ( FILE ='c:\keys\SQL2005-02_service_master_key_new',ENCRYPTION BY PASSWORD ='mypassword')
I am experiencing following error
sg 15240, Level 16, State 1, Line 1
Cannot write into file 'c:\keys\ SQL2005-02_service_master_key_new'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Please let me know how I can export certificate with same encryption as it is in PRIMARY to MIRROR. Here what I want to achieve is I want to create same certificate on MIRROR with same encryption like in step 3 in stage 1
July 21, 2010 at 11:36 pm
Fix the principal
--On the principal
--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [principal_new_cert]
WITH SUBJECT = 'mirroring cert',
START_DATE='07/11/2010', --make sure this is a day prior to the current date
EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO
--backup the cert for the endpoint
BACKUP CERTIFICATE [principal_new_cert] TO FILE = 'c:\principal_new_cert.cer';
GO
--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [principal_new_cert])
GO
--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_principal_cert]
GO
Fix the mirror
--On the mirror
--drop the old cert for the principal login
DROP CERTIFICATE [old_principal_cert]
GO
--create the new cert using the backup you made on the principal server
CREATE CERTIFICATE [principal_new_cert] AUTHORIZATION PrincipalServerUser
FROM FILE = 'c:\principal_new_cert.cer'
GO
--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [mirror_new_cert]
WITH SUBJECT = 'mirroring cert',
START_DATE='07/11/2010', --make sure this is a day prior to the current date
EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO
--backup the new cert for the endpoint
BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'c:\mirror_new_cert.cer';
GO
--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [mirror_new_cert])
GO
--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_mirror_cert]
Finish the principal
--Go back to the principal
--drop the old cert for the mirror login
DROP CERTIFICATE [old_mirror_cert]
GO
--create the new cert using the backup you made on the mirror server
CREATE CERTIFICATE [mirror_new_cert] AUTHORIZATION MirrorServerUser
FROM FILE = 'c:\mirror_new_cert.cer'
GO
--finally resume the mirroring session for each database
ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME
July 23, 2010 at 1:24 pm
Thanks it worked. However I created certificate on Principal using certificate from Mirror.
July 1, 2014 at 5:28 am
Thanks worked for me as well. I also forgot to give dates for initial certificates and they expired.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply