Help With EncryptByKey
Aug 4, 2006
hi! guyz..will you help me with this..why is it everytime i execute the T-SQL below it wont decrypt the string I encrypted, the 'encrypt me' string..will you check my code please..thanks in advance!!!
create master key encryption by password = 'p@ssword'
create certificate MyCertificate
with subject = 'My certificate',
start_date = '08/05/2006'
create symmetric key my_symmetric_key
with
algorithm = TRIPLE_DES
encryption by certificate MyCertificate
declare @var_enc varbinary(200)
declare @var_dec nvarchar(200)
declare @string nvarchar (200)
set @string = 'encrypt me'
select @string
open symmetric key my_symmetric_key
decryption by certificate MyCertificate
select @var_enc = EncryptByKey(key_GUID('my_symmetric_key'),@string)
select @var_enc as 'ENCRYPTED'
select DecryptByKey(@var_enc) as 'DECRYPTED'
close symmetric key my_symmetric_key
drop symmetric key my_symmetric_key
drop certificate MyCertificate
drop master key
View 1 Replies
Jan 18, 2012
i have a ms sql base which contains tables encrypted with EncryptByKey, who knows how to make me a script do save the encrypted tables to clear text pm me in ym : hgfrfv or msn : [URL], i have all the keys used on encryption and all those stuff.
View 1 Replies
View Related
Aug 8, 2006
When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:
ALTER PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50))
AS
BEGIN
OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';
Declare @Temp varbinary(8000);
Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);
close symmetric key MyKey;
select @Temp as temp;
END
The result I get for this procedure is null. Is there something wrong with this code?
View 5 Replies
View Related
Jan 21, 2008
The DecryptByKey function occasionally returns null even though the EncryptByKey function retuned a non-null value. The problem only occurs for a subset of rows returned by a single select and every time the script is executed, a different set of rows is affected by the problem. Occasionally all fields get encrypted/decrypted successfully, but this is rare.
It seems that the EncryptByKey function occasionally returns a value that can not be decrypted at a later point in time.
I am running on Windows XP Professional SP 2 with SQL Server 9.0.3042.
I have included a sample of the code below.
Thank you,
Mike
CREATE FUNCTION [dbo].[encrypt_text]
(
@input_text varchar(255)
)
RETURNS varbinary(8000)
AS
BEGIN
RETURN EncryptByKey(Key_GUID('eia_key'), @input_text)
END
CREATE FUNCTION decrypt_text
(
@input_text varbinary(8000)
)
RETURNS varchar(255)
AS
BEGIN
return convert(varchar(255),DecryptByKey(@input_text))
END
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N'eia_key')
DROP SYMMETRIC KEY eia_key
CREATE SYMMETRIC KEY eia_key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = '???'
OPEN SYMMETRIC KEY eia_key DECRYPTION BY PASSWORD = '???'
execute util_print 'Deleting data'
execute ld_delete_lips_data
execute util_print 'Loading data'
set nocount on
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (1, 'TERM', 0, 0)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (2, '0 - 2', 0, 2)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (3, '2 - 5', 2, 5)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (4, '5 - 10', 5, 10)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (5, '10+', 10, null)
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (1, dbo.encrypt_text('3 Month'), dbo.encrypt_text('Blended'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (2, dbo.encrypt_text('1 Year'), dbo.encrypt_text('Fundamental'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (3, dbo.encrypt_text('Technical'), dbo.encrypt_text('Technical'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (1, dbo.encrypt_text('Low'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (2, dbo.encrypt_text('Median'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (3, dbo.encrypt_text('High'))
execute util_reseed_ident 'asset_classes', 0
execute util_execute_sql 'insert into asset_classes default values', 11
insert into sectors (sector_id, sector_name)
values (1, dbo.encrypt_text('Sovereign'))
insert into sectors (sector_id, sector_name)
values (2, dbo.encrypt_text('Inflation Linked'))
insert into sectors (sector_id, sector_name)
values (3, dbo.encrypt_text('Quasi & Foreign Government'))
insert into sectors (sector_id, sector_name)
values (4, dbo.encrypt_text('Securitized/Collateralized'))
insert into sectors (sector_id, sector_name)
values (5, dbo.encrypt_text('Corporate'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (6, dbo.encrypt_text('AAA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (7, dbo.encrypt_text('AA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (8, dbo.encrypt_text('A'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (9, dbo.encrypt_text('BBB'))
insert into sectors (sector_id, sector_name)
values (10, dbo.encrypt_text('High Yield'))
insert into sectors (sector_id, sector_name)
values (11, dbo.encrypt_text('Emerging Debt'))
set nocount off
insert into currencies (currency_id, currency_name, currency_code)
select CurrencyID, dbo.encrypt_text(CurrencyName), dbo.encrypt_text(CurrencyCode)
from lips_import..Currencies
View 3 Replies
View Related
