I would like to use TDE, but I cannot use it, so I have chosen to use the EncryptByCert and DecryptByCert functions. However, I was also considering encrypting/decrypting data in c# as shown here.
My question is are EncryptByCert and DecryptByCert unsecure because the certificate is also stored in the database? How do people get around this problem?
Is using the c# built-in encryption a better idea?
Thanks in advance for any help. :)
There is an Encryption Hierarchy one has to deploy:
The root key used can be the service master key (actually the root is the service password, but this is transparent to the service start), in which case the applications can access the data just by simply connecting to the database and decrypting it. This protects the data if the database file is lost accidentally, but does not protect the data against compromised access to the server: since the decryption key is held by the server itself (the service master key) then anyone that has access to the data can see the data, because anyone can decrypt it. The data is protected through normal access rights and permissions, but the cryptography does not add any protection against authorized users.
The other option is to rely on a password at the root o encryption hierarchy, in which case the application must ask the user for the password to access data. For instance, if is a web site , the user would have to fill in a data decryption password in order to access a specific page. This truly protects the data cryptographically against users that gain access to the data through ordinary data access permissions and don't know the password(s).
SQL Server 2008 also offers the possibility to leverage a hardware TPM to store the decryption key (ie. an employee badge) but this is Enterprise Edition functionality only.
Ultimately, asking if the SQL Cryptographic API is secure or insecure is a non-question. The API itself of course is secure. Encrypting with the data with a symmetric key and then encrypting the symmetric key with a certificate and storing the private key of the certificate along with the data in the database is secure, as long as the private key is properly protected. The security or insecurity of a cryptographic deployment it always ultimately driven by the key management (bare some faulty implementation, but lets assume the implementation is perfect) and key management is 1% application design and 99% human process.
Ultimately, you need to do a serious threat modeling (sorry, but "The threat is that the data is private information and should not be seen plain text." is not threat modeling). Follow a methodology like STRIDE, or other similar methodologies. This is a very hard domain, and is deceptively simple to read about cryptographic APIs and believe that you know how to protect data:
The world is full of bad security systems designed by people who read Applied Cryptography