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, June 28, 2007 12:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 322, Visits: 827
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mgood/3058.asp


Post #377517
Posted Wednesday, August 1, 2007 9:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 12, 2012 1:38 AM
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.


Post #387236
Posted Thursday, August 2, 2007 12:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:41 AM
Points: 77, Visits: 20

Concept is good, I have tested its working. 

Need to explore for implementation

Post #387256
Posted Thursday, August 2, 2007 12:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:21 AM
Points: 1,588, Visits: 387
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
Post #387257
Posted Thursday, August 2, 2007 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2007 1:32 PM
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.


Post #387359
Posted Thursday, August 2, 2007 7:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 2:04 AM
Points: 263, Visits: 324
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.
Post #387367
Posted Thursday, August 2, 2007 8:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Clear, Simple and to the point.

Good Job!

Thanks,




* Noel
Post #387408
Posted Thursday, August 2, 2007 8:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:32 AM
Points: 108, 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
Post #387420
Posted Thursday, August 2, 2007 10:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 322, Visits: 827

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




Post #387489
Posted Thursday, August 2, 2007 10:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 322, Visits: 827

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. 




Post #387491
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse