asked on

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.

Peter Hutchison

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.

rkulp

ASKER

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.

PadawanDBA

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?

rkulp

ASKER

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?

PadawanDBA

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.

rkulp

ASKER

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.

PadawanDBA

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)

Peter Hutchison

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.

rkulp

ASKER

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?

Peter Hutchison

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?!

rkulp

ASKER

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.

rkulp

ASKER

I needed to set Encrypt=True in the connection string. When I did that I got the following error:
Encryption-Error-Message.png

SOLUTION

Peter Hutchison

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rkulp

ASKER

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.

ASKER CERTIFIED SOLUTION

rkulp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rkulp

ASKER

I solved the SSL problem based on this related question:

https://www.experts-exchange.com/questions/28630081/Some-Encryption-Decryption-Questions.html

rkulp

ASKER

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.