question

asimsuvedi avatar image
asimsuvedi asked

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.
securityencryption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLServerMonkey avatar image
SQLServerMonkey answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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][1] 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. [1]: http://msdn.microsoft.com/en-us/library/ms189586.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.