locked
what is the use of master key (which is created under master DB) in sql server? RRS feed

  • Question

  • what is the use of master key (which is created under master DB) in sql server? 

    The one which is created as

    create master key 
    encryption by password = 'somepassword';


    Regards, Ashif Shaikh


    • Edited by Asif_DBA Wednesday, April 3, 2019 12:30 PM
    Wednesday, April 3, 2019 10:18 AM

All replies

  • Are you talking about the service master key (SMK) created when a database is encrypted?

    To understand what SMK is for, I suggest you start by reading the following document:
    Service Master Key - SQL Server

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, April 3, 2019 12:19 PM
  • No, I am talking about the following.

    create master key 
    encryption by password = 'somepassword';


    Regards, Ashif Shaikh

    Wednesday, April 3, 2019 12:29 PM
  • From msdn:

    Remarks

    The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

    https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-2017

    Wednesday, April 3, 2019 12:33 PM
  • Take a look at the Remarks in the article "CREATE MASTER KEY (Transact-SQL)".

    A Fan of SSIS, SSRS and SSAS


    Wednesday, April 3, 2019 12:37 PM
  • If it master key created while enable TDE. The purpose of that master key is

    The master key is a symmetric key used to protect the private keys of certificates and asymmetric keys.
    When a master key is created, it is encrypted using the Triple DES encryption method and protected by a user-provided password. This key is stored in the master database in an encrypted format.


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Wednesday, April 3, 2019 12:40 PM
  • Try this documentation:
    CREATE MASTER KEY (Transact-SQL)

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, April 3, 2019 12:58 PM
  • No, I am talking about the following.

    create master key 
    encryption by password = 'somepassword';


    Regards, Ashif Shaikh

    Database Master Key (DMK) protects Symmetric keys, Asymmetric keys, Certificates, etc. A DMK is protected by the password (which you specify when creating it) as well as the Service Master Key (SMK). 

    Key management works in a hierarchical manner where SMK (Service Master Key) stands at the top of the hierarchy. SMK, in turn, protects the DMK (Database Master Key), which in turn protects the certificate or Asymmetric key which further protects DEK which then protects the data. 

    Thus, SMK makes the decryption simpler by not having a DBA manually intervene. You may be using a password to encrypt the DMK but SMK takes care of decrypting the DMK automatically to open a DB without someone's intervention. 

    If you want to dig deeper, I'd advise that you go through the following link that explains the encryption hierarchy in detail. Especially the diagram should help make things clear.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy?view=sql-server-2017


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, April 3, 2019 1:19 PM