locked
SQL Server TDE (Transparent Data Encryption) RRS feed

  • Question

  • Hi,

    My company wants to encrypt some of the tables of database. Our requirement is to do storage level encryption. I searched the net and found TDE (Transparent Data Encryption) is the option for storage level encryption. But if we enable TDE then the whole database will be encrypted. I want to know-

    1) Is it possible to encrypt some tables (not full database) by TDE?

    2) Is there any other security feature that encrypt table at storage level?

    Please note that we need encryption at storage level, that means if storage (hard disk/SAN disk) is stolen then the hacker/thief will not able to retrieve the encrypted information. There are some table encryption feature like DDM (Dynamic Data Masking) which encrypt data but not at storage level.

    Regards

    Arif

    Saturday, September 28, 2019 9:00 AM

Answers

  • Hi Arif,

    >>Is it possible to encrypt some tables (not full database) by TDE?

    TDE encrypts data files, so I’m afraid you cannot encrypt somes tables by TDE.

    For more information, please refer to TDE.

    >> Is there any other security feature that encrypt table at storage level?

    You could implement column level encryption by using symmetric encryption.

    I suggest you refer to this similar thread.

    Hope this could help you.

    Best Regards,

    Amelia Gu



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by arifulhaq Monday, September 30, 2019 8:14 PM
    Monday, September 30, 2019 5:56 AM

All replies

  • Hi 

    In SQL Server, as per current functionality TDE can be applied at a Database Level and not at a Table level.

    In TDE, all files and file groups in the database are encrypted.

    But, as per your requirement - I can say Column Level Encryption - Always Encrypted will suit you, Please go through below link for more detail.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, September 28, 2019 9:15 AM
  • I suggest you consider TDE for you storage-level encryption requirement. Although it will encrypt data at the file level, it is efficient and easy to manage without requiring application changes.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, September 28, 2019 9:54 AM
  • Arif,

    DDM is *not* encryption, it is applying a masking function just prior to the result set being returned to the client. 

    For storage level encryption, your options are BitLocker and/or TDE, depending on your requirements. 

    There is no table encryption feature, but it could be possible to use AlwaysEncrypted, it just depends on the current schema of that table. 

    HTH

    Saturday, September 28, 2019 4:58 PM
  • Hi Arif,

    >>Is it possible to encrypt some tables (not full database) by TDE?

    TDE encrypts data files, so I’m afraid you cannot encrypt somes tables by TDE.

    For more information, please refer to TDE.

    >> Is there any other security feature that encrypt table at storage level?

    You could implement column level encryption by using symmetric encryption.

    I suggest you refer to this similar thread.

    Hope this could help you.

    Best Regards,

    Amelia Gu



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by arifulhaq Monday, September 30, 2019 8:14 PM
    Monday, September 30, 2019 5:56 AM
  • Hi Arif,

    >>Is it possible to encrypt some tables (not full database) by TDE?

    TDE encrypts data files, so I’m afraid you cannot encrypt somes tables by TDE.

    For more information, please refer to TDE.

    >> Is there any other security feature that encrypt table at storage level?

    You could implement column level encryption by using symmetric encryption.

    I suggest you refer to this similar thread.

    Hope this could help you.

    Best Regards,

    Amelia Gu



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thanks Amelia

    Monday, September 30, 2019 8:15 PM
  • Please note that we need encryption at storage level, that means if storage (hard disk/SAN disk) is stolen then the hacker/thief will not able to retrieve the encrypted information.

    TDE protects you from the SAN administrator stealing the database file and bringing it outside the organisation. And for that matter, if a thief manages steal disks from the SAN.

    However, an intruder that also has access to the operating-sytsem disk will not be stopped by TDE, since the entire key hieararchy for TDE is available in the OS. (And it has to be, since else it cannot decrypt the file.)

    Always Encrypted is a safer option, but it is also more work to implement.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 30, 2019 9:32 PM