DB Encryption

[mossbs]

Hi guys,

Although I've been working with SQL for a while, encryption is not something I've worked with before - I've done a bit of reading up on BOL etc but have some specific questions I'm still not sure of the answer to...

- Is it possible to encrypt data so that it is encrypted only on the SQL side? (ie reports and queries wouldn't display the sensitive data)
- But... in the app that sits on top of the DB - values are not encrypted.

From what I've read - this is possible but means alot of messing about and changes are required to the application?

What I would ideally like is some form of encryption that requires no alterations to the application.

Is this possible? or am I living in a dream world?!

Any help greatly appreciated!

 

[djj55]

Which version of SQL are you using? You may be able to use Transparent Data Encryption (TDE).

If you encrypt a column then decrypting will be needed.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLBill]

I don't do encryption...and would be curious to see the ultimate answer to this. But I see the issue as, you want the decryption done in SQL Server not the application. I would think if the decryption is done in SQL Server, you couldn't limit it to a specific application, so it would be more effective to have the decryption happen in the application.

If the application uses a specific login or can be identified someway in SQL Server you might be able to have a trigger or something that checks for that value and does the decryption. Maybe something like:

IF 
  BEGIN
    DECLARE @myPassphrase VARCHAR(10)
    SET @myPassphrase = 'OpenSesame'
    SELECT col1, col2, DecryptByPassphrase(@Passprase, col3, 0)
    FROM mytable
  END
ELSE
  SELECT col1, col2
  FROM mytable

You might have to pull the decrypted values into a temp table and then have that returned in the select. As I said, I don't work with encryption, but this is how I think it might have to work.

It would be a lot of work to set it up on the SQL Server side and I really think it would be easier to set the decryption in the application.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875