A Simple Approach to SQL Server 2005 Encryption
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mgood/3058.asp
-
Excellent article! While I haven't tested any of the scripts mentioned, it does provide a solid background to this subject (one that I didn't get the chance to experience yet...). I learned a lot.
-
Concept is good, I have tested its working.
Need to explore for implementation
-
Nice article. Especially like the tip about the cost of opening the symmetric key. Not something I'd thought of before.
-
Execllent article, and I like the mindset of trying to take a simple approach. Too many time complex approaches result in failed implementations or complications. Thanks.
-
Greate article on what I need to know as a DBA without getting bogged down in the details. This is a departure from most things that I have read about encryption! Thanks.
-
Clear, Simple and to the point.
Good Job!
Thanks,
* Noel -
Mike - great, I did a similar thing a few months back when one of our programmers needed to do an encrypted field for the first time (rot-13 really doesn't cut it) and it was great to see someone with a similar background go through the same exercise.
Off topic:
I notice you were a programmer who ended up doing full time Sybase/SQL Server DBA work.
This is my background as well, for the past 5 years I've been working on moving large Sybase DBs to SQL Server - which is a great deal of programming since there really aren't
commercial products to do this - which has caused me to assume that most Sybase db's are being left in place, even if the shop ends up doing all new work on SQL Server.
Roger L Reid
-
I'm really glad to see this is being received well. The code snippets got reformatted a little, so I apologize if hard to read; this is my first article & I have lot to learn about publishing in HTML.
We just reviewed this with our development team and they made a great suggestion which we've adopted as our standard approach, and that I wish I'd included in the original article: write custom "encrypt" and "decrpyt" UDFs for each encrypted column. These make subsequent coding much simpler, developers don't have to know names of the symmetric key or the certificate, don't have to worry about casting the datatypes, etc.
create function dbo.fnEncryptAccountNbr(@AccountNbr varchar(16))
returns varbinary(68)
as
begin
return EncryptByKey(Key_GUID('MyKey'), @AccountNbr);
end
go
create function dbo.fnDecryptAccountNbr(@EncryptedAccountNbr varbinary(68))
returns varchar(16)
as
begin
return convert(varchar(16), DecryptByKeyAutoCert(cert_id('MyCert'), null, @EncryptedAccountNbr));
end
go
I have only tested this a little bit, but so far don't see any performance hit caused by these UDFs (I've been badly burned by UDF performance--or lack thereof--in SQL2000, am always on guard for that now).
-
Forgot to include this: in addition to the UDFs, we've also decided to use a stored proc to wrap/hide the open symmetric key business.
create proc dbo.spOpenSymmKey
as
--open symm key if not already open
set nocount on
if not exists(select 1 from sys.openkeys where key_name = 'MySymmKey' and database_name = db_name())
open symmetric key MySymmKey decryption by certificate MyCert;
go
PS - Would have been cool to embed this functionality in the fnEncryptxxx UDF, but cannot because it causes a "side-effect" and SQL won't let you.
-
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.
- 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 = @ParamEncryptedAgain, thank you for your time in preparing this article. You have already saved me a ton of experimenting to see what might work.
-
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.
-
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
-
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 ?
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply