July 20, 2010 at 1:28 pm
Database mirroring failed between PRIMARY and MIRROR, due to the following error.
‘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 9:47 am
any one?
July 21, 2010 at 10:44 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic956270-1549-1.aspx
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
July 21, 2010 at 10:46 am
Looks to me that answers should actually go here, not in the other thread since the OP is using SQL Server 2005.
July 21, 2010 at 10:55 am
Lynn Pettis (7/21/2010)
Looks to me that answers should actually go here, not in the other thread since the OP is using SQL Server 2005.
This was the third one I saw. If you want to sort the links out, I'll edit mine out.
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
July 22, 2010 at 6:40 am
I apologize for that. Please can you delete this post.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply