About security and encryption with references to SQL Server 2005

There have been many books written on security and encryption, and there is much talk about security these days. I will not bring anything new with this post to the general topic of security, but I would like to present some ideas in condensed format.

The main point I want to make is that we cannot discuss security without defining what are the valuables to protect, against what type of access do we want to protect them, and who are the attackers against whom we need to protect. Without knowing this information, no security measures can be meaningfully evaluated. It may not be possible to block all attack methods, but if some of them are much more expensive than what the attackers can afford (money, time, physical resources, knowledge), then this cost can be sometimes considered a good enough mitigation. Beware, however, of underestimating an attacker's capabilities; many security solutions have failed because they have underestimated the resourcefullness of the attackers. When thinking about software security, a very important point to keep in mind is that software is relatively cheap to produce when the technical knowledge is available; hence, once someone figures out a way to break software X, that attack can be mass-distributed virtually for free and people who would not have been able to break product X by themselves, will now be able to do it with minimal effort. The task of designing a secure system is very difficult because a single security hole is sufficient to compromise the system. To make things even harder, secure systems are rarely supposed to operate alone, they usually have to interact in complex ways with other systems that have their own security considerations, so security is as much a matter of engineering as it is a matter of administration and deployment.

Encryption plays an important role in many security schemes. But encryption by itself is just a security tool; it is not sufficient to provide security. Unfortunately, encryption is sometimes expected to resolve all security problems. Nothing is farther from reality. Encryption can provide solutions for a number of security problems but it cannot resolve all of them and will also introduce some problems of its own. One of the most obvious encryption issues is key management. Encryption will protect a secret such that it can only be recovered by providing another secret - the decryption key, but how do we protect this other secret? Using encryption again will leave us with the same problem for a different key.

So, to summarize the previous two paragraphs, security is a sum of several factors that cannot be ignored and encryption is just a tool, not a magical provider of security. Note that what I discussed so far are very general ideas, they are not related to the security of a particular product or to the details of a particular encryption scheme.

Now, I would like to more specifically discuss the security obtained through encryption in SQL Server 2005. The data encryption keys in SQL Server are protected in two ways: through permissions and by being encrypted using other keys. These two layers of protection provide defense in depth for the keys: the permissions should be enough, but if they can be bypassed, the encryption on the keys should provide a second barrier. The encryption of the keys can be made by other keys or by a password, forming an encryption chain. Ultimately, this chain must end either with the service master key encryption or with a password encryption. As I mentioned in previous posts (part 1, part 2), the service master key is protected using DPAPI. From the server point of view, the access to a symmetric key is limited depending on where the encryption chain ends (see, for example, my earlier comments on using symmetric keys). If the encryption chain is rooted at the service master key, decryption can always be performed by the server automatically, and access to the key is only restricted by permissions. If the chain is rooted at a password, direct access is restricted to those with knowledge of the password (I mentioned "direct access", because "indirect access" could be given through signed code, for example, by embedding an open key statement in a signed procedure and granting execute to others on the procedure). However, these access limitations should be interpreted carefully, they are not security guarantees. I'll try to clarify this point through the next paragraphs.

I have received several questions lately inquiring about ways to protect data in SQL Server against the machine administrator and whether the encryption features can help. We could encrypt the data and if nobody would access it, the machine administrator would not be able to decrypt it. But if clients will connect to the server to access the data and decrypt it on the server machine, then the machine administrator, having full control of the box, can patiently wait for such a moment to read the decrypted data (or the password that protects the encryption key) from the machine memory. So, while the machine admin does not have direct access to the encrypted data stored in the server, he could easily gain access by simply attaching a debugger to the server process. The root problem here is that if the data can be "seen" by the server on a machine, then the machine administrator can be assumed to be able to access it as well. Of course, it is possible to protect against the machine administrator by doing the encryption/decryption off-site and only storing the encrypted data within the server, but this is a solution that will not fit all scenarios.

What about the protection of data against a sysadmin, could that be achieved? A sysadmin would have a much harder time circumventing data encryption if he has restricted access to the operating system, that's for sure. But a sysadmin still has a lot of power because he can do anything that can be done inside the server that would just require a permission check. Even if sniffing a password from memory can be very hard to achieve by a sysadmin, there are various other ways through which he could attack a system from inside. For example, by profiling the server he could try to collect passwords that are unsafely processed by applications. Or he might examine the code of stored procedures that use hard-coded passwords to open symmetric keys. Or he might just execute malicious code to compromise the server integrity and try to disclose sensitive information. Encryption is the best protection that can be obtained for data against a sysadmin, but it should not be considered bulletproof.

Encryption provides strong security for protecting data at rest. But encryption does not protect and cannot protect data that is currently worked on that has to be in decrypted form. Such data has to be protected by securing the machine and operating system on which the database resides. It's not possible to have a secure database setup on a poorly administrated machine and it is very hard to protect against the machine or database administrators themselves, which are powerful system "insiders". Only by protecting all parts of a system, can a system be considered secured.

Comments (8)

  1. Jim Youmans says:

    I have a pretty good understanding of how encryption works in 2005 but what I am looking for is a real world example of how it has it has been done.  

    Let’s say that I have a table with one or more encrypted columns that are encrypted by symmetric Key1.  I want only authorized users to have the ability to decrypt the data.  I don’t want DBA’s or the DBO or the
    developers to be able to do this.

    I would think I would have to create Key1 using a password.  But then how do I protect that password to make sure the DBA’s or developers don’t get hold of it.

    My thought is that I could install something on the web server (a encrypted password or certificate or something) that only the application on that box could access, and then that is used to open Key1 and decrypt the data.

    The reason I would do this is not because I don’t trust my sysadmins, but due to client security requirements.


  2. Well, as I explained in my post, you won’t be able to protect the data against a machine administrator. You can protect it against a dbo as long as you restrict his privileges on the machine and you can make it hard for a sysadmin to access your data as well, but no method will be bulletproof against a machine administrator that can debug processes and can examine the server memory – he will be able to get at the decrypted data eventually.

    The only solution I can think of that would come close to answer your requirements would be to encrypt and decrypt the data outside the database server, maybe in the client application. This way, the client would only exchange encrypted data with the database server and the administrators of the latter would not be able to get at the cleartext data other than by attempting a brute force attack on the encryption.

    You should do a reality check on the client security requirements. Not all requirements can be fulfilled; in some cases you will need to agree to a tradeoff.


  3. Charley Lou says:

    I read a couple of your articles related to encryption topic and I feel that’s really helpfull I don’t know if you are familiar with encryption issues in replication and clustering environment. I read some documents from Microsoft web site that explains how to move an encrypted database from its original server to another new server instance. That cause a lot manual works, if the database master key has been encrypted by the original service master key and you still want to enjoy the auto-open feature in the new environment. As we know the Microsoft SQL Server 2005 has a hierachy encryption key structure and its top level service master key is really service oriented. For what kind of mechanism or set up, Microsoft makes their encrypted database working smoothly and automatically in a clustered and replicated infrastructure. I search the web site all the way, but very disappointed on information return. Do you have any good idea or experience to share?


    Charley Lou

    Oct 25, 2006 11:19 PM

  4. Clustering Scenarios – You don’t need any special setup for encryption.

    Replication Scenarios – Currently, Encryption does not work with Replication in a seamless way. If you want to use both features, you will have to manually replicate the encryption keys.

  5. Charley says:

    I am still a little bit confusion. In clustering environment, the same database is running on different server instance. Will this database should has the same master key under the different service master key or can we unify the service master key on different clusted server? Will it be possible to use statement:ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY to break the key chain from service master key to solve this problem? Can you explain your solution in detail?


  6. Yes, in a clustering scenario, you have the same database, so you have the same encryption keys – same database encryption key and same service master key. There is no special need to decouple these keys. Things just work – there is no solution involved, so there is no solution to explain.

    Because you also posted on the Security Forum, please also post future questions on that forum in continuation to the thread you started.

  7. ghasa says:

    Thanks for the great information on encryption!  I have a question I’m hoping you can answer.  After creating a certificate in SQL 2005, if no authorization is specified in its creation and no users are mapped to it, by default who has rights to use it?  

  8. The creator has full rights to using the certificate.

    If the certificate is protected by a password, only the creator can sign or decrypt using the certificate. The dbo can only verify a signature or encrypt using the certificate or backup the public key – assuming he does not know the password.

    If the certificate is encrypted by the DbMK, then it can be fully used by both the creator and any dbo.

Skip to main content