none
Login based on Certificate vs. Asymmetric Key RRS feed

  • Question

  • We can create SQL Server logins based on certificates or asymmetric keys for code-signing.

    What differences are there between these two options, and how should we chose one over the other?

    Are there any security advantages of one over the other in particular scenarios?

    Note that since a certificate is based on an asymmetric key, this isn't a symmetric vs. assymmetric issue.

    Tuesday, September 25, 2012 2:03 PM

Answers

  • As far as I understand, it is mainly a matter of the available feature set for the two in SQL Server. Below is from my article on certificate signing on http://www.sommarskog.se/grantperm.html

    Instead of signing your procedure with certificate, you can use asymmetric keys. You create an asymmetric key in <small>SQL</small> Server with the command <small>CREATE ASYMMETRIC KEY</small>. The syntax is similar, but not identical, to <small>CREATE CERTIFICATE</small>. Please see Books Online for details.

    From a cryptographic point of view, a certificate is an asymmetric key that has an issuer and an expiration date. Since it has an issuer, a certificate can participate in a chain of trust, which is important in for instance Service Broker dialogues. When it comes to signing stored procedures, I have (with quite some help from Razvan Socol) identified the following practical differences:

    • An asymmetric key never expires, which for procedure-signing purposes is a slight advantage.
    • You don't have to specify a subject for an asymmetric key.
    • You cannot export an asymmetric key from a database. If you want to sign procedures in two databases with the same key, you could create an asymmetric key outside <small>SQL</small> Server and import it into the databases. (This is possible.) I will need to add the disclaimer that I have not tested whether this actually works.
    • The fact that an asymmetric key cannot be exported, can on the other hand be seen as a security advantage, as someone cannot take your key into another database without your knowing.
    • The key for a certificate in <small>SQL</small> Server is always 1024 bits, where as for an asymmetric key you can choose between 512, 1024 and 2048 bits. It's possible that there is a performance gain by using a shorter key for signing your procedures. However, I have not tested this, nor have I had it confirmed, so it's pure speculation on my part.

    All and all, I can't find any of these points convincing enough to mandate any over the other. I have preferred to talk only about certificates in the main part of this text to simplify the presentation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 25, 2012 8:28 PM