none
SQL Server 2008 SSL Certificate List Blank RRS feed

  • Question

  • We are running SQL Server 2008 Workgroup edition with instance name MSSQLSERVER on Windows Server 2008 Standard. We just purchased and installed an SSL certificate for IIS 7.0 for a new domain name hosted on the same physical server. The server is not part of a domain, but rather just another workgroup computer. From what I read on the help files and the microsoft tutorials, I am supposed to be able to encrypt all data to the SQL server with the same SSL certificate. It shows up under Personal Certificates in the Certificate MMC. However, when I go to SQL Server Configuration Manager and right click Protocols for MSSQLSERVER, then go to Properties, under the first tab, Flags, I push Force Encryption to yes, and then over to the Certificate tab I am supposed to select a certificate. Well, here's the thing, there is nothing in the list. Not even a self-signed certificate. Nothing. The list is blank. How can I make our SSL certificate show up on that list so we can start encrypting all traffic? The thing is that enabling Force Encryption doesn't do anything. When we tried logging in with SQL Server Management Studio from another location and selected to encrypt traffic, the handshake said the host was not trusted. Any help with this would be greatly appreciated.
    Monday, February 8, 2010 9:37 PM

Answers

  • Hi FireFerum,
    I never configured a server certificate in a Workgroup environment before. Based on my research, probably you may be able to create a server certificate with FQDN according to the following steps:
    1.Click Start, click Control Panel, and then double-click System.
    2.Click the Computer Name tab.
    3.Click the Change button, and then in the Computer Name/Domain Changes dialog box, click the More button.
    4.In the DNS Suffix and NetBIOS Computer Name dialog box, in Primary DNS suffix of this computer, specify the DNS suffix to be appended to the name of the computer. Then click OK.
    5.After you apply the changes, restart the computer to initialize it with its new FQDN name.
    6. After you create the FQDN for your server, you recreate a server certificate with the FQDN according to the previous suggestions and then configure it on your SQL Server instance.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by FireFerum Monday, February 22, 2010 8:25 PM
    Wednesday, February 17, 2010 9:14 AM
    Moderator

All replies

  • Some info on installing SSL certificates for SQL server , but I am not sure whether the same certificate for both SQL and web services can be used.

     

    http://support.microsoft.com/default.aspx/kb/316898

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


    Thanks, Leks
    Monday, February 8, 2010 10:10 PM
    Answerer
  • Hi FireFerum,
    This is an article dedicated for SQL Server 2008, http://msdn.microsoft.com/en-us/library/ms191192.aspx.
    Please try the steps and let us know the result.

    Best regards,
    Charles Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, February 11, 2010 7:28 AM
    Moderator
  • Well I tried that article and the result was that there are no certificates showing up in the SQL Server's Network Protocols Properties. Could it have something to do with the certificate being issued for IIS? Do I need to get another SSL certificate? If so, what domain do I put in when ordering it since I am getting it strictly for the SQL server and I already have one for the IIS and the website associated with the IIS? Thanks for the tips so far.
    Thursday, February 11, 2010 10:59 PM
  • Hi,
    If you could not see the certificate, it means that your certificate is not a valid certificate for SQL Server. To SQL Server instance, a valid certificate must be:
    "The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer".
    ref: http://msdn.microsoft.com/en-us/library/ms191192.aspx

    Please recreate a server authentication certificate and specify the FQDN for it.

    Best regards,
    Charles Wang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, February 12, 2010 10:56 AM
    Moderator
  • Thank you for the help so far, however I am still in the same spot. I followed the link you sent and I can see the certificate in the Personal Certificates. It points to the website that the server hosts with IIS 7. Maybe the problem is with the FQDN but here is my setup and maybe you can point me in the right direction:

    Server 2008 on Workgroup. Named "SERVER"
    IIS Running a website with randomdomain.net. (For confidentiality's sake randomdomain.net is something else in reality)
    IIS Has a certificate for randomdomain.net for SSL and it is working as I can do SSL web traffic.
    SQL Server 2008 running as LocalSystem on MSSQLSERVER instance

    When following the article you last sent on "To configure the server to accept encrypted connections" Step 2 asks me to find the certificate on the drop down list of the tab, but the drop down list is empty. If I need to have another certificate issued, what would be my FQDN?

    Thank you for the continued help.
    Wednesday, February 17, 2010 12:33 AM
  • Hi FireFerum,
    I never configured a server certificate in a Workgroup environment before. Based on my research, probably you may be able to create a server certificate with FQDN according to the following steps:
    1.Click Start, click Control Panel, and then double-click System.
    2.Click the Computer Name tab.
    3.Click the Change button, and then in the Computer Name/Domain Changes dialog box, click the More button.
    4.In the DNS Suffix and NetBIOS Computer Name dialog box, in Primary DNS suffix of this computer, specify the DNS suffix to be appended to the name of the computer. Then click OK.
    5.After you apply the changes, restart the computer to initialize it with its new FQDN name.
    6. After you create the FQDN for your server, you recreate a server certificate with the FQDN according to the previous suggestions and then configure it on your SQL Server instance.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by FireFerum Monday, February 22, 2010 8:25 PM
    Wednesday, February 17, 2010 9:14 AM
    Moderator
  • That was exactly it. On Workgroup, there is no way to get a certificate issued directly to the server since there is no domain. Adding server.randomdomain.net in the DNS suffix changed the full name of the server and therefore allowed a new SSL certificate from IIS to work in SQL as well.

    Thank you Charles!
    Monday, February 22, 2010 8:28 PM
  • I just wanted to add after going through this issue SQL does not support wild card certs even if you add the server as a sub domain on the cert.  Apparently there is a registry hack out there. I just had my CA issue me a cert for Server.CompanyDomain.net and it was displayed in the list of certs. and the FDQN Computer name must match the cert.

    ie) *.companydomain.net
            Server.companydomain.net

    Friday, April 2, 2010 8:11 PM
  • I have an externally hosted sql server without a domain name associated - I connect using the ip address. I am facing the same issue that the certificates dropdown is empty.

    Any help is appreciated.

    Saturday, March 17, 2012 10:36 PM