CODEWITHSUNDEEP
sql-serverstored-proceduresencryptionsql-server-2008-r2
kwangsa
kwangsa

Reputation: 1711

SQL Server encryption : create key inside stored procedure

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

Upvotes: 1

Views: 1973

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48924

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

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 176224

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

Upvotes: 1

Related Questions