locked
Number of symmetric keys RRS feed

  • Question

  • Hi All,

    We are creating a new application which requires data to be encrypted. We are planning to encrypt some columns using encryption keys. These encryption keys will be different for different users. Is there any restriction in number of maximum number of symmetric keys in a database ? Is there any performance issues because of too many symmetric keys ?

     

    Thanks,

     

    Hamlin Stephen

     

    Wednesday, April 7, 2010 12:48 PM

Answers

  • Two comments that I gleaned from Laurentiu Cristofor's comments:

    1. There is overhead from thousands of symmetric keys, but nobody has a benchmark on it.  (I could not find any benchmark / performance analysis published since that time either.) 

      It seems that if a dedicated connection is established to the database, the symmetric key can remain open thus reducing the overhead on opening the keys.  But if this is a web app without a persisted connection then you will be dealing with the overhead repeatedly.

      Of course, performance issues can also be addressed by more powerful CPUs, more memory, etc. 
    2. If you do the encryption from the client side, then each client can have its own encryption keys and the processing will all be done on the client machines.   This distribution of the encryption process to the clients would eliminate the encryption impact on the SQL Server.

    Of course, you presumably will not be encrypting everything, but only the columns of data that are particularly secure.  The more you encrypt on the server, the higher the overhead.  

    You also have the administrative overhead of maintaining the keys (whether on the server or distributed to clients) so that you can decrypt the data again in case of problems.  (Unless you are never supposed to be able to decrypt the data.)

    A side point: In addition to encrypting data with different keys depending on the rows being updated, security can be offered by implementing row-level security.  Although this is not a built-in feature of SQL Server, certainly many people have done it.   Here is a deep discussion of how to do that (not that every feature of this whitepaper may be needed):

    http://msdn.microsoft.com/en-us/library/cc966395.aspx

    RLF

    Friday, April 9, 2010 1:33 PM

All replies

  • I don't have personal knowledge on this topic, but here is a thread from a couple of years ago where Laurentiu Cristofor responded to a similar scenario. 

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/be9aa084-3cf8-44a2-8456-97eede90ffaf

    RLF

    Thursday, April 8, 2010 2:55 PM
  • Hi Almost the same situation here. Users are going to have their own key to encrypt their data.But that post does not have a conclusion.

     

    Can anybody help me ?

     

    Friday, April 9, 2010 9:59 AM
  • Two comments that I gleaned from Laurentiu Cristofor's comments:

    1. There is overhead from thousands of symmetric keys, but nobody has a benchmark on it.  (I could not find any benchmark / performance analysis published since that time either.) 

      It seems that if a dedicated connection is established to the database, the symmetric key can remain open thus reducing the overhead on opening the keys.  But if this is a web app without a persisted connection then you will be dealing with the overhead repeatedly.

      Of course, performance issues can also be addressed by more powerful CPUs, more memory, etc. 
    2. If you do the encryption from the client side, then each client can have its own encryption keys and the processing will all be done on the client machines.   This distribution of the encryption process to the clients would eliminate the encryption impact on the SQL Server.

    Of course, you presumably will not be encrypting everything, but only the columns of data that are particularly secure.  The more you encrypt on the server, the higher the overhead.  

    You also have the administrative overhead of maintaining the keys (whether on the server or distributed to clients) so that you can decrypt the data again in case of problems.  (Unless you are never supposed to be able to decrypt the data.)

    A side point: In addition to encrypting data with different keys depending on the rows being updated, security can be offered by implementing row-level security.  Although this is not a built-in feature of SQL Server, certainly many people have done it.   Here is a deep discussion of how to do that (not that every feature of this whitepaper may be needed):

    http://msdn.microsoft.com/en-us/library/cc966395.aspx

    RLF

    Friday, April 9, 2010 1:33 PM