Encryption and Decryption data From DB level in SQL

  • I have a requirement where two of the columns in the table have to been encrypted. The data comes from the application. Is it possible to do something at the database level rather than changing code in the application., ie., when the data is entered in to the table it has to be encrypted and when we fetch data from the application we have to get the decry pt data.. I don't know if it makes any sense ...

  • What version of SQL Server you are running on? If 2008 or above, you can simply turn on TDE at the database level if that's Okay with your Organization.(Yes, It will introduce little performance overhead, but might not be even noticable in many cases on normal work loads which I've seen personally)

  • We use 2012. I dont think my organization agrees to that, they only have a requirement to encrypt 2-3 columns of a table. I think TDE is for the data and log files.

  • typically what i've done in the past is to limit access to the table to require stored procedures.

    the stored procedures get signed, so they can be called by other users, and the procedures handle all the encryption required for the CRUD operations.

    you'll most likely replace the columns by adding a new column of datatype(varbinary), encrypting the current values into the new columns, confirm they unencrypt correctly, and later drop the original, unencrypted columns.

    here's the ms link for database encryption:

    http://technet.microsoft.com/en-us/library/cc278098(v=sql.100).aspx

    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!

  • tejaswi161 (12/10/2013)


    We use 2012. I dont think my organization agrees to that, they only have a requirement to encrypt 2-3 columns of a table. I think TDE is for the data and log files.

    As I did mentioned earlier, Yes, It is at Database level which encrypts everything(Including the backup of the TDE enabled Database)

  • TDE is for the entire database and requires Enterprise Edition.

    For SQL 2008, individual column encryption is possible using keys and certificates. I would start at http://technet.microsoft.com/en-us/library/bb510663.aspx. Make sure you understand and test it so you don't lose access to your own data. If I could offer a small piece of advice, it would be to back up your keys. Don't just use the BACKUP KEY commands, but also store the SQL you use to create your keys in the first place. Back it up and store it somewhere secure.

  • sreekanth bandarla (12/10/2013)


    What version of SQL Server you are running on? If 2008 or above, you can simply turn on TDE at the database level if that's Okay with your Organization.(Yes, It will introduce little performance overhead, but might not be even noticable in many cases on normal work loads which I've seen personally)

    I can't possibly go along with this idea. It introduces overhead, which must be taken into account. Also what edition of SQL he has is also an issue.

    We need a lot more information here. Such as what are we attempting to protect and from whom. TDE protects data at rest, while the server is running ANYONE with access to the table can read the data. This is usually NOT ok.. The requirement from the original post actually rules out TDE.

    There are several ways to do it.. What level of access does the application user have? how tight is the security at the DB level? You might encrypt and decrypt at the DB level but the app supplies the key. If everything is in sql, the data and all the keys and passwords then you have to tighten access to that process down as tight as possible, but even then I think sysadmin users could still access it..

    CEWII

  • How does the application calls these columns ? If it is calling through a SP then the changes can be done on dB side only else a ad-hoc quey calls it then need changes on the application side too I believe.

    Please read this

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • the zero-changes to the application thing is the tough part;

    i know it's possible to use certificates to allow a view to decrypt data, and then i assume you could create an INSTEAD OF trigger on the view to handle insert/update/delete so the specific column data can be encrypted ;

    but that requires a lot of changes...renaming the table, replacing it with a view, certificates, etc that would be pretty involved until it was set up; and barely recognizable security offset: the data could still be viewed in an un-encrypted format.

    but from there, we are back to the points Elliot made; if someone has access to the sql server, they would be able to see it anyway.

    usually, you modify the application, so, for example, only the people who are in some Accounting group can see credit card numbers un-encrypted; the rest of the time, it remains encrypted.

    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!

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

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