EncryptByKey DecryptByKey Occasionally Returns Null

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