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

Encryption Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 5:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:36 AM
Points: 33, Visits: 239
Hi,
I have set up encryption within a 2005 database for the purposes of protecting credit card information in a single column with a table.
There is an application sitting on a separate machine which accepts user input and then calls a stored procedure in the SQL database to encrypt the data.
My question, and it may sound simple/obvious, is this:
Will the credit card number be transmitted in clear text to the database at which point the application will encrypt it, or is the data encrypted within the application and then transmitted to the database encrypted?
It is quite a subtle point but very important from a PCI-Compliance standpoint.
Many thanks.
Dunc
Post #1536266
Posted Thursday, January 30, 2014 5:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
DuncEduardo (1/30/2014)
Will the credit card number be transmitted in clear text to the database at which point the application will encrypt it, or is the data encrypted within the application and then transmitted to the database encrypted?


A stored procedure executes on the database server, not on the client.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1536269
Posted Thursday, January 30, 2014 5:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:36 AM
Points: 33, Visits: 239
so how would you ensure PCI Compliance by protecting the clear text information being passed to server from application?
Post #1536272
Posted Thursday, January 30, 2014 5:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Encrypt in the application, not the database. It also prevents any sysadmin from just decrypting it, which they can do if you use keys protected by database permissions. Of course, key management becomes a hell of a lot harder if you're implementing encryption in the app not the database.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1536275
Posted Thursday, January 30, 2014 5:47 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:36 AM
Points: 33, Visits: 239
Yes I thought so and I understand the decryption issue. With regards to encrypting in application do you mean that a similar methodology to that in SQL Server could be employed, or would "bespoke" code have to be written from scratch?
By that I mean, can the database encryption process be applied at the application level by some means?
Post #1536276
Posted Thursday, January 30, 2014 6:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
DuncEduardo (1/30/2014)
With regards to encrypting in application do you mean that a similar methodology to that in SQL Server could be employed, or would "bespoke" code have to be written from scratch?


Am not a front end dev

By that I mean, can the database encryption process be applied at the application level by some means?

No.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1536283
Posted Friday, February 7, 2014 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 6,745, Visits: 14,384
DuncEduardo (1/30/2014)
so how would you ensure PCI Compliance by protecting the clear text information being passed to server from application?

Secure and encrypt channels between the client and server. IPSec, when enabled, will secure communications between the SQL server instance and the client. You may also use SSL to encrypt connections to SQL Server.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1539066
Posted Friday, February 7, 2014 4:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:36 AM
Points: 33, Visits: 239
Thanks Perry - I'm personally trying to push for this approach.
Net Admin initially said that these methods can be resource-heavy and the preference is Tokenisation. However, having examined the server setups, he is starting to realise that the weight/bandwidth problems are less significant that he had assumed.
Post #1539081
Posted Friday, February 7, 2014 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 6,745, Visits: 14,384
DuncEduardo (2/7/2014)
Thanks Perry - I'm personally trying to push for this approach.
Net Admin initially said that these methods can be resource-heavy and the preference is Tokenisation. However, having examined the server setups, he is starting to realise that the weight/bandwidth problems are less significant that he had assumed.

Rubbish, IPSec is extremely secure and lightweight


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1539106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse