Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A Simple Approach to SQL Server 2005 Encryption Expand / Collapse
Author
Message
Posted Thursday, August 2, 2007 12:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:36 PM
Points: 55, Visits: 158

Mike - Awesome job.

I am very impressed. I have not explored SQL 2005 encryption significantly myself yet, but have scanned a few other articles and gave up on the complexity and lack of simple solutions. Yours reads very logically and was easy to follow new terms and concepts without having to have BOL open to clarify obscure references. Yeah!

Again, I don't know enough to poke any holes, but knowing your thorough approach to new ideas, I bet you cover 99% or better of the solution. We have a few sensitive storage requirements ourselves coming up in a new project. Maybe we can experiment with this in our new solutions, I'll let you know how it goes.

 




Post #387533
Posted Thursday, August 2, 2007 8:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 9:49 AM
Points: 3, Visits: 92
First, let me say "Thank You!" for your timely article, which is just in time to help me with my next project.  I do have one thought about your solution to searching on the value in an encrypted field.  You state the issue as "We can't simply add a DecryptByKeyAutoCert(...) = @param clause to our WHERE clause and expect decent performance".  This would certainly cause every record to be scanned and the field to be decrypted to determine if there is a match. 
 
However, since the result of encrypting a specific phrase or sequence of characters will always return the exact same encrypted value, you reverse the issue:  encrypt the @param, which will yield a value that you can directly compare against the stored values in the table.  This only works when the WHERE clause is "=" on "IN (@ParamEncrypted1, @p=ParamEncrypted2,...) , and would not work for any other comparison, such as "<", ">", or in a "LIKE " clause.  However, for fields like SSN or Credit Card numbers, this would be perfectly acceptable.
 
I admit that I have not tried this, but I don't know of any reason it wouldn't work.  For example, assuming @param is declared as varchar(16):
 
--symmetric key approach; need to open the key once per session
open symmetric key MyKey decryption by certificate MyCert;
DECLARE @ParamEncrypted varbinary(68);
SET @ParamEncrypted = EncryptByKey(Key_GUID('MyKey'), @param)
select * from MyTable where EncryptedText = @ParamEncrypted
 
Again, thank you for your time in preparing this article.  You have already saved me a ton of experimenting to see what might work. 
 
Post #387622
Posted Thursday, August 2, 2007 10:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:17 PM
Points: 316, Visits: 810

Geno, thanks for your kind words.  To your point, it might be nice if it worked that way, but in fact SQL does not encrypt same sequence of characters to the same encrypted value.  Use your code to check this for yourself:

open symmetric key MyKey decryption by certificate MyCert;
DECLARE @ParamEncrypted varbinary(68), @param varchar(16)
set @param = 'abc'

SET @ParamEncrypted = EncryptByKey(Key_GUID('MyKey'), @param)
select @ParamEncrypted

SET @ParamEncrypted = EncryptByKey(Key_GUID('MyKey'), @param)
select @ParamEncrypted

You get two different values. 




Post #387628
Posted Tuesday, August 7, 2007 11:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:17 PM
Points: 316, Visits: 810

Not sure where else to document this, this clearly not best place.  We've done some perf testing since this article was written, and have discovered there's a decent performance hit whenever the "open symmetric key" statement is included in a stored proc that does anything else. 

Sounds like a recompile problem, but when I capture a trace there are no recompiles.  What I do see are CacheMiss events, which I believe means the stored proc needs to be recompiled, so this is kind of like a recompile problem?  Not sure, I've been away from tuning for too long!

Put "open symmetric key" in its own 1-line stored proc, and the CacheMiss events change to CacheHit events, and timing improves dramatically.

Here are some examples of poor-peforming procs:

--this proc incurs cost of open key & compile every time, is worst performer
create proc dbo.TestProc1
as
open symmetric key ...
insert  ....
go
--this proc incurs cost of open key only once, but compiles every time
create proc dbo.TestProc1
as
if not exists(select 1 from sys.openkeys where key_name ...)
        open symmetric key ...
insert  ....
go
--this proc calls helper proc to conditionally open key
--it incurs cost of open key only once, but compiles helper proc every time
create proc dbo.TestProc2
as
exec dbo.TestProc2Helper;
insert  ....
go
create proc dbo.TestProc2Helper
as
if not exists(select 1 from sys.openkeys where key_name ...)
        open symmetric key ...
go

The approaches above all incur extra cost of CacheMiss & resulting compilation.  Here are two approaches that do not incur this cost, and perform noticeably better.  You have to look close, there's not much difference between these and the poor performers above--the key is putting the "open symmetric key" statement in its own proc.

--this proc template offers optimal performance
create proc dbo.TestProc3
as
if not exists(select 1 from sys.openkeys where key_name ...)
        exec dbo.TestProc3Helper
insert  ....
go
create proc dbo.TestProc3Helper
as
open symmetric key ...
go
--this proc template just about as good as TestProc3, and is easier to use
create proc dbo.TestProc4
as
exec dbo.TestProc4Helper
insert  ....
go
create proc dbo.TestProc4Helper
as
if not exists(select 1 from sys.openkeys where key_name ...)
        exec dbo.TestProc4Helper2
go
create proc dbo.TestProc4Helper2
as
open symmetric key ...
go



Post #388674
Posted Wednesday, August 8, 2007 7:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 9, 2008 6:24 PM
Points: 1, Visits: 6
hi, first of all thanks for nice article.
i have some questions here, if i'm using EncryptByKey/DecryptByKey, it's only usefull if my scheme of application using more than 1 type of user connection to db, one for encryption , others for decryption. if my scheme of application only have 1 user connection to db, using this way just too overkill , CMIIW. i think if my scheme of application only have 1 user connection to db, i only need use EncryptByPassPhrase/DecryptByPassPhrase would be enough secure for my application. does anyone have any suggestion ?
Post #389109
Posted Wednesday, August 15, 2007 1:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:17 PM
Points: 316, Visits: 810

The passphrase approach is simple enough for the SQL stmts, and it might even peform better (I haven't measured, but for sure you don't have the overhead of opening a key).  The only problem is handling the passphrase.  Where are you going to store it? 

If you're encrypting data to achieve regulatory compliance or some other serious purpose, then you probably won't want to hard-code the passphrase within stored procs or application source code.  And you won't want to store it in a config file or the registry as plaintext, it would have to be encrypted....  The approach published here just basically solves this problem, you don't have to worry about protecting the passphrase. 

If you can get away with hard-coding the passphrase in your code, then that's the easiest way to go. 




Post #391055
Posted Thursday, August 16, 2007 2:54 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, Visits: 76
Thanks Mike. A nice article that even a complete newbie to encryption can follow and understand! Great work.
Post #391453
Posted Friday, August 17, 2007 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 24, 2014 12:52 PM
Points: 6, Visits: 47
I am in 100% agreement. The sql documentation on encryption had my brain hurting. But I must add that SQL books online is an excellant tool. Mike, your suggestions were refreshing and easy to understand. I have also been writing sql for almost 12 years, starting with 6.5. The importance of security today is too import to pass on. My job is to pass data back and forth to sql from web pages and other applications. I'm not a DBA but as Mike will probably agree, you can't program around SQL for long without much of the knowledge. Thanks Mike
Post #391746
Posted Thursday, September 6, 2007 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 3:25 PM
Points: 1, Visits: 10
Mike, this article was really helpful. You covered a couple things here that I just didn't find in a lot of other reading. In particular the brief discussion about how expensive it can be to open symmetric keys was helpful. Mostly though, I am grateful for the piece you put in about searching on encrypted values. I have an app where I need to search a table full of 32,000+ rows for a specific SSN value that is encrypted. It was taking a ridiculous amount of time to return my results so I think when I implement your solution using checksum, it will help a ton!
Post #397125
Posted Friday, November 9, 2007 12:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, Visits: 831
I have some questions regarding implementing encryption at database level:

Our security people wants to change keys every 90 days. Do I need to
1. change the pwd at service master key?
2. decrypt and re-encrypt data?

Thanks,
Post #420661
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse