locked
keys, certificate and "is_master_key_encrypted_by_server" on user database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    We had SQL 2008 R2 server SRV1 with databases: distribution, user_db and etc. Server run as replication distributor.

    Now we moved to SQL 2014 server SRV1 that holds only same user_db database. Replication distributor now runs on SRV2.

    I noticed that user_db on new SRV1 has "is_master_key_encrypted_by_server" set to 1 (SELECT name, is_master_key_encrypted_by_server FROM sys.databases WHERE name = 'user_db' )

    User_db has keys: 
    ##MS_DatabaseMasterKey##
    SQLSERVER_REPLICATION

    User_db has certificate:
    SQLSERVER_REPLICATION - ENCRYPTED_BY_MASTER_KEY.

    I guess all this is legacy configuration from old server SRV1. Now I would like to remove all these keys and certificates from user_db and set "is_master_key_encrypted_by_server" to 0.

    Is any service that could use this configuration if we are not using db encryption, we only use service broker and couple transactional replication subscriptions on user_db?

    Thanks

    • Edited by jori5 Thursday, January 7, 2016 5:06 PM
    Thursday, January 7, 2016 5:05 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi jori5,

    It is possible that you can still use any replication configuration after removing these user_db encryption keys.

    Besides, before you remove these encryption keys, please use the following statement to check whether anything is encrypted by the database master key and the certificate.

    Use userdatabase 
SELECT * FROM sys.key_encryptions

    And you can remove the database master key and the certificate using these statements.
    Use user database

    DROP SYMMETRIC KEY sysmmetrickryname 

DROP CERTIFICATE certificatename 

DROP MASTER KEY


    Regards,
    Ice fan

    Ice Fan
    TechNet Community Support


    Monday, January 11, 2016 6:11 AM