Certificate Deployment Issue

[DeaconFrost]

Sorry for this being vague, but I'm still gathering the details. I handle installing certificates we get from partners and clients on several servers so they can reach out securely to push or pull data. I normally get one at a time, so I've been installing them into the Certificate Management Snap-In manually. SQL servers get the certs installed under local user, which is that service account that runs SQL. Web servers get the certs installed as local machine certs.
So here's the problem. Something is different between a cert installed manually and one installed via PowerShell. I'll post my scripts, but I'm still not sure what is different....except my Devs say the certs don't work when deployed via script. I've gone into the mmc.exe snap-in to verify they are in the correct store, correct permissions, etc. I can't see a difference.
I started using the PowerShell scripts recently to save time. I built new servers that needed 75 certs installed, so I did so via PowerShell. Is there a better cert deployment tool? Aside from my issue above, I'd love to know if there's a better tool or application. Or, if there's a better script.

SQL/Local User:
Import-PfxCertificate -FilePath ./certfile.pfx -CertStoreLocation Cert:\CurrentUser\My -Password (ConvertTo-SecureString -String 'password' -AsPlainText -Force)

Web/Local Machine:
Import-PfxCertificate -FilePath ./certfile.pfx -CertStoreLocation Cert:\LocalMachine\My -Password (ConvertTo-SecureString -String 'password' -AsPlainText -Force)

 

[cam966251]

Just guessing based on my experience with how to use a keypair in .NET/C#, I would assume your issue is that you're missing the -Exportable arg. The cert API has a wierd behavior where it doesn't throw an exception at the time of export, and it only happens when you try to access the private key. If that doesn't work, could you see what the exception is or send a code snippet that's failing?

Beyond that, to answer your question about a better way to handle this, I would urge you to consider using less manual configuration of servers and a adopt a modern GitOps model or at least config management. The exact tooling is less important than the operational pattern, but you could use a config mgmt tool to pull from a secret store and deploy certs on some event based on how you import the process for accepting the keypairs. Your devs and mgmt will appreciate reducing toil, deploying faster, and creating a more reliable platform. I'd be happy to help you with some ideas to get started.

 

[MrGuvernment]

Off topic, but must ask:

Are these connections over VPN tunnels or dark fiber or something? Or do you have NAT rules going direct into your SQL databases - if so, stop right there.

Could you not proxy these connections via an SSL offloader, NGIXNX proxy or something similar to centralise, and also, secure said connections, giving a single point of control, as cam966251 also hinted towards...

Really in no scenario should any vendors have direct DB access into your network....or hopefully as you noted "pull" information in, you just query data to / from via another interface, but then I ask why are certs being installed directly on the SQL servers

 

[DeaconFrost]

Just guessing based on my experience with how to use a keypair in .NET/C#, I would assume your issue is that you're missing the -Exportable arg. The cert API has a wierd behavior where it doens't throw an exception at the time of export, and it only happens when you try to access the private key. If that doesn't work, could you see what the exception is or send a code snippet that's failing?

Beyond that, to answer your question about a better way to handle this, I would urge you to consider using less manual configuration of servers and a adopt a modern GitOps model or at least config management. The exact tooling is less important than the operational pattern, but you could use a config mgmt tool to pull from a secret store and deploy certs on some event based on how you import the process for accepting the keypairs. Your devs and mgmt will appreciate reducing toil, deploying faster, and creating a more reliable platform. I'd be happy to help you with some ideas to get started.

I'd love some suggestions on tools the centrally manage these certs. We use Bamboo/Octopus to deploy code and certs to some servers, but that's only in use by a specific dev team. As for the code snippet, I don't have anything meaningful from the developers. The only difference I see is a LocalSession permission missing. However, when I've added or removed that on a test server, it makes no change to the functionality of the process.

 

[DeaconFrost]

Really in no scenario should any vendors have direct DB access into your network....or hopefully as you noted "pull" information in, you just query data to / from via another interface, but then I ask why are certs being installed directly on the SQL servers

No vendors do have access. These DB servers also act as app servers, with a process to authenticate to a secure portal and pull information at a set interval. It's a terrible design, so we've forced them to rebuild and separate out the roles of an app server and the DB servers.

 

[MrGuvernment]

No vendors do have access. These DB servers also act as app servers, with a process to authenticate to a secure portal and pull information at a set interval. It's a terrible design, so we've forced them to rebuild and separate out the roles of an app server and the DB servers.

So many red flags here!
Especially considering supply chain attacks....

NO vendor should have ANY direct access into YOUR network via nat rules, ever for anything, at min, a VPN tunnel into your network, to at least keep things from being internet exposed.

And if they do, they need to be 100% isolated and locked down in their own VLAN, to which you then control your app access there as well, even on a separate VM, and if the vendor is only populating the DB, how often does that happen?
Is it real time? or once a day?

Cause a safer process. while manual is to download a DB dump from them and import it.
I would certainly be questioning the design and talking to the vendor and let them know their methods are insecure and do they not have other options.

 

[DeaconFrost]

I understand, but there are no vendors involved. We've got it secured much better than it seems. This is a common process used in the industry I'm in. SSL certs are involved, IP address restrictions, etc. We're getting WAY off topic, and we're already building a new design for the process. My questions were about certificates.