none
SSL Certificate not visible from SQL Configuration Manager

    Question

  • Hi all,

    for some reason I am not able to see the certificate from the Configuration Manager --> SQL server network configuration --> Protocols for MSSQLSERVER when I right click and select the Certificate under the dropdown.
    Certificate was imported from sys admin guy and I can see it under Certificates' Personal folder of the Console Root - certificates (Local Computer). It also looks that is configured and imported properly and in line with the requirements under the Microsoft's links below:

    http://technet.microsoft.com/en-us/library/ms191192.aspx
    http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx

    The version of the operating system is where SQL server resides is Windows Server 2012 Standard Edition and SQL Server is 2012 developer edition.

    Since my sql server services Engine is running under service account with Deny Logon Locally domain policy, I started the service as LocalSystem and open the Configuration Manager with an administrative account, but still didn't worked.

    Feedbacks on this issue are highly appreciated.

    Cheers

    Friday, October 18, 2013 2:06 PM

All replies

  • Hi Ban,

    Please verify if the value for below registry key is NULL or not

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

    This registry key should contain the thumbprint property of the certificate we want to use. If this is null that means Certificate is not imported properly or having some issues, in that case

    * Right click on certificate

    * Click Details tab

    * Check value for THUMBPRINT key

    * Copy that value in a notepad and delete spaces in between

    * Copy this value to the above mentioned registry key

    * Restart SQL Services and check if it shows the certificate

     

    You can follow this article (though its for SQL 2005)

    http://blogs.msdn.com/b/jorgepc/archive/2008/02/19/enabling-certificates-for-ssl-connection-on-sql-server-2005-clustered-installation.aspx

    Hope this will help.

     

     

     


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Friday, October 18, 2013 9:04 PM
  • Hi Gursethi,

    Thanks for the reply. I have also tried that, and it didn't worked, which anyway is meant for the clustered instances and not standalone. My instance is sql server 2012 standalone installation. However, it has the Availability Groups enabled, which I am afraid that is the main reason. Since when I put the Thumbprint into registry the sql instance fails to start with the error "TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid. ". And my aim was not testing a database which is part of the AAG, but some other dummy database.

    I am planning to perform another test with poorly standalone sql instance on sql server 2008 R2 version and see if works.

    Cheers

    Monday, October 21, 2013 9:12 AM
  • Hello,

    The error message indicates the certificate is invalid or cannot be accessed. You can refer to the Troubleshooting section in the KB article to determine whether the certificate that you installed is valid.

    If the certificate is correctly installated and work well on the standalone SQL Server instance, but not work with Cluster instance, I recommend you that submit a feedback at https://connect.microsoft.com/SQLServer/

    Regards,
    Fanny Liu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 24, 2013 10:20 AM
    Moderator
  • Hi Fanny,

    thanks for the reply. I managed to install the certificate correctly and also appears on the Configuration Manager --> SQL server network configuration --> Protocols for MSSQLSERVER and also Forced Encryption to yes. This works ok under the user account which I installed the certificate when running the SQL server service under the same user, however when I start sql service under dedicated domain account service failing to start since is not able to find that certificate under this domain account. This account has the Deny Logon Locally policy enabled and I am not able to start MMC under that user and import the certificate.

    How is possible to solve this situation, since we must keep the policy Deny Logon locally for the service accounts, which also Microsoft's recommended setting.

    Best regards,
    BanSQL

    Friday, October 25, 2013 2:52 PM
  • Hi BanSQL

    Did you ever solve this issue with the domain service account having the Deny Logon Locally policy applied.

    I'm running into this problem myself right now and can't for the life of me figure out how to get around it.

    Regards

    Keith

    Wednesday, November 2, 2016 1:30 PM
  • After some Google searching (a lot, actually) I came across this procedure which seems to have fixed it:

    1. First we need to find the name of the service account used by the instance of SQL Server. It will probably be something like ‘SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]‘.
    2. One way to do this is to navigate to the installation directory or your SQL Instance. By default SQL Server is installed at C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName.
    3. Right click on the MSSQL folder and click Properties.
    4. Click the Security tab and write down the user in the Group or user names window that matches the pattern of ‘SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]‘.
    5. Now, open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.
    6. Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item (Note: Select computer account and Local computer in the two pages on the wizard that appears.
    7. Click Ok.
    8. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported.
    9. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys…
    10. Click the Add… button under the Group or user names list box.
    11. Enter the SQL service account name that you copied in step 4 and click OK.
    12. By default the service account will be given both Full control and Read permissions but it only needs to be able to Read the private key. Uncheck the Allow Full Control option.
    13. Click OK.
    14. Close the MMC and restart the SQL service.
    Wednesday, November 30, 2016 9:27 PM
  • Not working for me. I've changed the THUMBPRINT key value and my certificate didn't show up in drop down of the protocols' certificates, the old one just was not selected anymore.
    Monday, March 5, 2018 12:44 AM
  • I've created the cert from OpenSSL and can't make this to work.
    Monday, March 5, 2018 1:17 AM
  • I've seen cases in which the THUMBPRINT needs to be UPPERCASE for this to work. Even facing a time in which when using it LOWERCASE the SQL services won't start after reboot.
    Monday, March 5, 2018 4:50 PM