October 1, 2007 at 8:06 am
Gal...can you direct me to the PASS article you speak of, and yes, it would be fabulous to have some semblance of code I could reference to...I think you may have hit my problem on the head...in simple terms...we have a db that our PM offered a client any variation of 13 lookup variables, including acct. number (contains) and SSN(exact)...I recently tried to do a simple lookup for one SSN and the results took over 1 minute! This is definitely due to the encryption, as unencrypted renders in seconds. making matters worse, our PM allowed "like' searches of account numbers, which are encrypted, so any type of unencrypted indexing/partitoning system will not work!!
Anyway...let me know if you need more info and if you could get me a link to the article, and some basic code, that may point me to the right direction!!
Thanks everyone!!!
PS...another possibility that we are exploring at this point...loading tables on RAMDISK, which in theory, should satisfy the client's requirements...anyone have any experience doing this?
October 1, 2007 at 8:20 am
I have heard some people say that RAMDISKs work great and love them. That's an interesting idea, maybe drop a separate database up there, same rights, security, and move the encrypted data to that database on demand and decrypt it?
October 1, 2007 at 8:31 am
yes...a friend of mine suggested doing that as we have 3 basic tables to search...Accts, SSN's, and a flat table holding all the rest of the item's data i.e. name, address, etc.
What little I have found researching this method (I only spent a hour on Fri :-P) seems as if it will satisfy our client's demands...any input on this method from anyone would be extremely appreciated!!1
October 1, 2007 at 10:14 am
Robert Heynen (10/1/2007)
Gal...can you direct me to the PASS article you speak of, and yes, it would be fabulous to have some semblance of code I could reference to...
It wasn't an article. It was a presentation at a conference two weeks ago. I can do some rough code, probably only tomorrow. I'll have to do it from memory.
If your client can live with passing a certain portion of the account number to search on (first 5, last 5 or similar) then I can give you an encrypt and compare technique. If they can't then they're stuck with decrypting the entire table to search - slow. It's a trade off, and it is their call
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2007 at 8:41 pm
Won't work with 2005's encryption routines. They're non-deterministic. Encrypt and compare will get you no matches.
Didn't know that. Wow... the boy's in Redmond pretty much blew it there...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 12:16 am
Jeff Moden (10/1/2007)
Didn't know that. Wow... the boy's in Redmond pretty much blew it there...
Don't blame MS for this one. The 2005 encryption uses industry-standard, well proven encryption routines - triple DES, AES, etc. Those algorithms are non-deterministic by nature. That's just how they work.
I'm not a cryptography guru, but I would imagine that deterministic encryption routines would not be as strong (since you can then use dictionaries of encrypted values to locate values)
Hashes are deterministic and there are, if you know where to look, massive dictionaries of hash values for standard hashing algorithms, to speed up password cracks. No need to hash and compare, you can just compare the hased values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2007 at 4:43 am
Ok, now that the crises are out of the way and I have a couple free minutes, let em see what I can conjure up for you.
This is going to be example code, not something you can dump straight into your production db.
Watch this space....:)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2007 at 5:49 am
First, a big, big thank you to Lara Rubbelke from Microsoft who presented on this at PASS recently. This code is derived from her presentation, and not somethng I came up with myself.
-- create the keys
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'w3j^h54j*23h3@34jn$a5dn@ss#sff$33Q#Ek34jjk(df(f(23'; -- straight from BoL
-- remember to backup your db master key somewhere safe.
-- the asymmetric key will be used to encrypt the symmetric key. The symmetric key will be used to encrypt the data.
-- the asymetric key is encrypted by the DB master key
CREATE ASYMMETRIC KEY EncryptSymKey
WITH ALGORITHM = RSA_1024;
CREATE SYMMETRIC KEY EncryptData
WITH algorithm = Triple_des -- would prefer AES with a bitlength > 128, but not available on the machine I'm using (win xp)
ENCRYPTION BY ASYMMETRIC KEY EncryptSymKey;
GO
-- should see 2 symmetric and 1 asymmetric key
SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.asymmetric_keys;
GO
CREATE TABLE HashSalt (
EncryptedSalt VARBINARY(128)
);
GO
-- this table needs to be searched by the account number (as a whole) or by a wildcard of account number, with a minimum of
-- left-most 5 characters
CREATE TABLE HasSensitiveData (
AccountID INT IDENTITY PRIMARY KEY,
Descr VARCHAR(100),
EncryptedAccountNumber VARBINARY(256), -- nondeterministic, so can't search.
AccountHash VARBINARY (256), -- used for exact matches
PartialAccountHash VARBINARY(256) -- used for partial matches
);
GO
CREATE NONCLUSTERED INDEX idx_Sensitive_AccountNo1 ON HasSensitiveData (AccountHash) INCLUDE (EncryptedAccountNumber, Descr)
GO
CREATE NONCLUSTERED INDEX idx_Sensitive_AccountNo2 ON HasSensitiveData (PartialAccountHash) INCLUDE (EncryptedAccountNumber, Descr)
GO
-- now, let's add some data.
-- First, I need a salt.
OPEN SYMMETRIC KEY EncryptData DECRYPTION BY ASYMMETRIC KEY EncryptSymKey
INSERT INTO HashSalt (EncryptedSalt)
VALUES (EncryptByKey(Key_GUID('EncryptData'),'MyReallyReallyLongSaltForAHash2322'))
CLOSE SYMMETRIC KEY EncryptData
GO
SELECT * FROM HashSalt
GO
-- and now the account data
DECLARE @AccountNumber VARCHAR(20),
@Salt VARCHAR(256)
OPEN SYMMETRIC KEY EncryptData DECRYPTION BY ASYMMETRIC KEY EncryptSymKey
SELECT @Salt = DecryptByKey(EncryptedSalt) FROM HashSalt
SET @AccountNumber = '1234567890987654321'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'First Account',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
SET @AccountNumber = '47378383882922201'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'How did this get in here?',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
SET @AccountNumber = '23454434111111'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'Second Account',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
SET @AccountNumber = '34557684345634'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'Another Account',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
SET @AccountNumber = '788953442324323'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'Someone',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
SET @AccountNumber = '45667789877'
INSERT INTO HasSensitiveData (Descr, EncryptedAccountNumber, AccountHash, PartialAccountHash)
VALUES (
'Important',
EncryptByKey(Key_GUID('EncryptData'),@AccountNumber),
HashBytes('SHA1', @Salt + @AccountNumber), -- NB. Salting the hash. Don't do this and you're vulnerable to rainbow tables
HashBytes('SHA1', @Salt + LEFT(@AccountNumber,5))
)
-- .... I added 10000 records to test.
CLOSE SYMMETRIC KEY EncryptData
SELECT * FROM HasSensitiveData
GO
-- now, how are we going to search this?
-- Looking for account 788953442324323
DECLARE @Salt VARCHAR(256)
OPEN SYMMETRIC KEY EncryptData DECRYPTION BY ASYMMETRIC KEY EncryptSymKey
SELECT @Salt = DecryptByKey(EncryptedSalt) FROM HashSalt
-- Decrypt and compare is one way....
-- clustered index scan, 156 reads, 383ms
SELECT Descr, CAST(DecryptByKey(EncryptedAccountNumber) AS VARCHAR(256)) FROM HasSensitiveData WHERE DecryptByKey(EncryptedAccountNumber) = '788953442324323'
--now, let's try that hash column out.
-- Index seek, 5 logical reads, 6ms
SELECT Descr, CAST(DecryptByKey(EncryptedAccountNumber) AS VARCHAR(256)) FROM HasSensitiveData WHERE AccountHash = hashbytes('SHA1',@Salt + '788953442324323')
-- and what about a partial match. No problem.... Let's assume we've actually been passed 6 characters, not the 5 in the hash''
-- index seek and filter. 4 logical reads, 29ms
SELECT Descr, CAST(DecryptByKey(EncryptedAccountNumber) AS VARCHAR(256)) FROM HasSensitiveData
WHERE PartialAccountHash = hashbytes('SHA1',@Salt + LEFT('788953',5))
AND DecryptByKey(EncryptedAccountNumber) LIKE '788953%'
CLOSE SYMMETRIC KEY EncryptData -- All done....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply