Share via

How Secure Is Your Data?

  • Article

End users want their data to be secure and for that SQL Server 2005 has various protection levels to encrypt the data. The simplest way to encrypt data may not be the best option, as you may not have the desired level of protection.

Books Online is a good resource to know the syntax for encrypting data in various ways. To make my points clear, I will at times give references to certain DDL that you need to use. But my primary goal with this blog is to just emphasize what security guarantee you get depending on what protection mechanism you choose.

It is well known that data in SQL Server 2005 should always be protected by a symmetric key. But you need to carefully choose how to protect this symmetric key. The symmetric key can be protected by a password, a certificate, an asymmetric key or any other user created symmetric key, depending on what level of protection is desired.

 To make the right decision, end users need to answer the question - 'Whom am I trying to protect my data from?'
Level 2: From attackers who can get access to the database files (protecting data at rest)
Level 1: Level 2 + From the sysadmin
Level 0: Level 1 + From the dbo

The Ultimate Protection. Only I can see my secrets.

For end users that want the Level 0 protection, the symmetric key should only be protected by a password. This is the most secure option as the sysadmin or the dbo cannot access the symmetric key in any way and the data is truly protected. Data at rest is also protected, as even if an attacker manages to steal the files, he can only decrypt the data if he knows the password. What some may perceive as a disadvantage is that each time you need to encrypt or decrypt the data; you need to ensure that the key is open. But this is only a small price you need to pay to ensure your data is fully protected. The system view sys.open_keys will show all the open keys in a session at any given time. If the key is not open, then the following DDL statement needs to be issued to open it
  OPEN SYMMETRIC KEY <key_name> DECRYPTION BY PASSWORD = '<password>'

DBO is my friend. Let him know my secrets.

For end users that want the Level 1 protection, the requirements can be eased slightly. The symmetric key should be protected by a certificate [or an asymmetric key]. The private key of the certificate [or the asymmetric key] need to be protected by the database master key i.e. at the time of creating the certificate do not specify the ENCRYPTION BY PASSWORD option.
  CREATE CERTIFICATE c1 with subject = 'EncrCert'

If you have an existing certificate [or the asymmetric key] protected by a password and you would like to use that, you can change the protection to the database master key using the following DDL statement.
  ALTER CERTIFICATE c1 WITH PRIVATE KEY DECRYPTION BY PASSWORD = '<password>'

It may not be intuitive but not specifying the ENCRYPTION BY PASSWORD option with the ALTER CERTIFICATE statement, decrypts the private key of the certificate using the password supplied with the DECRYPTION BY PASSWORD option, and re-encrypts it using the database master key.

Now comes the important part, protecting it from sysadmin. The dbo or highly privileged database user needs to make sure that the database master key should only be protected by the password. At creation time, the database master key is encrypted by the service master key by default. The Service Master Key encryption should be dropped explicitly using the following DDL statement.
  ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

By doing so, the database owner does have access to your data as he can use the database master key to decrypt the private key of the certificate, which can then be used to open the symmetric key and decrypt the protected data. However, you are still protected from the sysadmin as he needs to be able to open the database master key in his session, and without the password there is no way to do it once you drop the encryption by the service master key.

I only care about my data that is stolen.

For end users that want Level 2 protection, things are a piece of cake. He needs to encrypt the data with a symmetric key that is protected by a certificate [or an asymmetric key] as in Level 1 protection. The certificate [or an asymmetric key] is protected by the database master key as demonstrated earlier.

The important difference is that the database owner should not drop the encryption of the database master key by the service master key (that he gets by default at creation time) or needs to re-add the encryption of the service master key using the following DDL statement
  ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The sysadmin then has access to the database master key and to any entity (certificate or asymmetric key) encrypted by the database master key. Of course, this means your data is not protected from the sysadmin or the database owner, but the data at rest is protected. If someone is able to steal the database files, and is able to attach it to his own SQL Server Instance he still cannot decrypt the encrypted data, because he does not have the password to the symmetric key or access to the keys in the hierarchy, that protect the symmetric key.

For Level 1 and Level 2 protection, it is important to remember that you need to be aware if the database master key is protected by the service master key or not. You will also need to be aware that your protection level might get dropped from Level 1 to Level 2, if the owner of your database chooses to add the protection of the service master key back to the database master key.

In conclusion, there is this inherent conflict Security seems to always have with Usability. Leveraging on the key hierarchy in SQL Server 2005 eases administrative requirements slightly due to the fact that you DONT need to explicitly open the symmetric key that you use to protect data. You can build transparent data encryption around that feature, which I will cover in a later blog. But then you need to understand your data is not secure from the sysadmins and database owners. Encrypting symmetric keys with password can make your data super secure, but you need to manage the opening and closing of the key that protects the data.

Make the choice that suits you best, but understand your security requirements and never compromise on security for usability!

Comments

  • Anonymous
    December 02, 2005
    Thanks for this informative blog.

    But I have a question. In the "DBO is my friend. Let him know my secrets" section, you have said "...By doing so, the database owner does have access to your data as he can use the database master key to decrypt the private key of the certificate, which can then be used to open the symmetric key and decrypt the protected data. ...". How can DBO open the DMK without knowing the password?
  • Anonymous
    January 17, 2006
    Thanks - this was very helpful. This was the best article on this topic that I've found.
  • Anonymous
    April 26, 2006
    yukondoit

    Great article, very helpful

    Rgds,

    Steve Sawyer