Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Certificate Expiration in Database Mirroring Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 2:49 PM
Points: 6, 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
Post #956270
Posted Wednesday, July 21, 2010 11:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, February 12, 2011 6:05 AM
Points: 601, 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
Post #956934
Posted Friday, July 23, 2010 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 2:49 PM
Points: 6, Visits: 15
Thanks it worked. However I created certificate on Principal using certificate from Mirror.
Post #958220
Posted Tuesday, July 1, 2014 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:19 AM
Points: 9, Visits: 58
Thanks worked for me as well. I also forgot to give dates for initial certificates and they expired.
Post #1587948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse