title: Open Symmetric Key With Password In Stored Procedure
body: <p>I am trying to write a stored procedure to decrypt some data encrypted by a Symmetric Key that's encrypted with an Asymmetric Key with a password.</p>
  
  <p><code>OPEN SYMMETRIC KEY</code> requires a string literal for the password so I had to do a work around with <code>EXEC sp_executesql</code>. Is there a better way to do this?</p>
  
  <pre><code>DECLARE @open nvarchar(200), @close nvarchar(200)
  SET @open = 'OPEN SYMMETRIC KEY skey DECRYPTION BY ASYMMETRIC KEY akey WITH PASSWORD = ' + quotename(@password,'''') + ';';
  SET @close = 'CLOSE SYMMETRIC KEY skey;';
  
  EXEC sp_executesql @open
  
  SELECT [TransactionID],Convert(varchar(max),DECRYPTBYKEY([EncryptedText])) as DecryptedText FROM [dbo].[TestTable];
  
  EXEC sp_executesql @close
  </code></pre>
  
  <p>If you execute it with the wrong password it throws up the following errors:</p>
  
  <pre><code>Msg 15466, Level 16, State 1, Line 1
  An error occurred during decryption.
  Msg 15315, Level 16, State 1, Line 1
  The key 'skey' is not open. Please open the key before using it.
  </code></pre>
  
  <p>Should I just wrap <code>EXEC sp_executesql @open</code> in a <code>TRY ... CATCH</code> and return <code>NULL</code> or is there a (more) elegant way of handling this?</p>
  
  <p><strong>EDIT:</strong> What's the best way to handle someone calling this procedure with an incorrect password?</p>