如何将解密证书密码作为参数传递给存储过程

Question

使用SQL Server 2012 v11.0.2100.60，我正试图创建一个存储过程，通过带密码的证书解密对称密钥。

如果我把硬编码的密码放在存储过程中，它可以正常工作，但我希望能够将证书密码作为参数传递给存储过程(这样明文密码就不会出现在服务器上的任何地方(。

这是我试过的。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testSproc]
@pw VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
OPEN SYMMETRIC KEY key_name  
DECRYPTION BY CERTIFICATE cert_name WITH PASSWORD = '''+@pw+''';
SELECT ....

当我执行时，我得到以下内容：

消息15466，级别16，状态9，过程testSproc，第9行
解密过程中出错。

这就是我执行时的样子

DECLARE @return_value int
EXEC    @return_value = [dbo].[testSproc]
@GL = N'98787notTheRealPW45668456318'
-- I've tried both N'98787notTheRealPW45668456318' and just '98787notTheRealPW45668456318'
SELECT  'Return Value' = @return_value
GO

我传递参数是否错误？或者存储过程中的变量设置不正确？非常感谢您的帮助！

Answer 1

想明白了。将OPEN KEY命令放入它自己的变量中，并在那里传递pw。那就执行吧。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testSproc]
@pw NVARCHAR(max)
AS
BEGIN
DECLARE @SQL AS NVARCHAR(MAX);
SET NOCOUNT ON;
SET @SQL = 'OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE cert_name WITH PASSWORD = '''+@pw+'''';
EXEC (@SQL)
SELECT ....