none
SQL server built in encryption is not real encryption rather obfuscation- Is there a real encryption available in SQL

    Question

  • We are using a third party tool for database column value encryption. This tool requires encryption keys to be stored in database itself. To achieve this, we created a store procedure using WITH ENCRYPTION and kept the encrypted key there as part of stored procedure text. This store procedure was then protected using SQL server built in encryption (using WITH ENCRYPTION).

    The details in above link tells that the SQL server inbuilt encryption is not a true encryption rather just a obfuscation which means our store procedure was not encrypted but obfuscated. Is there a way  where we can store the encryption keys in database and encrypt it?

    Any help will be very valuable.

     
    Friday, May 10, 2013 8:29 AM

Answers

  • No, there is no way you can store a secret and they key to the secret in the same room. An intruder will always be able to find the secret.

    You could store the key in a table, and encrypt the key using SQL Server's built-in encryption. But comes the question how to protect that key. SQL Server has a key hierarchy where the keys are eventually protected by the service master key. But to be secure, the user must have at some point supply a passphrase or a private key that is not stored in the database but somewhere else.

    You talk to the vendor of this tool and ask how they think their scheme is supposed to be secure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 10:02 AM
  • TDE will appear as plain text within SQL Server so users can query the information freely. TDE is helpful for protection against data file/backup theft only.

    Placing the key in the database itself while retaining the ability of the application to read it from SQL in plain text will defeat the purpose as Erland mentioned above.

    Saturday, May 11, 2013 2:44 AM

All replies

  • No, there is no way you can store a secret and they key to the secret in the same room. An intruder will always be able to find the secret.

    You could store the key in a table, and encrypt the key using SQL Server's built-in encryption. But comes the question how to protect that key. SQL Server has a key hierarchy where the keys are eventually protected by the service master key. But to be secure, the user must have at some point supply a passphrase or a private key that is not stored in the database but somewhere else.

    You talk to the vendor of this tool and ask how they think their scheme is supposed to be secure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 10, 2013 10:02 AM
  • Hi Shankun,

    How about using TDE instead?


    Hemantgiri S. Goswami | SQL Server Citation| Author of book - SQL Server 2008 High Availability

    Friday, May 10, 2013 12:12 PM
  • TDE is something we were thinking of. But do you have know-how how the key management process can be handled via TDE. Since it will encrypt the database columns, is there a way that the key can be changed.
    Friday, May 10, 2013 12:25 PM
  • TDE will appear as plain text within SQL Server so users can query the information freely. TDE is helpful for protection against data file/backup theft only.

    Placing the key in the database itself while retaining the ability of the application to read it from SQL in plain text will defeat the purpose as Erland mentioned above.

    Saturday, May 11, 2013 2:44 AM