5

I am trying to setup some certificates for different hostnames (aliases) for SQL Server Reporting Services so that I can secure connections on my own machine for testing purposes. My understanding is that these certificates need to be signed by a trusted Root Certificate Authority (explained here). After doing some research, I've found a lot of helpful information that can generally be summarized by this post; the information I've found tells me how to create new certificates using Powershells New-SelfSignedCertificate cmdlet.

So now I know how to create a new Root CA, and I know how to create an SSL certificate for a given hostname which is signed by the new Root CA. However, I can't find anywhere that explains how to re-use an existing Root CA. I want the code to work like this:

$rootCA = <# What goes here? #>
$TestSigned = New-SelfSignedCertificate -subjectName "CN=TestSignedByRoot" -Signer $rootCA

Am I supposed to just create a new Root CA every time I want to create a certificate? That seems insane so I sure hope that's not the case. If any of you could provide some explanation and sample code it would be greatly appreciated.

Jake
  • 7,565
  • 6
  • 55
  • 68

3 Answers3

3

To get a certificate from the certificate store with known hash:

$rootCa = ( Get-ChildItem -Path cert:\LocalMachine\My\EEDEF61D4FF6EDBAAD538BB08CCAADDC3EE28FF )

To see what certificates there are in the My store:

Get-ChildItem -Path cert:\LocalMachine\My

To get a certificate by subject name, use Where-Object to check the Subject property:

$rootCa = Get-ChildItem -Path cert:\LocalMachine\My | Where-Object {$_.Subject -eq "CN=My Awesome Root CA"}
Mikael Dúi Bolinder
  • 2,080
  • 2
  • 19
  • 44
0

You are missing a key word here: Trusted. Not just by you, but by any computer that tries to verify the validity of the certificate. Most companies will have a PKI solution to be able to issue certificates internally, and will have the certificate for the Root CA pushed out via GPO into the Trusted Roots folder of the local machine certificate store. The Root CA should be something that is already established, and trusted by the computers that will be connecting to your SQL server. I would recommend contacting your IT or Security department to ask about if the company has a Trusted Root CA that you can get your certificate signed by, or if they can issue you a certificate that chains to the trusted root.

Edit: Ok, I missed the point. My understanding was 'my machine' being the SQL server that you support vs other computers that would connect to it. If the only computer that you are concerned with is your own, then this comes down to:

  1. Create a Certificate Signing Certificate (Certificate A)
  2. Add Certificate A to the Trusted Root folder of your LocalMachine certificate store
  3. Create a self signed certificate (Certificate B) using Certificate A as the signing certificate

Once you have completed steps 1 & 2 you can repeat step 3 as many times as needed to make additional certificates. You should only need 1 Root cert ever. The important part is that the Root certificate is trusted by the 'client', so that when it connects to the SQL reporting service and sees the certificate it can establish a chain of trust to the root certificate that initiated the chain.

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
  • 1
    There is no "IT" because I am administrating my own machine. No other computers are (or should be) involved in this equation. – Jake Apr 28 '18 at 02:24
0

To use an existing trusted certificate, you will need a copy of the trusted certificate in the Personal Certificates folder (cert:\LocalMachine\My) I used CertMgr. Else when you finally run your command to create the self signed certificate, you get an error

New-SelfSignedCertificate: CertEnroll::CSignerCertificate::Initialize: Cannot find object or property. 0x80092004 (-2146885628 CRYPT_E_NOT_FOUND)

PBo
  • 399
  • 3
  • 6