Certificate Expiration in Database Mirroring

  • PREDDY 50537

    Old Hand

    Points: 350

    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

    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

  • jncarter

    Ten Centuries

    Points: 1195

    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

    Old Hand

    Points: 350

    Thanks it worked. However I created certificate on Principal using certificate from Mirror.

  • Anoop Agarwal

    SSC Enthusiast

    Points: 123

    Thanks worked for me as well. I also forgot to give dates for initial certificates and they expired.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply