locked
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission RRS feed

  • Question

  • We are using SQL Server 2012.
    Using an admin user, we created master Key encryption, Certificate and Symmetric Key.
    We then grant permission for the certificate and symmetric key to User2.
    But, when User2 tries to OPEN MASTER KEY DECRYPTION, we are getting the error "Cannot find the symmetric key 'master key', because it does not exist or you do not have permission".

    How can we fix it ?

    Thank you.

    USE myDatabase
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'MyPwd'
    GO

    CREATE CERTIFICATE MyCert WITH SUBJECT = 'My Encryption';
    GO

    CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCert;
    GO

    --Give permission for the certificate and symmetric key to User2
    GRANT CONTROL ON CERTIFICATE :: MyCert TO User2
    go
    GRANT REFERENCES ON SYMMETRIC KEY::MySymmetricKey TO User2
    go

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyPwd'
    --> This is where I get the error "Cannot find the symmetric key 'master key', because it does not exist or you do not have permission"

    • Moved by Kalman Toth Tuesday, September 24, 2013 3:18 PM Not t-sql
    Tuesday, September 24, 2013 2:43 PM

Answers

  • I fixed my problem.

    http://technet.microsoft.com/en-us/library/ms174433.aspx
    says the following
    “If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.”

    select
    name, is_master_key_encrypted_by_server from sys.databases shows that my database master key is encrypted by the service master key.
    So, I don't need
     to use the OPEN MASTER KEY statement.

    • Marked as answer by Sofiya Li Wednesday, September 25, 2013 8:32 AM
    Tuesday, September 24, 2013 8:37 PM

All replies

  • We are using SQL Server 2012.
    Using an admin user, we created master Key encryption, Certificate and Symmetric Key.
    We then grant permission for the certificate and symmetric key to User2.
    But, when User2 tries to OPEN MASTER KEY DECRYPTION, we are getting the error "Cannot find the symmetric key 'master key', because it does not exist or you do not have permission".

    How can we fix it ?

    Thank you.

    USE myDatabase
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'MyPwd'
    GO

    CREATE CERTIFICATE MyCert WITH SUBJECT = 'My Encryption';
    GO

    CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCert;
    GO

    --Give permission for the certificate and symmetric key to User2
    GRANT CONTROL ON CERTIFICATE :: MyCert TO User2
    go
    GRANT REFERENCES ON SYMMETRIC KEY::MySymmetricKey TO User2
    go

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyPwd'
    --> This is where I get the error "Cannot find the symmetric key 'master key', because it does not exist or you do not have permission"

    • Merged by Sofiya Li Wednesday, September 25, 2013 8:29 AM the same question
    Tuesday, September 24, 2013 2:42 PM
  • I am moving it to security.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, September 24, 2013 3:17 PM
  • I fixed my problem.

    http://technet.microsoft.com/en-us/library/ms174433.aspx
    says the following
    “If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.”

    select
    name, is_master_key_encrypted_by_server from sys.databases shows that my database master key is encrypted by the service master key.
    So, I don't need
     to use the OPEN MASTER KEY statement.

    • Marked as answer by Sofiya Li Wednesday, September 25, 2013 8:32 AM
    Tuesday, September 24, 2013 8:37 PM
  • Hi aujong,

    You post same question 2 times. Please avoid this practice on Forum, I  have merged the following thread  into this thread .  

    Thanks for your post and glad to hear that the issue is resolved. You are kindly post a reply to share your solution and I will mark it as answer. That way, other community members could benefit from your sharing.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, September 25, 2013 8:32 AM
  • Thank you, Sofiya.
    In the future, if I am not sure which forum I should post my question to, can I post in 2 different forums ?
    Wednesday, September 25, 2013 1:55 PM