Encryption By Certificate
- by user1817240
I am encrypting customer name in database at database level. While saving in database only first letter of customer name is saved and hence while decrypting only first letter is retrieved.
The following code shows the test sp.
ALTER PROCEDURE [dbo].[spc_test_insert] (
@sFIRST_NAME typ_encryptedtext,
)
AS
BEGIN
DECLARE @sSENCRYPTION_KEY char(15)
DECLARE @sCERTIFICATE char(22)
SET @sSENCRYPTION_KEY='SymmetricKey1'
SET @sCERTIFICATE='CustomerCertificate'
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE CustomerCertificate;
INSERT INTO test_table (
FIRST_NAME,
) Values (
-- Add the Params to be Added...
EncryptByKey(Key_GUID(@sSENCRYPTION_KEY),@sFIRST_NAME),
)
CLOSE SYMMETRIC KEY SymmetricKey1
END
encryption decryption working fine in normal insert but its not working in stored procedure.
