Key Maintenance And Stored Procedures

Basically I would like to ask whether parameters can be used to pass the value of the 'symmetric key id', 'certificate' and optionally 'password' to a stored procedure that uses encryption functions.

The reason this is appealing is that when encryption keys etc change over time (we have a requirement to decrypt data, destroy and create new keys, then encrypt data every time we lose a staff - don't ask), as we would be passing the value of keys, passwords and certificates as parameters to a standard stored procedure.

Hardcoded Example (Working)

USE PSS

GO

CREATE PROC insert_payer_ba



-- define parameters

@param_rec_id NVARCHAR(MAX),

@param_bsb NVARCHAR(MAX),

@param_account NVARCHAR(MAX),

@param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY bartlett_sym

DECRYPTION BY CERTIFICATE bartlett_cert

WITH PASSWORD = 'Bartlett12_3';

DECLARE @en_rec_id varbinary(max);

SELECT @en_rec_id = EncryptByKey(Key_GUID('bartlett_sym'),@param_rec_id);

DECLARE @en_bsb varbinary(max);

SELECT @en_bsb = EncryptByKey(Key_GUID('bartlett_sym'),@param_bsb);

DECLARE @en_account varbinary(max);

SELECT @en_account = EncryptByKey(Key_GUID('bartlett_sym'),@param_account);

DECLARE @en_account_name varbinary(max);

SELECT @en_account_name = EncryptByKey(Key_GUID('bartlett_sym'),@param_account_name);

INSERT INTO [PSS].[dbo].[payer_ba_sym]

(rec_id, bsb, account, account_name)

VALUES (

@en_rec_id,

@en_bsb,

@en_account,

@en_account_name

);

CLOSE SYMMETRIC KEY bartlett_sym;

END

GO



PROPOSED USAGE (WHICH DOESN'T WORK)



USE PSS

GO

CREATE PROC insert_payer_ba



-- define parameters

@param_symkeyguid NVARCHAR(MAX), --(tried VARBINARY as well)

@param_cert NVARCHAR(MAX),

@param_certpass VARBINARY(MAX),

@param_rec_id NVARCHAR(MAX),

@param_bsb NVARCHAR(MAX),

@param_account NVARCHAR(MAX),

@param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY @param_symkeyguid

DECRYPTION BY CERTIFICATE @param_cert

WITH PASSWORD = @param_certpass;

end

DECLARE @en_rec_id varbinary(max);

SELECT @en_rec_id = EncryptByKey(Key_GUID(@param_symkeyguid),@param_rec_id);

DECLARE @en_bsb varbinary(max);

SELECT @en_bsb = EncryptByKey(Key_GUID(@param_symkeyguid),@param_bsb);

DECLARE @en_account varbinary(max);

SELECT @en_account = EncryptByKey(Key_GUID(@param_symkeyguid),@param_account);

DECLARE @en_account_name varbinary(max);

SELECT @en_account_name = EncryptByKey(Key_GUID(@param_symkeyguid),@param_account_name);

INSERT INTO payer_ba_sym

(rec_id, bsb, account, account_name)

VALUES (

@en_rec_id,

@en_bsb,

@en_account,

@en_account_name

);

CLOSE SYMMETRIC KEY @param_symkeyguid;

END

GO

Any assistance in correcting this syntax (if indeed these functions accept parameters would be greatly appreciated).

- Andrew