Decrypting with password parameter

Question

Hi all,
  in my db, I am using a symmetric key that is protected by a certficate with a password.  I've been successful at encryting and decrypting the data so far but I've run across a little problem.  I'm trying to pass in my password that protects the certificate as a variable so that I could possibly build a user-defined function that will do this for me.  That way my password wouldn't be shown in plain text in the function.  When I try running the following though, I'm getting a syntax error at the with password clause.

DECLARE @Password nvarchar(max)

SET @Password = 'myPass'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCertificate WITH PASSWORD = @Password

SELECT CONVERT(nvarchar, DecryptByKey(encrypted_field))

FROM dbo.myTable

CLOSE SYMMETRIC KEY MyKey

Is it possible to use a variable at the WITH PASSWORD clause?  Or must it be a literal string?

Answer 1

   Unfortunately OPEN SYMMETRIC KEY DDL requires that the password is a literal. We have an open SQL Server Connection item regarding this limitation: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369270.

   We would really appreciate your time in rating this open feedback item, as well as giving us any additional feedback on the Connect item. We use this information to help us triage the priority of tasks that we will be working on for future versions of SQL Server.

  The only existing workaround is to create dynamic SQL, being very careful on properly escaping any literal that the caller may control (i.e. the password, and the key/cert names if they are also passed as parameters).

  I hope this information helps,
- Raul Garcia
  SDE/T
  SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Raul,
  thanks for your reply.  I see then that I may need to make the sql dynamic.  Hopefully this is something that was addressed in SQL 2008 or hopefully in future releases.  Thanks again for your help.