3

I'm not a DBA by trade. My organization doesn't have a dedicated DBA, we have to just figure things out. I have a SQL Server 2012 Enterprise installed on a Sever 2012 R2 OS. It is a not in a SQL cluster. The server is a virtual machine. I've been asked to encrypt the connection for the SQL server, as described here: https://msdn.microsoft.com/en-us/library/ms191192(v=sql.110).aspx

I don't have any problems until I get to selecting the certificate. The certificate does not display. It's loaded properly in the MMC certificate plug in. Before posting here, I've looked at numerous links. I was going to post a few of them, but I don't have enough reputation points. Here is one I looked at: https://www.experts-exchange.com/questions/27860135/SSL-cert-not-showing-up-in-SQL-Server-2008-Configuration-Manager.html

The Engine and Sql Agent services do run as domain accounts. I've logged in as the engine account and added the certificate to the user account's personal store. I've also given both domain accounts full control to the private key. I've restarted the sql engine, sql browser and sql agent services. I've restarted the server. It still doesn't show up under Protocols for MSSQLSERVER > Properties > Certificate tab.

As for creating the certificate request. I created the certificate request on the database server. I used the MMC snapin for certificates (local computer). I right clicked Personal > All Tasks > Advanced Operations > Create Custom Request. The friendly name is the servers host name (not FQDN). For the subject name, I used the Common name. Key usage options I selected were CRL Signing, Decipher only, Encipher only, Key agreement, Key encipherment. And I selected "Make these key usages critical". For Extended Key Usage, I selected Server Authentication and Client Authentication. I did NOT select "Make the Extended key usage critical". Basic constraints are enabled but left blank. I selected "Make the basic constraints extension critical". The private key is RSA, 2048-bit. Private key is exportable. Hash algorithm is SHA512.

Any help is greatly appreciated.

  • The cert name should be FQDN e.g. servername.domain.com. Use DECLARE @DomainName NVARCHAR(150) EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domainname OUTPUT SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @DomainName AS FQDN to find FQDN and generate the cert with that name. – Kin Shah May 18 '16 at 15:16
  • Thank you for your help. Just so I'm clear, by cert name, you mean the friendly name should be the FQDN? And I believe that means I need the common name to be the FQDN as well? – user3271408 May 18 '16 at 16:07
  • Yes, I tend to keep the cert and friendly name as same - just to be consistent. FYI, I had similar requirement to do a POC and I had same issue as you faced yesterday. I resolved it by keeping the same name - FQDN and reload the cert. – Kin Shah May 18 '16 at 16:10
  • Thank you. I'll give it a shot and I'll post my results. – user3271408 May 18 '16 at 17:59
  • This did not seem to work. I uploaded a new certificate. The cert and friendly name are both the FQDN. I added the two domain accounts to have full control to the private keys. I restarted the SQL services. But the SQL Network Configuration Protocols properties still does not see the certificate. I can restart the whole server tomorrow morning, just to try that. – user3271408 May 24 '16 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.