Post reply

Need to Decrypt an Encrypted column that is selected in a view

  • April 29, 2013 at 12:50 pm

    #273381

    Hi,

    I have a document imaging application that connects to my database through a view and it can only use the view. One of the table fields selected in the view has now been encrypted for PCI and HIPAA compliance. I need to be able to decrypt this field for the view but due to MS design, the view does not let me open the key. I've also read where it is not recommended to use the openrowset to execute a stored procedure in the view. Does anyone have a suggestion to accomplish this task? Thanks in advance.

  • April 29, 2013 at 12:57 pm

    #1610966

    creating a view that auto-decrypts the data makes the data no longer HIPAA compliant, doesn't it?

    I'd say that is no different from storing the data unencrypted in the first place.

    you should either make the view redact the information altogether, maybe with a placeholder "[Removed per HIPAA Compliance]" or actually change the application to correctly open the keys so you can present the encrypted data.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 29, 2013 at 1:04 pm

    #1610972

    Lowell,

    I have no control over that application so I can't change anything about the app. I did not provide 100% accurate information I now realize. The field is SSN and it only returns the last 4 digits which is necessary for document processing and is still in compliance.

    However, that did trigger a thought of how to accomplish that! Thanks.

  • April 29, 2013 at 1:28 pm

    #1610982

    Lowell (4/29/2013)

    creating a view that auto-decrypts the data makes the data no longer HIPAA compliant, doesn't it?

    I'd say that is no different from storing the data unencrypted in the first place.

    you should either make the view redact the information altogether, maybe with a placeholder "[Removed per HIPAA Compliance]" or actually change the application to correctly open the keys so you can present the encrypted data.

    Actually, there is a way to have this work auto-magically based on user permissions. I have played with it a bit based on an article that I cannot find at the moment (not sure if it was on SSC or Simple Talk).

Viewing 4 posts - 1 through 4 (of 4 total)

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