Hello Folks:
This really is not a question. I need to confirm my understanding of column level encryption in SQL 2005. Ill divi up points based on the value responses add to this conversation. References strongly appreciated.
We are upgrading from SQL 2000 -> 2005. I am charged with doing a proof of concept on encrypting the most performance sensitive column of the most performance sensitive table in our database.
I want to find a magic certificate based solution where the DBMS encrypts the column for me with a single command. From there, when the table is queried from a machine with the magic certificate installed, it gets plain text returned. If the certificate is not there, hash is returned. Also in the magic solution, if a select statement is passed to the dbms containing a where clause with the column in plain text the dbms is smart enough to encrypt that value before interrogating indexes. Finally in the magic solution I do not have to change my ASP.NET application, short of installing the magic certificate on my web servers.
As usual, I am not finding magic. I am finding SQL 2005 has plenty of encryption alternatives, but not the solution above.
I intend to do certificate based triple DES encryption of the column. I am discovering:
1) The schema of the table must change. Specifically the column in question must change for a varchar to a varbinary.
2) My application is always responsible for encrypting/decrypting the data. SQL 2005 provides the tools (EncryptByCert, DecryptByCert). It does not do the function unless asked. (My ASP.NET application has to change.)
3) The key produced is database specific. So I need to replace snapshot based transactional replication schemes with SQL 2005 database mirroring. (This is a good thing.)
4) My ASP.NET app needs to be performance aware. If searching for a specific row based on plain text, it would have to encrypt the plain text and perform the search based on the encrypted value.
Again, please respond with anything you can add to this or if you wish to debate what I have found above. I can be wrong. Thanks in advance.
