Creating and Setting SQL Server Certificates with PowerShell

I recently had a need to add certificates to SQL Servers throughout an organization. There were quite a few servers to update and the certificates would need to be generated using a given format. This would include some descriptors for the names, IPv4 address, and ensuring that SQL Server would see the certificate when finished.

I realized this would need some sort of script so reached for PowerShell and the dbatools module. There’s a function in dbatools that supports setting the SQL Server Certificate and I knew that would be useful. But first, I had to generate the certificate itself. I read up on this in PowerShell and there’s no “easy” button for creating a certificate at this time, especially not when you need to add extra properties.  Posts such as this one helped me get started. It works by creating an INF file, then shelling out to “certreq.exe” to generate the CSR file needed to obtain a certificate from a certificate authority. We had need to use the DNS name, the FQDN, and the IPv4 address as part of our certificate request, so I had to adjust my code to handle that.

Let’s start with getting the IPv4 address. I had to filter out the “home” address and ignore any IPv6 addresses so used this:

Next, I needed to get the DNS name and the FQDN for the server and set the various variables to use throughout the INF file. The Certificate Name seems to be required to be the Machine Name in order for SQL Server’s Configuration tools to see the certificate. It will work without that, but it’s easier to support if you can see it. I adjusted the “Friendly Name” to be something that would make sense

To add those in to the INF file appropriately, I used the following:

Finally, I would pass all of that over to an actual INF file and then call “certreq.exe” to generate the Certificate Request and save that file.

Now that I’ve generated a Certificate Request, I need to get the actual certificate, then upload it back to the server, import that certificate to the appropriate location in the certificate store, and assign it to SQL Server.  In order for SQL to use that certificate, the services have to be restarted.  The trickiest part of this was figuring out the correct web calls to make to POST the CSR to the Certificate Authority site, then download the certificate. This was done through a lot of trial and error using the Debug console inside the browser. I’m also quite thankful that I can just update lines of code in PowerShell to try something new without needing to re-run everything. That made it easier, though it still took a couple of hours to get everything correct.  I’d done similar coding several years back using curl so the concept wasn’t too foreign, but it had been a while.

Start with connecting to the SQL Server, using admin credentials I’d saved in a CliXML file. Then call the function above to generate the CSR on the server and copy the INF/CSR files down to my local machine. While not strictly necessary, I found this helpful.

Next, connect to the Certificate Authority and POST the contents of the CSR:

Then download the generated certificate to my local machine. The RegEx was crafted using something similar to https://regex101.com/ to paste my results and verify that my string found the occurrence I wanted for the appropriate file. Again, this will vary depending on the CA:

Now that I have the Certificate downloaded locally, it needs to be uploaded to the server. The assumption with this is that there’s only one “*.cer” file in the server’s folder to find. The certificate location in the code will probably be the same for most Windows servers.

Then we need to find the Thumbprint of the certificate in order to set it for SQL Server. Again, this code is assuming that the Friendly Name for the Certificate started with MSSQL and that there was only one certificate matching that in the store.

Using that Thumbprint, we will set SQL Server to use this. Note that the code has a “ToUpper”. My trials seemed to indicate that if you didn’t use uppercase for the Thumbprint, SQL Server would not see it as expected.

Finally, we force a restart of the SQL Server Service and then disconnect from the session.  Normally, I’d use DBATools’ Restart-DbaService command, but for some reason that wasn’t working properly from my machine. I resorted to using a remote script call to do that instead and then disconnected from my PS session.