Cannot find the symmetric key , because it does not exist or you do not have permission on sqlserver 2005 standard edition

  • Hi guys,

    We are facing some problems in Sqlserver 2005 standard edition,while opening the symmetric key (certificate decryption) on sa login.The key and certificate are created under normal user"xxxx".

    The error message is:

    Cannot find the symmetric key "xxxx", because it does not exist or you do not have permission.

    The script we are using as follows :

    OPEN SYMMETRIC KEY SKey_AD

    DECRYPTION BY CERTIFICATE ActiveDirectoryInfo;

  • Have you put a "GO" statement to define the end of the procedure?

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • I had the same problem when the database AD account accessing the database was different that the account that created it. I found the following and am in the process of testing it:

    GRANT REFERENCES ON SYMMETRIC KEY :: ]

    For example:

    GRANT REFERENCES ON SYMMETRIC KEY :: sk_Admin TO [MyDomain\Jerry] ;

  • We tried...

    GRANT REFERENCES ON SYMMETRIC KEY::[keyname] TO

    It runs successfully, but it still does not give permissions to the user. There has to be something else we are missing.

  • wait just got it...

    need to give access to the certificate first then the key.

    do this first then give access to the symmetric key with the line above

    GRANT CONTROL ON CERTIFICATE::Certificatename TO

  • hi,

    i have tried as your said, but it is not working again permissions issue.

    🙂

  • Any resolve to this... I'm having the same issue.

    This grant succeeds...

    GRANT alter,control,references,take ownership, view definition ON SYMMETRIC KEY::[BTS_Key1] TO [johnm]

    GO

    grant alter,control,references,take ownership, view definition on certificate::BTS_CERT1 to johnm;

    GO

    When johnm does this in the same database, it fails...

    OPEN SYMMETRIC KEY [dbo.BTS_Key1]

    DECRYPTION BY CERTIFICATE [dbo.BTS_CERT1];

    Msg 15151, Level 16, State 1, Line 1

    Cannot find the symmetric key 'dbo.BTS_Key1', because it does not exist or you do not have permission.

    But, If I grant johnm SA fixed server role... it succeeds.

    Troy.

    #

  • Doing this...

    GRANT view definition ON SYMMETRIC KEY::[BTS_Key1] TO [johnm]

    grant control on certificate::BTS_CERT1 to johnm

    And removing the schema seems to be the key.

    OPEN SYMMETRIC KEY [BTS_Key1]

    DECRYPTION BY CERTIFICATE [BTS_CERT1];

    Troy.

    #

  • Hi there, 
    I am facing the same issue. I am working on setting up encryption/decryption mechanism for my organization. The code works fine with  my login (i.e similar to SA) however the same code do not work for other login (which is used by online reports) .  It gives the error. 

    use db1
     OPEN SYMMETRIC KEY [xyz] OPEN SYMMETRIC KEY [xyz]  
     DECRYPTION BY CERTIFICATE cert_abc; DECRYPTION BY CERTIFICATE cert_abc;  

    Msg 15151, Level 16, State 1, Line 3Msg 15151, Level 16, State 1, Line 3
    Cannot find the symmetric key 'xyz', because it does not exist or you do not have permission.Cannot find the symmetric key 'xyz', because it does not exist or you do not have permission.

    I have used the solutions provided above, however it is still gives same error.

    GRANT REFERENCES, CONTROL, VIEW DEFINITION ON CERTIFICATE::[cert_abc] TO [JohnDoe]
    GRANT ALTER, REFERENCES, CONTROL, VIEW DEFINITION ON SYMMETRIC KEY::[xyz] TO [JohnDoe]

    Can you please let me know if anything else required to be done ?

  • You can use the below command to open the symmetric key.

    --Grant View
    GRANT VIEW DEFINITION ON SYMMETRIC KEY::SQLSymmetricKey TO [UserName]

    --Grant Control
    GRANT CONTROL ON CERTIFICATE::SelfSignedCertificate TO [UserName]

    • This reply was modified 4 days, 12 hours ago by  yash.viva93.

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

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