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 «««34567»»

A Simple Approach to SQL Server 2005 Encryption Expand / Collapse
Author
Message
Posted Sunday, May 24, 2009 10:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 09, 2010 7:49 AM
Points: 7, Visits: 56
Hi,

Can some one send me the article which was talking about in the below.

"Found this article useful as a newbie to encryption methods. A dept in our company has asked for an application to store sensitive information in a SQL2005 database, however they do not even want myself (DBA) and developers to see the encrypted data. From what I can gather one certificate and key can be created and used in a stored procedure to save and encrypt the data( developers would know) and another to decrypt the data. But I can see a way of how myself with the sysadmin access cannot easily decrypt the data as I will know the cert and keys. I wonder if anyone could clarify if my thinking is correct or if there is any option?"

Thanks in advance..
Nagendra
Post #722839
Posted Wednesday, May 27, 2009 6:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
nagendrareddy.kolli (5/24/2009)
Hi,

Can some one send me the article which was talking about in the below.

"Found this article useful as a newbie to encryption methods. A dept in our company has asked for an application to store sensitive information in a SQL2005 database, however they do not even want myself (DBA) and developers to see the encrypted data. From what I can gather one certificate and key can be created and used in a stored procedure to save and encrypt the data( developers would know) and another to decrypt the data. But I can see a way of how myself with the sysadmin access cannot easily decrypt the data as I will know the cert and keys. I wonder if anyone could clarify if my thinking is correct or if there is any option?"

Thanks in advance..
Nagendra


I don't think it's possible using SQL Server's native encryption toolset. By design, the DBA as sysadmin has ultimate access on the data whether encrypted or not. Ultimately, the DBA's job is to safeguard data access. If they are not trusted with that key, then I'm not sure who would...

I have had to grapple with this issue myself recently, but I haven't been able to find a way around it.

Also, let's not forget that one of the DBA's duties is data recovery in case of system failure. If DBAs are kept out of the loop of how to decrypt sensitive information, it will ultimately be their employer who will suffer (potentially catastrophic) loss of data.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #724722
Posted Friday, January 15, 2010 1:09 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 06, 2012 7:03 AM
Points: 78, Visits: 168
Not sure you've gained anything.

select ... convert(varchar(16), DecryptByKeyAutoCert(cert_id('MyCert'),

But now, isn't the key actually "MyCert"? If somebody were clandestinely querying your database, all they'd need to figure out is "MyCert" which is visible in the stored procedure.


Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.
Post #848531
Posted Sunday, January 31, 2010 4:44 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:46 AM
Points: 123, Visits: 513
thnaks for the DecryptByKeyAutoCert !
Post #856792
Posted Tuesday, October 26, 2010 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 7:39 PM
Points: 5, Visits: 11
Hi Mike,

Thanks for an excellent article.. I am a developer and not a DBA. However, I cannot implement encryption without knowing the details in-depth about the same. Your article helped immensely in that. I have a few questions here - why do I need to create a certificate to create a symmetric key? Can I just use ENCRYPTION BY PASSWORD to create a Symmetric key? Also, I don't have a master key. Is it necessary to create one?

I searched online and just used Created the Symmetric Key and used the option ENCRYPTION BY PASSWORD for that and was able to encrypt and decrypt the data. That seemed very simple to me, but I am not sure if I am missing anything here in this approach. I am a novice in encryption.. Our client demanded encryption so, I am diving into it without knowing anything.. deadlines are always tight.. I need it yeterday - kind of..
Thanks in advance for the help

Post #1011052
Posted Tuesday, October 26, 2010 12:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:31 AM
Points: 275, Visits: 739
We used certs because we did not want to have to worry about managing passwords in source code, or in config files, etc. By using certs, our development team did not have to concern themselves with this stuff, and I think it made it easier for us to address the compliance auditors.

If you don't mind managing the passwords, then I believe what you've described is all there is to it.

PS - This is an older article, relevant only to SQL 2005. I believe this has changed quite a bit (for the better) in SQL 2008.



Post #1011064
Posted Tuesday, October 26, 2010 12:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
spraocs (10/26/2010)
Hi Mike,

Thanks for an excellent article.. I am a developer and not a DBA. However, I cannot implement encryption without knowing the details in-depth about the same. Your article helped immensely in that. I have a few questions here - why do I need to create a certificate to create a symmetric key? Can I just use ENCRYPTION BY PASSWORD to create a Symmetric key? Also, I don't have a master key. Is it necessary to create one?

I searched online and just used Created the Symmetric Key and used the option ENCRYPTION BY PASSWORD for that and was able to encrypt and decrypt the data. That seemed very simple to me, but I am not sure if I am missing anything here in this approach. I am a novice in encryption.. Our client demanded encryption so, I am diving into it without knowing anything.. deadlines are always tight.. I need it yeterday - kind of..
Thanks in advance for the help



If you are simply using a symmetric key with a password, you need to declare the password every time in your code you need to open the symmetric key to encrypt/decrypt data. This password is then visible to anyone with access to that code.

Symmetric keys are not as secure as certificates/asymmetric keys; they are fast for encrypting/decrypting data but an extra layer is required to protect them.

I would use a certificate to protect the symmetric key; certificates can be backed up independently, which provides another layer of safety in case either the certificate or symmetric key gets corrupted.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1011067
Posted Tuesday, October 26, 2010 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 7:39 PM
Points: 5, Visits: 11
Thanks a lot! Yes, I need to install the DB on SQL Server 2008 at the client's place. But I have only 2005 on my system to work on.. I will need to see how this is done in SQL Server 2008.
Post #1011142
Posted Tuesday, October 26, 2010 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 7:39 PM
Points: 5, Visits: 11
Marios, Thanks for the information. I would definitely consider using the Certificates.
Post #1011144
Posted Tuesday, October 26, 2010 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 7:39 PM
Points: 5, Visits: 11
I have another question.. Is it possible to use the ENCRYPTION BY PASSWORD temporarily and later rerplace it with the certificate?
Post #1011151
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse