SQL Server encryption : create key inside stored procedure

Question

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'.

Looks like parameters are not supported for this. You could try creating the entire literal string and execute that string with EXEC but you'd need to watch out for any ' characters in the password — Nick.Mc
– Nick.Mc, Commented Sep 11, 2015 at 0:57

Solomon Rutzky · Accepted Answer · 2015-09-11 01:19:13Z

3

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);

edited Sep 11, 2015 at 1:19

answered Sep 11, 2015 at 1:13

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2015-09-11 01:01:48Z

1

You may try to create variable @sql with placeholders and then using REPLACE fill them.

This approach is slightly better vs string concatenation because there is less double and quad - ':

DECLARE @sql NVARCHAR(MAX) = 
    N'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''@DCMPassword''';

SELECT @sql = REPLACE(@sql, '@DCMPassword', @Password);

EXEC(@sql);

answered Sep 11, 2015 at 1:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

SQL Server encryption : create key inside stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related