locked
SQL Server Transparent Data encryption RRS feed

  • Question

  • I have implemented TDE for the Database and Column Level Encryption for Sensitive data in Tables. But, the Porblem is the data is entered through an front end application how could i encrypt this data when it is inserted from the Front end. And how to decry-pt this data for the users when it is selected.

    Your suggestions are most valuable.

    Reagrds

    Rehaan Khan


    RehaanKhan. M

    Friday, August 8, 2014 9:35 AM

Answers

  • Let me start with a solution that may have been overlooked, but it is good to make sure we cover it. Have you considered using column-level permissions? It may not be a complete solution for your particular scenario if you need to give access to the column for other reasons (after all, the group you are trying to restrict is probably developing applications on top of the column storing sensitive data) or if the developer group has permission to create objects that would render the sensitive data subject to ownership chains. For more information on column-permissions look at http://msdn.microsoft.com/en-us/library/ms186915.aspx

    Assuming permissions alone will not solve the problem. By using encryption you should be able to limit access to the sensitive data to the developers, but it will also require some changes to your schema & application. TDE (Transparent Data Encryption) will not help you in this scenario since you need to restrict access to the data and restricting access to the column is not sufficient.

    The following links may be useful to get you started with SQL Encryption capabilities:

    Older articles, but they may still be quite useful:

    One recommendation may be to encrypt the data using an AES key, and protect the key using one or more certificates (I would recommend using a separate certificate per individual if possible), making sure that only authorized people have access to the keys. Anyone else with access to the column, but not to the keys would not be able to decrypt the data.

    BTW. I would also recommend using SQL Auditing (http://msdn.microsoft.com/en-us/library/cc280386.aspx) in order to keep honest people honest, by monitoring access to the keys & to the sensitive data.

    I hope this information helps,

    -Raul Garcia

    SQL Server Security

    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Sofiya Li Friday, August 15, 2014 7:16 AM
    Monday, August 11, 2014 5:28 PM
  • The problem with column-level encryption is that someone has to implement it. And that is typically the developers - who are not permitted to see the data.

    I think you need a test environment, and keep the developers of the the production box. In the test environment restore a copy of the production database, and add on a routine which garbles the sensitive data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Friday, August 15, 2014 7:16 AM
    Monday, August 11, 2014 9:28 PM

All replies

  • If you want your data to encrypted from front end and automatically decrepted when the user access it from front end then the only option is to have encryption logic inbuilt in the application front end.

    It depends on the type of application. Do you have a client server application? if yes then the encryption/decryption process should happen in the client. There are also third party tools which does it. In that way any data that is transmitted from client and the transient data in network and the data in database is encrypted.

    TDE is more of an encryption for data file/ backups etc so that they cannot be used any where else. Its kind of an encryption in such a way that data cannot be misused even if anyone has access to files/backups.

    It doesnt really give much protection because if ppl outside your company can enter the network and grab your files/backups then they would be good enough to access SQL/Application directly to grab it.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, August 8, 2014 10:04 AM
  • TDE will protect data as it rests in files but not as it is transferred over the network.  You can use SQL Server protocol encryption to encrypt data as it's transferred over the wire to the immediate client.  Also consider https to protect data as it is transferred between web browsers, web servers, and mid-tier services.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 8, 2014 11:03 AM
  • I would like to ask for a clarification on your question:

    Are you asking for guidelines on how to use the SQL Server encryption APIs (i.e. EncryptByKey, etc.)? Or is your intent to handle the encryption as a whole (key management, encryption, etc.) on the application, and simply use SQL as a repository for the opaque blob?

    If it is the later, I would also be very interested in understanding your scenario a bit more so we can try to provide better assistance.

    Thanks,

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, August 8, 2014 6:04 PM
  • I would like to ask for a clarification on your question:

    Are you asking for guidelines on how to use the SQL Server encryption APIs (i.e. EncryptByKey, etc.)? Or is your intent to handle the encryption as a whole (key management, encryption, etc.) on the application, and simply use SQL as a repository for the opaque blob?

    If it is the later, I would also be very interested in understanding your scenario a bit more so we can try to provide better assistance.

    Thanks,

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Hi gracia,

    Actually for our software development, Our team is using Alpha5 and backend as SQL Server2012. but we need a scenario that a particular data fields has to be hidden from Developer also and only management can able to view the data. By using Access Permissions right i have given management grid to view the information and Users grid to Hide the information. But the developer in SQL Server also has to be restricted from viewing the information. How could i achieve this using SQL server. As alpha5 is an Limited code Supported and it Uses Xbasic and JavaScript. I don't want to Implement Application Level Encryption as it is front end developers work. I just want to make it through SQL Server any possibilities. 

    Thank you.


    RehaanKhan. M

    Monday, August 11, 2014 6:27 AM
  • Let me start with a solution that may have been overlooked, but it is good to make sure we cover it. Have you considered using column-level permissions? It may not be a complete solution for your particular scenario if you need to give access to the column for other reasons (after all, the group you are trying to restrict is probably developing applications on top of the column storing sensitive data) or if the developer group has permission to create objects that would render the sensitive data subject to ownership chains. For more information on column-permissions look at http://msdn.microsoft.com/en-us/library/ms186915.aspx

    Assuming permissions alone will not solve the problem. By using encryption you should be able to limit access to the sensitive data to the developers, but it will also require some changes to your schema & application. TDE (Transparent Data Encryption) will not help you in this scenario since you need to restrict access to the data and restricting access to the column is not sufficient.

    The following links may be useful to get you started with SQL Encryption capabilities:

    Older articles, but they may still be quite useful:

    One recommendation may be to encrypt the data using an AES key, and protect the key using one or more certificates (I would recommend using a separate certificate per individual if possible), making sure that only authorized people have access to the keys. Anyone else with access to the column, but not to the keys would not be able to decrypt the data.

    BTW. I would also recommend using SQL Auditing (http://msdn.microsoft.com/en-us/library/cc280386.aspx) in order to keep honest people honest, by monitoring access to the keys & to the sensitive data.

    I hope this information helps,

    -Raul Garcia

    SQL Server Security

    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by Sofiya Li Friday, August 15, 2014 7:16 AM
    Monday, August 11, 2014 5:28 PM
  • The problem with column-level encryption is that someone has to implement it. And that is typically the developers - who are not permitted to see the data.

    I think you need a test environment, and keep the developers of the the production box. In the test environment restore a copy of the production database, and add on a routine which garbles the sensitive data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Friday, August 15, 2014 7:16 AM
    Monday, August 11, 2014 9:28 PM