DecryptByKey + CTE
Nov 6, 2007
I have a SPROC in which I am using a CTE. Turns out, one of the encrypted fields isn't being decrypted properly - I get "system characters" and not NULL values for the encrypted field, meaning the conversion is failing somewhere. I have tried various combinations to try and decrypt the value, but to no avail so far - I have tried decrypting in the CTE alone, tried decrypting in the CTE plus the SELECT that follows the CTE, as well as only the SELECT statement that follows the CTE. If I run the same conversion in regulare SELECT, outside the CTE, the value comes out just fine.
Any thoughts?
Edit: By the way, I am opening the key using the certificate outside the CTE and closing it after the SELECT that follows the CTE.
View 1 Replies
Jan 12, 2007
Hi
I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column?
Thanks in advance
Waz
open symmetric key HR01 decryption by password='yes'
DECLARE @Bonus decimal
DECLARE @Salary decimal
DECLARE @Errors int
DECLARE @Success int
DECLARE @LoopCount int
SET @Errors = 0
SET @Success = 0
SET @LoopCount = 0
WHILE (@LoopCount < 40)
BEGIN
SELECT
@Bonus = SUM(convert(float,convert(varchar(80),decryptbykey(Bonus)))),
@Salary = SUM(convert(float,convert(varchar(80),decryptbykey(Salary))))
FROM ChallengeEmployee
WHERE ChallengeID = 5
IF(@Bonus <> 14945490 OR @Salary <> 36382777)
BEGIN
PRINT 'Bonus ' + CAST(@Bonus AS varchar(80))
PRINT 'Salary ' + CAST(@Salary AS varchar(80))
SET @Errors = @Errors + 1
END
ELSE
SET @Success = @Success + 1
SET @LoopCount = @LoopCount + 1
END
PRINT 'Finish'
PRINT 'Errors ' + CAST(@Errors AS varchar(80))
PRINT 'Success ' + CAST(@Success AS varchar(80))
close symmetric key HR01
View 13 Replies
View Related
Nov 6, 2015
How to grant users the right to use the DECRYPTBYKEY function to decrypt the data. I have seen some people talk about using a stored procedure or view to surface the decrypted data, but how would you implement that when trying to pull back a single dataset? It would be best to use an inline function to allow the row to be returned decrypted, but opening the keys isn't allowed in the function construct.
So, I know I have to be missing something, but how do you let basic users (db_reader types) decrypt the data they need based on a custom database role? What do I need to give the user permission to?
My setup is simple:
- I have my SMK
- I have a DMK encrypted by password
- I have my self signed certificate
- I have my symmetric Key encrypted by the certificate
View 0 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
