locked
installed certifacate does not apear in dropdownlist in sql configuration manager RRS feed

  • Question

  • Although i have installed a certificate that meets all the requirement stated in
    http://support.microsoft.com/kb/316898
    but it does not appear in certificate drop down list of certificate tab under the protocols for MSSQLSERVER properties window.

    the operating system is Windows XP sp3 ,SSL Diagnostics Version 1.1 is used to make the certificate
    the SQLserver service is running under localsystem 

    why is that?my second questions is i am using SQL mixed mode and i am afraid of
    sending clear password on the wire what measure i can do to avoid it?
    --yousef
    Sunday, August 22, 2010 8:36 AM

Answers

  • Hi Yousef,

     

    Thanks for your post.

    Since the question in your latest post is a new question, I recommend that you ask it in a new thread and you will get quicker support.


    Regards,
    Tom Li
    Tuesday, August 31, 2010 10:41 AM

All replies

  •  

    Yousef,

    Are you running the SQL Server Configuration Manager under the same user account as the SQL Server service?  Otherwise  the SQL Server service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Monday, August 23, 2010 12:34 AM
  • Sivaprasad S,


    Thanks for quick response.I have changed the SQL Server service account
    so that it runs under the same login which is the creator of the certificate.
    and I logged in as the same login which has administrative privilege and ran the configuration manager still the certificate does not appear in the combobox.I have installed 2 certificates one with makecert and the other by means of SSL Diagnostics Version 1.1
    .Both appear in personal folder of certificates snap-in but one of them(the one was created by SSL Diagnostics Version 1.1)
    appears in trusted root.I really confused the certificas meet all the requirement and the accounts are the same .Do you think the problem is related to the
    windows xp?


    --yousef
    Monday, August 23, 2010 6:04 AM
  • Hi Yousef,

    Which version of SQL Server are you using? Could you please paste the result of "SELECT @@VERSION" here?


    Regards,
    Tom Li
    Wednesday, August 25, 2010 2:41 AM
  • Hi Yousef,

     

    Based on my test, I could see this certificate. Please see the steps I have adopted:

    1.       Generate a Server Certificate
    a) Run “inetmgr” from windows run dialog
    b) Expand “Internet Information Services | <machine name> | Web Sites | Default Web Site”, right-click “Default Web Site” and choose “Properties”
    c) Switch to “Directory Security” tab
    d) Click “Server Certificate…” button under “Secure communications” section to generate a certificate

    2.       Generate new certificate from “SSL Diagnostics”
    a) Open “SSL Diagnostics”
    b) Scroll the textbox to the end and select the row of “#SSL port (SecureBindings) set but certificate not installed”
    c) Click “File | Crete New Cert”

    3.       Make SQL Server to use the certificate generated above
    a) Open “SQL Server Configuration Manager”
    b) Expand “SQL Serve <version number> Network Configuration”
    c) Right-click “Protocols for <instance name>” and choose “Properties”
    d) Switch to “Certificate” tab
    e) Choose the certificate generated above from the dropdown list

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by Tom Li - MSFT Tuesday, August 31, 2010 2:35 AM
    • Unmarked as answer by Tom Li - MSFT Tuesday, August 31, 2010 10:29 AM
    Wednesday, August 25, 2010 6:20 AM
  • Hi Tom,

    Thank you so much for taking time to answer me.
    the problem has already gone by means of makecert.since my sql server is running on win xp which is not a part of a domain ,
    I had to change my primary DNS to Local but the problem with SSL Diagnostics is, it does not include FQDN(fully qualified domain name) in the name of the certificate.
    following code helped me to make a valid certificate to test SSL on my xp

    makecert -r -pe -n "CN=Mycomputername.Local"
     -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1
     -ss my -sr localMachine -sky exchange
     -sp "Microsoft RSA SChannel Cryptographic Provider" 
    -sy 12 c:\test.cer
    

    now the question  is, although i have installed the certificate successfully
    and  have set force encryption to yes under the flags' tab of protocols' window as well as sql native client
    configuration properties but SQL Server accepts both encrypt connection and non-encrypt connection,why?(i have checked the encrypt connection
    of the connection properties of connect to server window).
    also when i run profiler i can capture both T-SQL statements which is run against encrypted and non-encrypted connection
    and i expected the profiler should not be able to get the encrypted connection's T-SQL statement,am I right or something is wrong with my  configurations?
    by the way the following is what i got after running
    SELECT
    @@version
     
    

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
        Feb  9 2007 22:47:07
        Copyright (c) 1988-2005 Microsoft Corporation
        Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

    i have to add (sp2) at the end of 2005 ;)

    --yousef
    Tuesday, August 31, 2010 9:51 AM
  • Hi Yousef,

     

    Thanks for your post.

    Since the question in your latest post is a new question, I recommend that you ask it in a new thread and you will get quicker support.


    Regards,
    Tom Li
    Tuesday, August 31, 2010 10:41 AM
  • Since the question in your latest post is a new question, I recommend that you ask it in a new thread and you will get quicker support.

    Hi Tom ,
    I have opened a new thread which is accessible through the following link:

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/69d55ae6-d4f0-4f91-b226-67ccb0326506

    Hope I can have your assistance.


    --yousef
    Wednesday, September 1, 2010 5:36 PM