Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Certificate Expiration in Database Mirroring


Certificate Expiration in Database Mirroring

Author
Message
PREDDY 50537
PREDDY 50537
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
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_id principal_id pvt_key_encryption_type pvt_key_encryption_type_desc issuer_name
SQL2005-02-Certificate 269 1 MK ENCRYPTED_BY_MASTER_KEY SQL2005-02
SQL2005-01-CertificatePublic 270 7 NA NO_PRIVATE_KEY SQL2005-01
SQL2005-03-CertificatePublic 271 8 NA NO_PRIVATE_KEY SQL2005-03
SQL2005-02-Certificate_New 275 1 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_id principal_id pvt_key_encryption_type pvt_key_encryption_type_desc issuer_name
SQL2005-01-Certificate 268 1 MK ENCRYPTED_BY_MASTER_KEY SQL2005-01
SQL2005-02-CertificatePublic 269 7 NA NO_PRIVATE_KEY SQL2005-02
SQL2005-03-CertificatePublic 270 8 NA NO_PRIVATE_KEY SQL2005-03
SQL2005-02-Certificate_New 274 7 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

[color=#FF0000]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.[/color]


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
jncarter
jncarter
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 443
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
PREDDY 50537
PREDDY 50537
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
Thanks it worked. However I created certificate on Principal using certificate from Mirror.
Anoop Agarwal
Anoop Agarwal
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 117
Thanks worked for me as well. I also forgot to give dates for initial certificates and they expired.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search