locked
Encryption of credit card information RRS feed

  • Question

  • I'm working on a project where credit card details will be captured in Database 1, periodically this data will then be transferred to a table in Database 2.
    I would like to make use of SQL Server 2005 encryption functionality to control this though am getting buried in certificates / symmetric keys / asymmetric keys.

    I understand that first I must go through the process of :
     (a). Set up a database master key in Database 2.
     (b). Set up an asymmetric key in Database 2.
     (c). Supply the public key from the asymmetric key to the 3rd party and the algorithm used to perform the encryption.
     (d). The 3rd party will capture and encrypt the credit card in Database 1 using the public key.
     (e). Periodically we will extract the data from this database and insert the data into a staging table in Database 2.
     (f). Once in Database 2 we will use the asymmetric key to decrypt the credit card.

    My questions are :

     (a). Is the process I have outlined above the best solution ?
            - I'm worried about the performance of using asymmetric keys.
            - Should I use a symmetric key encrypted by asymmetric key ?

     (b). How do I give the 3rd party the public key ?
            - Do I just give them the public key value from sys.asymmetric_keys ?
            - Can I do the same if I use a symmetric key ?

     

     

     

    Sunday, September 19, 2010 7:46 AM

Answers

  • Hi 10_4_Big_Buddy,

     

    (c). Supply the public key from the asymmetric key to the 3rd party and the algorithm used to perform the encryption.

    Did you mean you want to export the Asymmetric Key in SQL Server?

    As far as I know, there is no official method to do such thing. In other words, the Asymmetric Key in SQL Server cannot be exported and imported.

    I would like to recommend you to encrypt the encryption algorithm manually in your program. And please use your customized code to decrypt data too. It means the database will only be used to store the encrypted data.

     

    Meanwhile if you think the import and export of Asymmetric Key is useful, I would like to recommend that you provide Microsoft your feedback at http://connect.microsoft.com/SQLServer so that our product team can hear your voice directly and continuously improve our products in future.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Monday, September 27, 2010 11:08 AM
    Thursday, September 23, 2010 2:56 AM

All replies

  • To answer (b), you should consider performing all the encryption/decryption via stored procedures and granting EXEC on the stored procedure to the 3rd party. That why you don't need to manage any keys with a 3rd party no grant them direct access to any tables.
    Wednesday, September 22, 2010 12:18 AM
  • Hi 10_4_Big_Buddy,

     

    (c). Supply the public key from the asymmetric key to the 3rd party and the algorithm used to perform the encryption.

    Did you mean you want to export the Asymmetric Key in SQL Server?

    As far as I know, there is no official method to do such thing. In other words, the Asymmetric Key in SQL Server cannot be exported and imported.

    I would like to recommend you to encrypt the encryption algorithm manually in your program. And please use your customized code to decrypt data too. It means the database will only be used to store the encrypted data.

     

    Meanwhile if you think the import and export of Asymmetric Key is useful, I would like to recommend that you provide Microsoft your feedback at http://connect.microsoft.com/SQLServer so that our product team can hear your voice directly and continuously improve our products in future.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Monday, September 27, 2010 11:08 AM
    Thursday, September 23, 2010 2:56 AM