1

I am using symmetric key encryption in SQL server but the problem is that if anyone has permissions to explore the database they can easily view certificates and symmetric keys and can therefore access encrypted data using these keys. How can this be prevented?

1

While it's true that anyone with read permissions can browse the database using SQL Server Management Studio, additional permissions need to be granted to use the keys for decrypting the data. The first point to keep in mind is that a certificate is a public object, and it's the related private key that needs protection to ensure that it can't be used by unauthorized users to decrypt the symmetric key and subsequently the data. The private key is encrypted by the Database Master Key and is not visible to any user, even to those in the sysadmin role. Just because a user can see the objects doesn't mean they can use them. The permissions needed to decyrypt data using the encryption hierarchy you described, which is a symmetric key protected by certificate protected by the database master key, are CONTROL on the certificate and REFERENCES on the symmetric key. Without both of those permissions, decryption will not be possible. However, if you grant the CONTROL permission on the certificate directly to the user or the user is in the db_owner, db_ddladmin or db_securityadmin role, then the user can back up the certificate and private key, which is a threat to the security of your encryption. In addition to removing the users from the aforementioned roles, there are several ways to address this issue. One is to use an asymmetric key in your hierarchy instead of a certificate, since there is no backup command for asymmetric and symmetric keys. The other is to use code signing and only grant the permissions to a certificate user. Someone also mentioned that it may be possible to use impersonation , or "execute as" in a procedure, but this may not be the safest choice. If you want to replace the certificate with an asymmetric key in your hierarchy, then you should back up the Database Master Key for the database and backup the database, then restore both in a non production environment so you can create the script and test it completely before attempting this in a production environment.

If you really want to hide the names of all of the objects, then you can deny view definition to the users in the database. Again, test in non-production first.

0

Protecting keys is fairly easy - just make sure none of the database users have permissions to them the MSDN article on creating Symmetric keys details the permissions required to view/alter the keys, so just ensure no one has these.

Obviously I have no knowledge of your specific application, but my initial feelings when you say "anyone having the rights to explore the database" would be to say why do many (any) users have such elevated permissions? You could have more problems than users just getting to the keys. Best practise is to only give minimal required permissions to any particular user.

1
  • Thanks for Reply. i want to hide symmetric key and certificate name as we can encrypt store procedure – muhammad aamir akhter Oct 18 '16 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.