Sql Server can't see my certificate

▲
0
▼

I need to install a certificate for encryption (replication) between an external vendor and my company.

I cannot get a third party certificate for the FQDN of my server because the net part of that does not match a domain that we own (ie my FQDN is sqlservername.company.root.net but we don't own a domain called company.root.net.). We do own mycompany.com, so I got sqlserver.mycompany.com on the cert and have a DNS entry to alias sqlserver.mycompany.com to sqlservername.company.root.net.

I cannot use a self generated cert since the vendor needs to trust the cert authority.

I have a cert that I have purchased and installed, but SQL Server won't see it since the FQDN doesn't match.

I tried installing it by putting the thumbprint of the cert into the registry directly, but then SQL server won't start with the following errors:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.

Unable to load user-specified certificate [Cert Hash(sha1) "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

(where the x's above match the thumbprint of the cert without spaces)

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

What do I need to do differently to get this working?

encryption
ssl-certificate
sql-server-2008-r2
asked on Stack Overflow Jun 14, 2011 by William Salzman

2 Answers

▲
3
▼

You need to use MMC to install your certificate in the certificate store and then use the SQL Server Configuration Manager to link the certificate to your SQL Server service. See https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

Then, make sure that the service-account running you SQL Server service has full permission on the certificate. In MMC, right-click on the certificate, select Manage private key, and then grant full access to the service-account running you SQL Server.

You should restart your SQL Server for the changes to take effect.

answered on Stack Overflow Aug 13, 2019 by F1XM4J
▲
2
▼

Before anything else, you must install the certificate in the Windows certificate truststore.
Did you do that?
The error

You should verify that the certificate is correctly installed

seems to indicate you did not do this.
I was expecting that the hostname verification would be configurable but from here SSL in MS-SQL2008 r2 it seems as an absolute requirement.
To be honest I am not sure if the trick you did with the DNS entry will work.
It seems that some tweeking works for cluster installations ssl for cluster installations
In your case, may be you should have bought the certificate using the IP as subject name and use DNS to resolve to the FQDN you say.
But of course this implies use of a static IP and most likely it would not be feasible as well anyway.....

answered on Stack Overflow Jun 14, 2011 by Cratylus

User contributions licensed under CC BY-SA 3.0