After Encryption Keys and Certificates are in the Database. How to Hide them?
I am using Symmetric Encryption. Encryption requires to create keys and certificates. But even after encryption if anyone gets to the database, then he/she will be able to decrypt easily as the key is in the database itself. Same in the case of Asymmetric Key. I think I do not have complete knowledge on this and want to know how to use the keys that reside in server to encrypt/decrypt data in the client database. We have to setup a database in Client as well as Server but want to encrypt the client db. But since the DB will be in the Client Machine, dnt know how to protect data. Any suggestions will be really really appreciated. Thank you very much.
There are a couple of options that you can look at, one is the EncryptByPassphrase/DecryptByPassphrase functions. However this means managing the passwords elsewhere. You could look to use an Authenticator for the EncryptByKey/DecryptByKey functions, again managing the autheticator that is passed in to the functions will need to be managed. Finally there is the option to protect the symmetric keys with a password that needs to be used when opening it. But again this adds a level of management for the password. There are a number of options for managing the passwords, the POC work that I am doing at the moment stored the passwords for the key in the application. I am looking into ways to use .NET encryption to store securly it in a configuration file but I'm not a .NET dev by trade so I have not yet got round to that part. For the password on the key I used this article (
http://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-access/) from MSSQLTips as the thing that got me going down that route. Hope this information is of some help to you. JQ
When using encryption you have to consider how the entire encryption stack works together. The MSDN explanation of the encryption hierarchy is pretty detailed and should help you understand the topic better: [
http://msdn.microsoft.com/en-us/library/ms189586.aspx] Basically, you have encrypted data using a key that is protected by a certificate that is protected by a password. Even if someone gets hold of the database they should not be able to access the data if they don't have the password to open the certificate. The certificates and keys cannot be seen in the database unless you have the correct permissions in that database. A normal user account should not be given elevated privileges in the database and would therefore not even see these objects. :