2

We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS.

We imported the same certificate on the SQL Server machine.

However, when trying to configure the instance from configuration manager there is certificate showing up in certificate tab.

With reference to link for deploying and troubleshooting certificate: http://support.microsoft.com/kb/316898/

Apparently the certificate was not issued to the machine FQDN/hostname as there is no domain for the SQL Server machine or the Web Server machine.

I tried creating an OpenSSL certificate on a machine using its hostname but when trying SCM to configure the instance after certification deployment there's no certificate in the tab.

Questions:

  • Does an OpenSSL certificate or a purchased certificate use port 443? Reference this link: https://stackoverflow.com/questions/9342769/sql-server-cannot-find-certificate.

  • If yes, what is the GUID for my SQL Server instance or where can I get this info while running "netsh".

  • Above link also states.

    “The last step was making sure the account running SQL Server had permission to read the certificate. I went into the certificate snap-in and then went to properties under the certificate, then on the Security tab I gave the Network Services account read permission on the certificate. (NOTE: I did not find any tutorials online reference this step. I was just some dumb luck that I found this.”

  • I couldn't find any security tab; is there something other than the MMC wizard to see this property of certificate?

Server details:

Window Server 2008 R2
SQL Server 2008 Web Edition

What am I missing?

  • Read technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx, it will address some of your issues. There is nothing in a certificate specifying what port to use. – dartonw Sep 17 '14 at 20:03
  • Darton, i've checked certificate requirement mentioned on your reference link with certutil and found all fullfilling, my sql server service is using local system account, so i tried open scm with Administrator user but still couldn't find in certification tab. – Harsimranjeet Singh Sep 18 '14 at 9:09
  • Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST : MAKECERT tool used for preparing certificate with command mentioned in link above – Harsimranjeet Singh Sep 18 '14 at 9:52
  • contd: FYR : makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -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 "cs020.classic.classicinformatics.net" is my system FQDN. contd. – Harsimranjeet Singh Sep 18 '14 at 9:53
  • Then open mmc and added console to view certificate, And it was automatically restored in the certificate store under "personal" Then opened SCM and view properties of SQL SERVER protocal. ISSUE: My system hostname was "cs020" and its in domain "classic.classicinformatics.com". If it was in workgroup then creating certifcate with "CN=cs020" would have worked, but i check my fullcomputer name and it was stating something else i.e. "cs020.classic.classicinformatics.net" , so there was it i used the above command and got my results. – Harsimranjeet Singh Sep 18 '14 at 9:54
0

Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST :

1) MAKECERT tool used for preparing certificate with command mentioned in link above –

FYR : makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -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

Where "cs020.classic.classicinformatics.net" is my system FQDN.

2) Then open mmc and added console to view certificate, And it was automatically restored in the certificate store under "personal" 3)Then opened SCM and view properties of SQL SERVER protocal.

FYI: My system hostname was "cs020" and its in domain "classic.classicinformatics.com". If it was in workgroup then creating certifcate with "CN=cs020" would have worked, but i check my fullcomputer name and it was stating something else i.e. "cs020.classic.classicinformatics.net" , so there was it, i've used the above command and got my results.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.