Post reply

is_master_key_encrypted_by_server

  • May 14, 2018 at 12:03 pm

    #334698

    Hi there,

    I've just encrypted a second DB with the same certificate as used by the first.

    USE DatabaseNumber2
    GO
    CREATE DATABASE ENCRYPTION KEY
     WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE SQL_Bandit_Cert1

    I've noticed that the is_master_key_encrypted_by_server flag for my second db is still set to 0 ? Is this expected ? What have I missed?

    Many thanks

  • May 14, 2018 at 12:45 pm

    #1990399

    Assuming you are referring to 'Is_master_key_Encrypted_by_server" column in sys.databases, you missed nothing. That column will not be set in sys.databases for all TDE enabled databases. The DMK which is used by TDE mechanism to encrypt user databases is created in Master database. That's the only DB(Master) for which this column shows as 1.

    Refer this: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-2017
    The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.

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

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