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
Unfortunately binary values cannot be converted/casted back to real/float data types. This limitation was also present in SQL Server 2000 (and from the information I could find, even in earlier versions). For detailed information on allowed cast/convert operations see http://msdn2.microsoft.com/en-us/library/ms187928.aspx.
I would recommend using a different data type if possible. Another workaround would be to cast to an intermediate data type (i.e. to a string), but this will cause data loss, and I would personally recommend against it because of the data loss potential. This seems to be the case in your particular scenario.
We really appreciate your feedback.
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi Raul
Thanks for the reply.
I don't think it's a cast issue. I am actually casting to a string in my function. The problem seems to be with running over multiple connections. Running the test script on a single machine works fine. If I run the test scripts below the actual and binary values always return the correct values. Only the Encrypted ones fail when running from two separate machines. (all works fine on one connection). Maybe it's how I'm handling the keys. Could a close statement on a connection effect another connections read?
Cheers
--########################################### Setup Data ###############################################
open symmetric key HR01 decryption by password='yes'
DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.Salary decimal
DECLARE @.EmployeeID int
CREATE TABLE TestData
(
empId int,
BonusActual decimal,
SalaryActual decimal,
BonusBinary varbinary(64),
SalaryBinary varbinary(64),
BonusEncrypt varbinary(64),
SalaryEncrypt varbinary(64)
)
SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.Salary = 60000
SET @.LoopCount = 0
WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, SalaryActual, BonusEncrypt,SalaryEncrypt)
VALUES (
@.EmployeeID,
@.Bonus,
@.Salary,
EncryptByKey(Key_GUID('HR01'), (CAST(@.Bonus AS varchar(80)))),
EncryptByKey(Key_GUID('HR01'), (CAST(@.Salary AS varchar(80))))
)
SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.Salary = @.Salary + 500
SET @.LoopCount = @.LoopCount + 1
END
UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt), SalaryBinary = decryptbykey(SalaryEncrypt)
close symmetric key HR01
--########################################### Verify Sums ###############################################
open symmetric key HR01 decryption by password='yes'
-- All figures should equal below (and do)
-- Bonus = 69950000
-- Salary = 309750000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum',
SUM(SalaryActual) as 'SalaryActualSum',
SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)) as 'SalaryBinarySum',
SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal)) as 'SalaryEncryptSum'
FROM TestData
close symmetric key HR01
--########################################### Run Tests ###############################################
open symmetric key HR01 decryption by password='yes'
DECLARE @.BonusActualSum decimal
DECLARE @.SalaryActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.SalaryBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.SalaryEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0
WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),
@.SalaryActualSum = SUM(SalaryActual),
@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),
@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))
FROM TestData
IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1
IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR
@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))
PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))
PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))
END ELSE
SET @.Success = @.Success + 1
SET @.LoopCount = @.LoopCount + 1
END
PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))
close symmetric key HR01
Results from one machine:
Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 0
Success 40
Results from two sessions of SQL Server Management Studio run simultaneously (just showing 1 of the 37):
Warning: Null value is eliminated by an aggregate or other SET operation.
@.BonusActualSum 69950000
@.BonusBinarySum 69950000
@.BonusEncryptSum 69404000
@.SalaryActualSum 309750000
@.SalaryBinarySum 309750000
@.SalaryEncryptSum 309750000
Finish
ActualErrors 0
BinaryErrors 0
EncryptErrors 37
Success 3
|||The strange thing is I cannot get this to fail on my local SQL Express. It's just failing on our development servers and production boxes. It also takes around 3 times as long to run on the high spec'd boxes. Could be an install issue....|||
Sounds unlikely it is related to the installation, but let’s not completely discard the possibility yet.I am suspecting it may be either a problem on how the key is being used or even a concurrency bug with the key ring in addition to the way the query is being cached/optimized by the server that we haven’t seen in our tests.
Let’s try to minimize the variables and see if we can get to the root problem. Can you try the following changes on your query and run them on the system that you know you can repro the problem?
--########################################### Run Tests ###############################################
open symmetric key HR01 decryption by password='yes'
-- if possible, separate the OPEN SYMMETRIC KEY from the rest of the batch
go
-- Key HR01 should be opened w/status = 1
if( (SELECT count(*) FROM sys.openkeys) = 0 )
PRINT 'Failed!!! no keys found in the key-ring'
ELSE
SELECT * FROM sys.openkeys
go
DECLARE @.BonusActualSum decimal
DECLARE @.SalaryActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.SalaryBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.SalaryEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0
-- RG: Let's just make sure the decryptbykey values we are getting back are not null and seem like valid decimal values (hex)
SELECT decryptbykey(BonusEncrypt) as decrypted_bonus, decryptbykey(SalaryEncrypt) as decrypted_salary FROM TestData
WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)),
@.SalaryActualSum = SUM(SalaryActual),
@.SalaryBinarySum = SUM(CAST((CAST(SalaryBinary AS varchar(80)))AS decimal)),
@.SalaryEncryptSum = SUM(CAST((CAST((decryptbykey(SalaryEncrypt)) AS varchar(80)))AS decimal))
FROM TestData
-- Let's make sure no decryption call returned null
if( @.BonusEncryptSum is null OR @.SalaryEncryptSum is null )
BEGIN
PRINT 'FAILED!!! Some values are null'
SELECT @.BonusEncryptSum, @.SalaryEncryptSum, @.LoopCount
END
IF(@.BonusActualSum <> 69950000 OR @.SalaryActualSum <> 309750000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000 OR @.SalaryBinarySum <> 309750000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000 OR @.SalaryEncryptSum <> 309750000) SET @.EncryptErrors = @.EncryptErrors + 1
IF(@.BonusActualSum <> 69950000 OR @.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000 OR @.SalaryActualSum <> 309750000 OR
@.SalaryBinarySum <> 309750000 OR @.SalaryEncryptSum <> 309750000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
PRINT '@.SalaryActualSum ' + CAST(@.SalaryActualSum AS varchar(80))
PRINT '@.SalaryBinarySum ' + CAST(@.SalaryBinarySum AS varchar(80))
PRINT '@.SalaryEncryptSum ' + CAST(@.SalaryEncryptSum AS varchar(80))
END ELSE
SET @.Success = @.Success + 1
SET @.LoopCount = @.LoopCount + 1
END
PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))
close symmetric key HR01
Hopefully we will be able to see if the key-ring and/or decryptbykey values are returning unexpected results. For your own safety, make sure to not post any of the decrypted values from the SELECT statement, just glance through them to see if there is anything that doesn’t seem to be a valid value. You can manually verify them be casting them to a decimal in an ad-hoc query, example:
declare @.x varbinary(100)
set @.x = 0x12000001CBA70800 -- varbinary value copied from decrypted column
print cast(@.x as decimal)
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi Raul
Thanks again for the reply. I amended my scripts (and removed excess variables) and I can still reproduce the error i.e. quite a few of the values returned from your select statement are null. I also showed the issue to the SQL Server team and they have now passed it onto Microsoft as they could not figure it out. I've included the latest script with your changes. I'm almost convinced that it's a install issue as I can run the tests without fail against my local SQL Express.
Thanks
--########################################### Description ###############################################
--The problem seems to be with running the decryptbykey function on the Company Servers
--Running the test script under the 'Run Tests' heading on a single connection will work fine
--most of the time (sometimes 1 or 2 will fail which is a concern). But running with
--the query in two windows of SQL Management Studio simultanteously will error on almost all results.
--The actual and binary values always return the correct values. But the Encrypted values will fail to sum correctly.
--This only seems to be a problem on the servers (Win2000 Server SP4 4GB RAM, 4 CPU)
--ie On local installs of SQL Express (Win XP SP2) we have increased the loop to 4000 and
--we do not have any issues and it runs 3 times as quick.
--########################################### Setup Data ###############################################
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE [name] = 'TestKey')
BEGIN
CREATE SYMMETRIC KEY TestKey WITH algorithm=DES encryption BY password = 'yes'
END
open symmetric key TestKey decryption by password='yes'
DECLARE @.LoopCount int
DECLARE @.Bonus decimal
DECLARE @.EmployeeID int
CREATE TABLE TestData
(
empId int,
BonusActual decimal,
BonusBinary varbinary(64),
BonusEncrypt varbinary(64),
)
SET @.EmployeeID = 1000
SET @.Bonus = 20000
SET @.LoopCount = 0
WHILE (@.LoopCount < 1000)
BEGIN
INSERT INTO TestData (empId, BonusActual, BonusEncrypt)
VALUES (@.EmployeeID, @.Bonus, EncryptByKey(Key_GUID('TestKey'), (CAST(@.Bonus AS varchar(80)))))
SET @.EmployeeID = @.EmployeeID + 1
SET @.Bonus = @.Bonus + 100
SET @.LoopCount = @.LoopCount + 1
END
UPDATE TestData SET BonusBinary = decryptbykey(BonusEncrypt)
close symmetric key TestKey
--########################################### Verify Sums ###############################################
open symmetric key TestKey decryption by password='yes'
-- All figures should equal below
-- Bonus = 69950000
SELECT
SUM(BonusActual) as 'BonusActualSum',
SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)) as 'BonusBinarySum',
SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal)) as 'BonusEncryptSum'
FROM TestData
close symmetric key TestKey
--########################################### Run Tests ###############################################
open symmetric key TestKey decryption by password='yes'
go
-- Key HR01 should be opened w/status = 1
if( (SELECT count(*) FROM sys.openkeys) = 0 )
PRINT 'Failed!!! no keys found in the key-ring'
ELSE
SELECT * FROM sys.openkeys
go
DECLARE @.BonusActualSum decimal
DECLARE @.BonusBinarySum decimal
DECLARE @.BonusEncryptSum decimal
DECLARE @.ActualErrors int
DECLARE @.BinaryErrors int
DECLARE @.EncryptErrors int
DECLARE @.Success int
DECLARE @.LoopCount int
SET @.ActualErrors = 0
SET @.BinaryErrors = 0
SET @.EncryptErrors = 0
SET @.Success = 0
SET @.LoopCount = 0
-- RG: Let's just make sure the decryptbykey values we are getting
--back are not null and seem like valid decimal values (hex)
SELECT decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData
WHILE (@.LoopCount < 40)
BEGIN
SELECT
@.BonusActualSum = SUM(BonusActual),
@.BonusBinarySum = SUM(CAST((CAST(BonusBinary AS varchar(80)))AS decimal)),
@.BonusEncryptSum = SUM(CAST((CAST((decryptbykey(BonusEncrypt)) AS varchar(80)))AS decimal))
FROM TestData
-- Let's make sure no decryption call returned null
if(@.BonusEncryptSum is null)
BEGIN
PRINT 'FAILED!!! Some values are null'
SELECT @.BonusEncryptSum, @.LoopCount
END
IF(@.BonusActualSum <> 69950000) SET @.ActualErrors = @.ActualErrors + 1
IF(@.BonusBinarySum <> 69950000) SET @.BinaryErrors = @.BinaryErrors + 1
IF(@.BonusEncryptSum <> 69950000) SET @.EncryptErrors = @.EncryptErrors + 1
IF(@.BonusActualSum <> 69950000 OR
@.BonusBinarySum <> 69950000 OR
@.BonusEncryptSum <> 69950000)
BEGIN
PRINT '@.BonusActualSum ' + CAST(@.BonusActualSum AS varchar(80))
PRINT '@.BonusBinarySum ' + CAST(@.BonusBinarySum AS varchar(80))
PRINT '@.BonusEncryptSum ' + CAST(@.BonusEncryptSum AS varchar(80))
END
ELSE
SET @.Success = @.Success + 1
SET @.LoopCount = @.LoopCount + 1
SET @.BonusEncryptSum = null
END
PRINT 'Finish'
PRINT 'ActualErrors ' + CAST(@.ActualErrors AS varchar(80))
PRINT 'BinaryErrors ' + CAST(@.BinaryErrors AS varchar(80))
PRINT 'EncryptErrors ' + CAST(@.EncryptErrors AS varchar(80))
PRINT 'Success ' + CAST(@.Success AS varchar(80))
close symmetric key TestKey
|||
This is really strange. Just to make sure that it is decryptbykey usage the one causing problems, can you try this change in the script on the real servers:
SELECT BonusEncrypt , decryptbykey(BonusEncrypt) as decrypted_bonus
FROM TestData
If it is decryptbykey is the one failing, we should see a valid encrypted value (the encrypted value should start with the same sequence, the key GUID on all rows and be of the same length) in the first column and null in the second one. If the first column shows any null then it also fails during the encryptbykey function.
After this test, can you try to change the script to add encryption by a certificate to TestKey and use decryptByKeyAutoCert? The way the decryptByKeyAutoCert handles the key ring may help on this particular case as a workaround; as I have never seen this behavior before, I am not sure if it will really work but it may be worth to give it a try:
OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'yes'
ALTER SYMMETRIC KEY TestKey ADD ENCRYPTION BY CERTIFICATE TestCert
CLOSE SYMMETRIC KEY TestKey
go
And on the test script, remove the OPEN SYMMETRIC KEY statement and use decryptbykeyautocert( cert_id('TestCert'), N'yes', BonusEncrypt) instead of the regular decryptbykey function.
I will also need to investigate more on this one, but any additional information I can get will help. I will also appreciate if you can test an algorithm different than DES. For testing purposes only, can you also give it a try with RC4 and RC2 algorithms?
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
I ran the select with the extra column and all of the BonusEncrypt values were returned correctly. Beside them were quite a few nulls in the decrypted_bonus column.
The interesting thing was changing the encryption method to RC4 or RC2 fixed the problem. Using the certificate didn't seem to make a difference.
Thanks
Warren
|||
Based on your observations I am suspecting the problem may be related to how Windows 2000 CAPI works with DES keys (to be more specific, the parity bits). Can you please try the following two tests?
* First, try using TRIPLE_DES instead of DES, I would expect a similar behavior.
* Save some of the rows that return null for the decrypted value, close and reopen the symmetric key and then copy the encrypted value and run the following script:
declare @.x varbinary(1000)
set @.x = -- Copy the encrypted value here
select datalength(@.x), decryptbykey( @.x )
go
Run this select statement a few times (reopen the key), it should always fail the same way (return null), the first column (datalength) is only to see if the encrypted data length seems correct (should always be the same). I would also expect that the select statement above works consistently with the rows that returned as not-null.
In the meantime I will continue investigating on my own environment. Thanks a lot.
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi Raul
I tried the TRIPLE_DES and it works fine. Just to make sure I repeated the test several times with both encryption methods and DES always failed and TRIPLE_DES never fails.
Also I couldn't get the script you sent to fail. So it only seems to be when reading from a table. Just to make sure I ran this script simultaneously in two windows without any errors:
OPEN SYMMETRIC KEY TestKey decryption by password='yes'
DECLARE @.LoopCount int
DECLARE @.x varbinary(1000)
DECLARE @.y varbinary(1000)
DECLARE @.z varchar(20)
SET @.LoopCount = 0
WHILE (@.LoopCount < 100000)
BEGIN
SET @.x = 0x0070D1427D69FA4BBC7EE4C3A031DCFE01000000C9E733BEA6BD461A28B9F8522280F0644CB68BE940D5E8AE
SET @.LoopCount = @.LoopCount + 1
SET @.y = decryptbykey( @.x )
SET @.z = CAST(@.y AS varchar(20))
IF @.y IS NULL OR @.z <> '20400' PRINT CAST(datalength(@.x) AS varchar(20)) + ' ' + @.z
SET @.y = NULL
SET @.z = NULL
END
CLOSE SYMMETRIC KEY TestKey
|||
I am working on this case with the premier support group, we will continue via premier support.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||
As I mentioned on my last post, the premier support engineers are working on this case with the customer, but they already figured out that the root of the problem is a bug in Windows 2000 CryptoAPI that only happens when using DES keys.
To prevent anyone else to hit the same problem, I decided to update this thread. We strongly suggest updating to Windows 2003 server products, or if you still need to continue using Windows 2000 use a different algorithm, we strongly recommend using TRIPLE_DES keys when using Windows 2000.
Thanks a lot, and kudos to the Premier Support team for their great work and help on this case!
-Raul Garcia
SDE/T
SQL Server Engine
. I