Encryption

  • 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

  • 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, MVP, M.Sc (Comp Sci)
    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
  • so how would you ensure PCI Compliance by protecting the clear text information being passed to server from application?

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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?

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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" 😉

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

  • 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" 😉

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply