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).