Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple Approach to SQL Server 2005 Encryption


A Simple Approach to SQL Server 2005 Encryption

Author
Message
Mike Good
Mike Good
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1020
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mgood/3058.asp



Yaniv Mor
Yaniv Mor
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 493
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.



charu-374169
charu-374169
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 20

Concept is good, I have tested its working.

Need to explore for implementation


DanKennedy
DanKennedy
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 389
Nice article. Especially like the tip about the cost of opening the symmetric key. Not something I'd thought of before.



Dan
www.firstcs.co.uk
divadrr
divadrr
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
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.



cjeremy74
cjeremy74
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 346
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.
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
Clear, Simple and to the point.

Good Job!

Thanks,


* Noel
Roger L Reid
Roger L Reid
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 166
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
Mike Good
Mike Good
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1020

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).





Mike Good
Mike Good
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1020

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search