SQL Server加密：在存储过程中创建密钥

kwangsa 发表于 Dev

广沙

我需要创建一个存储过程，使我们的密码保管人可以在DR数据库中重新创建对称密钥，但是无论如何它总是会抱怨语法不正确。这是SQL Server 2008 R2中允许的，还是语法错误？谢谢

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_BCPRecreateEncryption] 
    @Password varchar(255)
AS
BEGIN
    DROP SYMMETRIC KEY SymmetricKeyName
    DROP CERTIFICATE EncryptCert
    DROP MASTER KEY

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = @Password

    CREATE CERTIFICATE EncryptCert
        WITH SUBJECT = N'EncryptCert', START_DATE = N'08/06/2014 07:16:08', EXPIRY_DATE = N'08/06/2042 07:16:08'
        ACTIVE FOR BEGIN_DIALOG = ON;

    CREATE SYMMETRIC KEY SymmetricKeyName
        WITH KEY_SOURCE = @Password, 
                IDENTITY_VALUE = @Password, 
             ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE EncryptCert;
END

我也尝试使用，executesql但仍然收到相同的错误

DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@Password string' ;

EXECUTE sp_executesql N'CREATE MASTER KEY ENCRYPTION BY PASSWORD = @DCMPassword', @ParmDefinition , @DCMPassword=@Password

编辑添加错误：

消息102，级别15，状态1，过程sp_BCPRecreateEncryption，第13行
'@Password'附近的语法不正确。

消息102，级别15，状态1，过程sp_BCPRecreateEncryption，第20行
'@Password'附近的语法不正确。

所罗门·鲁兹基

主要问题是您不能使用变量来设置密码。它们必须是字符串文字。如果仅在MSDN页面上查看CREATE MASTER KEY，您会注意到没有选项为传递本地变量password。CREATE SYMMETRIC KEY的文档还显示，只有字符串文字对KEY_SOURCE和有效IDENTITY_VALUE。

您尝试通过Dynamic SQL修复的第二个问题string是无效的数据类型。

如果要从存储过程输入参数中获取值，则需要将该值连接到Dynamic SQL中。确保先对单引号进行消毒;-)，以避免SQL注入问题。

SET @Password = REPLACE(@Password, N'''', N'''''');

DECLARE @SQL NVARCHAR(MAX) = N'
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''
  + @Password
  + N''';';

EXEC(@SQL);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。