Problem Opening Symmetric Key with passed "password"

Question

We call "Open symmetric key" from a stored proc, passing in the password like this:

...

OPEN SYMMETRIC KEY DECRYPTION BY PASSWORD = @PassWord

...

@Password is declared as varchar (100) in the stored proc input argument section but the statement does not pass the t-sql syntax check.  The complaint is that @Password is not a String.  (Really?)  What is the correct syntax then?

TIA,

barkingdog

Answer 1

The OPEN SYMMETRIC KEY statement can't be parameterized, it requires a
literal string value if you use the "password=" clause.

Cheers,
Bob Beauchemin

Answer 2

Hi <

I am not sure if this going to help you , can you give a try on this

CREATE

PROC [dbo].[something]

@Password

sysname

AS

DECLARE

@open nvarchar(200)

SET

@open = 'OPEN SYMMETRIC KEY symmkeyname DECRYPTION BY PASSWORD =' + quotename (@password,'''')

EXEC

sp_executesql @open

But i do accept what BOB says is true .

---

Thanks, Leks

Answer 3

BTW. There is a Microsoft connect issue already open requesting parameterization of the OPEN SYMMETRIC KEY DDL: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369270.

I would really recommend voting for it (that makes it easier for us to triage customer requests) and continue providing us with feedback.

  Thanks a lot in advance.
  -Raul Garcia
   SDE/T
   SQL Server Engine

---

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

Answer 4

Thanks for the replies.  It's  very natural to invoke "Open key" in a stored proc, passing in the Passphrase as a parameter.  Dynamic sql works but prevents effective pre-compilation of the stored proc containing the dynamic sql. More a workaround than a highly quality  solution.


Barkingdog

P.S. I voted for the issue on Microsoft connect.

Answer 5

P.S. I voted for the issue on Microsoft connect.

Hi,

You may post the link here, it will beneficial to other members who have the same problems.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.