passing key decryption password to Stored procedure

Question

I try to pass the key used to decrypt symmetric key to a stored procedure as a parameter like this:

OPEN SYMMETRIC KEY MyKey
    DECRYPTION BY PASSWORD=@ keypassword;

I get an error message saying "Incorrect syntax near "@keypassword".
Is there something that I am missing?

Answer 1

  Unfortunately the syntax doesn't allow a variable as a password. You will have to either use dynamic SQL to use the password (make sure you escape the user input properly to prevent SQL injection) or, if it is possible in your particular scenario, consider using the key hierarchy available in SQL Server 2005.

 

 -Raul Garcia

  SDE/T

  SQL Server Engine