Home » SQL ServerRSS

installed certifacate does not apear in dropdownlist in sql configuration manager

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
 

7 Answers Found

 

Answer 1

 

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.

 

Answer 2

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
 

Answer 3

Hi Yousef,

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

 

Answer 4

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
 

Answer 5

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
 

Answer 6

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.

 

Answer 7

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
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter