SQL Clone
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
nagendrareddy.kolli
nagendrareddy.kolli
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

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

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Half Bubble
Half Bubble
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

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

peleg
peleg
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 537
thnaks for the DecryptByKeyAutoCert !
spraocs
spraocs
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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
Mike Good
Mike Good
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

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



Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

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

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
spraocs
spraocs
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
spraocs
spraocs
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 11
Marios, Thanks for the information. I would definitely consider using the Certificates.
spraocs
spraocs
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 11
I have another question.. Is it possible to use the ENCRYPTION BY PASSWORD temporarily and later rerplace it with the certificate?
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