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?

Improve this question
5
  • 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
    Commented Sep 17, 2014 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
    Commented Sep 18, 2014 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
    Commented Sep 18, 2014 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
    Commented Sep 18, 2014 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
    Commented Sep 18, 2014 at 9:54

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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