3

I have a permissions issue with regard to using a symmetric key under a specific user name when a stored procedure is executed.

Despite running

GRANT CONTROL ON CERTIFICATE::myCert TO myUser
GRANT VIEW DEFINITION ON SYMMETRIC KEY::myKey TO myUser

I still get the same error:

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

The Master Key, Certificate and Symmetric Key were set under the database the user name relates to.

If I run the SP under Windows Authentication it works fine.

Here's the stored procedure:

OPEN SYMMETRIC KEY myKey DECRYPTION
BY CERTIFICATE myCert

INSERT INTO sp_Password
(billEncryptPassword)
VALUES(ENCRYPTBYKEY(KEY_GUID('myKey'),@billEncryptPassword))

RETURN @@IDENTITY       
CLOSE SYMMETRIC KEY myKey

What have I missed?

ComfortablyNumb
  • 1,448
  • 10
  • 37
  • 64

2 Answers2

0

You're missing CLOSE MASTER KEY afterwards and that's messing with your subsequent encryptions/decryptions.

Himanshu
  • 31,810
  • 31
  • 111
  • 133
RyanHilliker
  • 1
  • 1
0

I am having the same problem even while running this as a SysAdmin.

To work around it I am currently closing all open keys which works fine. I'd much rather close only what I had open though.

CLOSE ALL SYMMETRIC KEYS;

--- Addendum

I just tried this with AES_256 vs DES encryption and the AES_256 worked on my end.

PseudoToad
  • 1,504
  • 1
  • 16
  • 34
  • 1
    I have managed to get it working by adding sysadmin server role to the login but I'm not sure if this is a real fix as the login is supposed to be a website connection. – ComfortablyNumb Jul 26 '11 at 15:31
  • We've just had the same issue; GRANT did nothing, but adding the sysadmin role solves it. Did you ever discover anything more elegant?! – Magnus Smith Jun 25 '12 at 17:05
  • 1
    You really shouldn't promote to sysadmin. That would be a hack (and a dangerous one at that). I was able tog et this to work by using AES_256. – PseudoToad Jul 10 '12 at 18:08