Can't find SSL certificate in SQL Server Configuration Manager RRS feed

  • Question

  • Many people before me has asked themself the same question a gazillion times: After a certificate that seems to comply to Microsoft rules for a SQL certificates has been added to the computer personal store, why isn't it visible in SQL Server Configuration Manager ?

    MS SQL cert rules: Encrypting Connections to SQL Server

    After having checked each requirement against my certificate template more than a hundred times (and particulary the Key Usage where "key encipherment" is set), I stumbled over another support article at HP

    Problems with an SSL Certificate Not Showing up in the Configuration Manager

    showing how to dump the actual certificate. And now it started to get interesting. It turned out that KeySpec in my certificate was 0, even if I had "key encipherment" set in my Server 2008 Enterprise template. How come?

    According to HP article above
    KeySpec = 1 -- AT_KEYEXCHANGE 'signature'

    (AT_SIGNATURE) - the certificate can only be used to sign a payload 'exchange'
    (AT_KEYEXCHANGE) - the certificate can be used to sign AND/OR encrypt a payload.

    Then I found the next article get_KeySpec which lists KeySpec value dependancy of Cryptography Providers, and now it's getting really intreresting. Because here it says that "Microsoft Software Key Storage Provider" always has a KeySpec value of 0 which explains why my template, which was set to support at least Server 2008 Enterprice CA (selected when template was duplicated in CA), generated KeySpec = 0 even if "key encipherment" was set, since templates which has a minimum support of Server 2008 only lists "Microsoft Software Key Storage Provider" and "Microsoft Smart Card Key Storage Provider" under Providers.

    So I deleted the certificate from computer personal store, deleted the certificate template, created a new template based on the same base template but set to support at least Server 2003 Enterprice CA, but with everything else exactly the same, issued the template in CA, and created a new certificate for the local computer.

    And now, BINGO, is the certificate visible in the SQL 2008 R2 Configuration Manager / SQL Server Network Configuration / Protocols for NNN / Properties / Certificate.

    So the question is, what needs to be done with a certificate which is set to support minimum Server 2008 Enterprise CA, so that it is accepted by a Server 2008 R2 SQL Server Configuration Manager? It seems quite basic that they should be compatible, but for the moment it seems like they aren't. Is it a bug in the configuration tool, or has someone goofed up in the basic design?


    Saturday, August 11, 2018 9:25 PM

All replies

  • Based on the complexity and the specific situation, we need do more researches. If we have any updates or any thoughts about this issue, we will keep you posted as soon as possible. Your kind understanding is appreciated. If you have further information during this period, you could post it on the forum, which help us understand and analyze this issue comprehensively.

    Sorry for the inconvenience and thank you for your understanding and patience.

    Tuesday, August 14, 2018 11:02 AM