locked
Table Level Encryption in SQL Server 2014 RRS feed

  • Question

  • Hi,

         I am currently working on Project in Bank. There is an issue regarding database encryption of SQL Server 2014. We need to encrypt particular column data like Credit_Card_number, PIN etc. so Database Administrator or other user (who have rights to view data) can not read data in text form and easily read by application.

         While searching this issue i found this link 

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

         From above link the problem is that column data is encrypting easily but decrypt by function "CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))". Then we would have to change the code of application for implement this type of encryption. 

         Is there any way for Transparent Encryption in SQL Server that can applies on Database Server Level and no any big change in application code.

    Tuesday, April 17, 2018 8:18 AM

Answers

  •  

         Is there any way for Transparent Encryption in SQL Server that can applies on Database Server Level and no any big change in application code.

    TDE is not a solution for you as TDE only encrypts data at rest and does for complete database and DBA can still see the column which you "think" is encrypted.

    The solution is column level encryption or Always Encrypted which is introduced from SQL Server 2016. But unfortunately I have little experience with both. AFAIK there is no code change required for Always Encrypted.

    See this Similar Thread


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by TAP Ammar Tuesday, April 17, 2018 9:52 AM
    Tuesday, April 17, 2018 9:06 AM

All replies

  • Transparent Data Encryption (TDE) is available in SQLServer

    see

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


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 17, 2018 8:39 AM
  •  

         Is there any way for Transparent Encryption in SQL Server that can applies on Database Server Level and no any big change in application code.

    TDE is not a solution for you as TDE only encrypts data at rest and does for complete database and DBA can still see the column which you "think" is encrypted.

    The solution is column level encryption or Always Encrypted which is introduced from SQL Server 2016. But unfortunately I have little experience with both. AFAIK there is no code change required for Always Encrypted.

    See this Similar Thread


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by TAP Ammar Tuesday, April 17, 2018 9:52 AM
    Tuesday, April 17, 2018 9:06 AM
  • So you want to eat your cake and have it?

    In SQL 2014, cell-level encryption is your only option. TDE might be good for checkboxes, but the data is visible for any one who can access the database, and that includes the DBA.

    Cell-level encryption is not really that much better, since the keys are protected by other keys and they are all on the server, and if the DBA wants to get there, he can. But at least he will not see numbers by mistake. And, yes, it will requires you to change application code.

    In SQL 2016 there is Always Encrypted, and this is better, since encryption here takes place on the client side. This means that the keys can be kept away from the DBA. This will require less changes to the application, because encryption/decryption happens transparently in the client API. However, the table needs to be rebuilt and reloaded, as the encrypted value are represented different from the cleartext values. The table definition also needs to be changed.

    Tuesday, April 17, 2018 9:46 PM