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

  • Question

  • 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: 

    User_db has certificate:

    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?


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


  • 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 

    Ice fan

    Ice Fan
    TechNet Community Support

    Monday, January 11, 2016 6:11 AM