I need to create a stored procedure that allows our password custodian to recreate the symmetric key in our DR database, but somehow it always complains about incorrect syntax. Is this allowed in SQL Server 2008 R2 or it is just incorrect syntax ? Thanks
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
I also tried to use executesql but still get the same error
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@Password string' ;
EXECUTE sp_executesql N'CREATE MASTER KEY ENCRYPTION BY PASSWORD = @DCMPassword', @ParmDefinition , @DCMPassword=@Password
Edited to add error :
Msg 102, Level 15, State 1, Procedure sp_BCPRecreateEncryption, Line 13
Incorrect syntax near '@Password'.Msg 102, Level 15, State 1, Procedure sp_BCPRecreateEncryption, Line 20
Incorrect syntax near '@Password'.
The main problem is that you cannot use variables to set passwords; they need to be string literals. If you look at the MSDN page for just CREATE MASTER KEY you will notice that there is no option to pass in a local variable for the password. The documentation for CREATE SYMMETRIC KEY also shows that only string literals are valid for KEY_SOURCE and IDENTITY_VALUE.
A secondary issue with your attempt to fix via Dynamic SQL is that string is not a valid datatype.
If you want to get the value from a stored procedure input parameter, then you will need to concatenate that value into the Dynamic SQL. Be sure to sanitize for single-quotes first ;-) to avoid SQL Injection issues.
SET @Password = REPLACE(@Password, N'''', N'''''');
DECLARE @SQL NVARCHAR(MAX) = N'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''
+ @Password
+ N''';';
EXEC(@SQL);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments