Trusted Certificate Does Not Appear in SQL Server Configuration Manager

I am using the following references:
http://support.microsoft.com/kb/31698
http://technet.microsoft.com/en-us/library/ms189067(v=dql.105).aspx
and others which give the same information. I have installed the trusted certificate into the Personal folder using the Microsoft Management Console. It works fine for HTTPS and secure FTP. However, when I try to enable SSL encryption on SQL Server 2008 R2 Express, the certificate does not show up in the certificates tab when I expand the SQL Server Network Configuration protocols, select properties and then select the certificates tab. It does show up in the personal folder of the certificates store. Is this because I'm using the express edition or something else? I appreciate suggestions on how to remedy this problem or definitive evidence that I'm wasting my time.
LVL 1
rkulpAsked:
Who is Participating?
 
rkulpAuthor Commented:
This blog post seems to explain a lot. I don't know if I can make an alias when I use router port forwarding. I think this ship is dead in the water.

http://blogs.msdn.com/b/sqljourney/archive/2012/03/16/implementing-ssl-encryption-for-sql-server-in-a-dns-forwarding-environment.aspx
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
Did you import the certificate into the personal store for your account or for the local computer? The stores are different, usually you need to import certificates into the store for the local computer.

Open mmc.exe, add Certificates and select option for Local Computer, then add the required certificates here.
0
 
rkulpAuthor Commented:
Peter,
Thanks for your quick reply. The certificate is in both the current user and the Local Computer personal store. It still does not show.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PadawanDBAOperational DBACommented:
Is your sql server database engine running under a non-default service account?  If so, did you import it into the personal store while logged in as the database engine service account?
0
 
rkulpAuthor Commented:
I installed sQL Server using the default settings and have not changed them. When looking at all the services, it shows my logon name and it shows my user name on Task Manager's list of all processes. Hence, I expect it is running under my account. How do I change it to be under the default service account, if that is what needs to be done?
0
 
PadawanDBAOperational DBACommented:
I mean, it doesn't *have* to be.  You just need to import the certificate into the personal store for the account that SQL Server is running under - that's what I was trying to make sure of.
0
 
rkulpAuthor Commented:
I have imported the certificate into each of the three choices: current user, service and local computer. It still does not show in any case. I'll try putting it in folders other than personal until it works or I exhaust the choices.
0
 
PadawanDBAOperational DBACommented:
Last question I have - is it a wildcard cert or is it a cert for the FQDN of the server?

Edit: also to answer your original question, I don't believe it's because you're on express https://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx)
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
Does this certificate include the private key as it need both public and private key installed when using it. The private key is generated when you generate the certificate request and resides on whatever computer you generated it on. Also, make sure any root certificates are installed as well.
0
 
rkulpAuthor Commented:
Yes. Why does it show up in the store but not in the SQL Server Configuration utility? Why does it work for HTTPS and FTP and not SQL Server?
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
I have looked at SQL 2008 R2 Express, and the only option I found is to turn on Trusted SSL certificates.
Maybe just having one installing in the cert store is needed, you do not need to configure it specifically?!
0
 
rkulpAuthor Commented:
I turne on Enforce Trusted SSL Certificates and was able to connect using what worked when it was turned off. I need to confirm that the connection on the client side is the same.
Thanks.
0
 
rkulpAuthor Commented:
I needed to set Encrypt=True in the connection string. When I did that I got the following error:
Encryption-Error-Message.png
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
Make sure any root certificates are moved from Personal folder (if imported there), to the Trusted Root Certificate Authorities (TRCA). If its a self-signed cert, copy the certificate itself to the TRCA folder.
0
 
rkulpAuthor Commented:
The certificate is in the trusted certificates folder for the computer. I have forwarded the error to the CA. We'll see what they have to say. Thanks for your help.
0
 
rkulpAuthor Commented:
I solved the SSL problem based on this related question:

http://www.experts-exchange.com/Programming/Languages/.NET/Q_28630081.html
0
 
rkulpAuthor Commented:
The blog post clearly indicates the problem. It also implies I can't do what I want to do because I don't have a DNS server; I only use router port forwarding. Hence I can't do the aliasing necessary. Peter asked all the right questions. If this had been a normal situation, then he would have the answer. He deserves and got all the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.