SQL Server 2005: A note about the use of certificates


To avoid any confusion, this post is not about the use of certificates for securing the communication between a client machine and the server; instead, this refers to the use of certificates created via the CREATE CERTIFICATE DDL.


I am prompted in writing this post by a recent question I just saw, which I know I answered many times before, but I now realized I never wrote about it here. The question is simply around the differences between how certificates are used elsewhere and how certificates are used in SQL Server. I’ll answer this question here and I’ll add some additional information and references, which I hope you will find useful.


The main use of certificates that we are familiar with from daily browsing activities is about the signing of code on the Internet. This signing is done as a way to prove the authenticity of a piece of software, because a signature can help validate the manufacturer of a piece of software. I say “can help”, because not everyone checks carefully the details of a digital signature to ensure it is indeed trustworthy. For more details around this type of use scenario, you can look into PKI – Public Key Infrastructure.


Certificates can be used in many ways, because asymmetric key encryption is a very powerful tool, so, when certificates were introduced in SQL Server, one of the design goals was to build a generic store for encryption keys, without putting any unnecessary restrictions on their use. Because of this, expiry settings or certificate revocation are not enforced. SQL Server just regards the certificate as a key container and keeps track of all of its settings, but it doesn’t restrict its use – it is up to you to decide what to do with it. The main use scenarios within SQL Server do not require any restrictions, so the only thing that is done is to give some warnings at the time the certificate is imported – if it was expired, for example. I should also note that features within SQL Server may do additional checks on a certificates’s settings: for example, the Service Broker feature does check for expiration of its certificates. If your intent is to plug into PKI, that is also possible through CLR.


So, what are the main use scenarios for having certificates in SQL Server? Here they are:


1. You can use certificates to sign T-SQL code.
2. You can use certificates to encrypt symmetric encryption keys or small pieces of data.


Let’s discuss these a little. Signing T-SQL code is the most powerful security feature shipped with SQL Server 2005 (my opinion, of course, as is any other subjective evaluation you may read on this blog). But, really, think about it: signing allows you to grant permissions to code instead of granting them to principals calling the code – this opens a world of possibilities in terms of how you can manage permissions and how you can restrict access to objects. So that you don’t get any wrong idea at my enthusiasm for this feature, I will add that I made no significant contributions to it, so I am not trying to push forward my work 😉 For a simple example of the things you can do with signing, you can have a look at this example. Raul’s blog has additional examples and information on this feature.


You can also use special builtin functions (SignByCert) to sign your own data, but unfortunately, due to a bug, their implementation uses the MD5 algorithm instead of the SHA1 algorithm used in T-SQL signing, so they are not interoperable – you cannot use these functions to simulate the internal code signing, so that you could manually compute the signature of a function, for example; instead, you would need to actually create the function and sign it via ADD SIGNATURE, then read the signature blob from the system catalogs.


The second use of certificates is quite natural for an encryption key – we would definitely like to encrypt something with it. The reason why certificates are more convenient to use for protecting symmetric keys than other methods is simply because they can be backed up individually. This also gives them an edge over their cryptographic sibling – the asymmetric keys. For a discussion of why there are two objects encapsulating RSA keys in SQL Server, see this explanation.


Some people think that certificate encryption is inherently safer than symmetric key encryption – it is not. There is no significant gain in strength from securing your data with an RSA key instead of using an AES one, as long as you are using equivalent key lengths – not equal, but equivalent – more on this below. The RSA encryption is slower than any symmertric key encryption and its larger key length is simply due to it being a fundamentally different technique and requiring longer key lengths than symmetric key algorithms, to offer the same strength of encryption. For a discussion of key lengths and a comparison of key lengths across symmetric and asymmetric algorithms, you can check this article or a cryptography book. This means that certificates are not appropriate for data encryption and their use should be restricted to the encryption of symmetric keys (which are small enough that the encryption performance is not a concern) and to the application of digital signatures (when the key is only used to encrypt what is basically a hash of the signed data, hence, again, performance is not a concern given that the length of a SHA1 hash value is 20 bytes/160 bits).

Comments (5)

  1. lcris says:

    With the risk of repeating myself, let me try to explain again why certificate revocation is not essential to the SQL Server use:

    In SQL Server, certificates are not used for signing code that is distributed to clients or other servers. They are used to either encrypt keys or to grant permissions to code in a database. If a certificate is compromised, you can generate another and re-encrypt your keys in the database or re-sign your objects – you do not need to revoke the certificate for other servers to know it’s not trustworthy, because other servers should not care about your certificate anyway – the trust in a certificate is limited to the server to which it is deployed, because the permissions assigned to it are database or server scoped. In a way, you can look at code signing in SQL Server as a way of assigning roles to T-SQL code. Same way as you can assign a bag of permissions to a role, you can assign a bag of permissions to a certificate and then sign code with that certificate like you would make users members of a role. The equivalent of dropping a role member in this signing paradigm would be removing the signature off a procedure (or any signable module).

  2. aloukian says:

    Hi, Thanks for this very good blog covering SQL server cryptography.

    I have a question.

    I am planing to use SQL encryption in my project, but I encountered a problem. I have two users that should be able to access encrypted information. One user is mapped to Windows user, the second user is mapped to Windows security group. I was planning to encrypt symmetric key (which is used for encryption) using two certificates created for each of these users. The problem is that user mapped to Windows security group cannot own a certificate, therefore this user cannot open symmetric key. I cannot use password to protect the encryption key, because both users are actually programs and I will face the problem of storing the password securely.

    I read your article on TSQL code signing – can it be used to solve my problem? Is there any other solution?

  3. lcris says:

    Sorry for the delay in responding – I haven’t got a comment in a while, so I don’t check for new comments that often these days.

    I suggest posting your question on the SQL Server securirty forum and then posting a link to that thread here. Please make sure to explain what exactly you are trying to accomplish – often times, people ask how I can do X and it turns out that it’s not necessary at all to do X and a simpler solution exists.

  4. sara says:

    Please, can you better detail and clarify the following issue?

    "You can also use special builtin functions (SignByCert) to sign your own data, but unfortunately, due to a bug, their implementation uses the MD5 algorithm instead of the SHA1 algorithm used in T-SQL signing, so they are not interoperable"

    It's not clear to me where the difference between MD5 and SHA1 lead to.

    Thanks in advance.

  5. lcris says:

    "It's not clear to me where the difference between MD5 and SHA1 lead to."

    The main point in this context is that it leads to a difference between what the builtin functions do and what the server uses internally. As I mentioned: "they are not interoperable – you cannot use these functions to simulate the internal code signing, so that you could manually compute the signature of a function, for example; instead, you would need to actually create the function and sign it via ADD SIGNATURE, then read the signature blob from the system catalogs."

    Also, MD5 is a weak algorithm that is no longer considered secure, so you would not want to use it for any security applications. See: en.wikipedia.org/…/MD5 for more details.