TMBTC Post

Using Let's Encrypt with MS SQL Express


DATABASE SQL LETS ENCRYPT POWERSHELL

Using Let's Encrypt with MS SQL Express

2017-09-15
using-lets-encrypt-with-ms-sql-express

I was asked recently to see if I could use the Let's Encrypt SSL certificate from an IIS server to encrypt SQL connections. Turns out with a little PowerShell magic, you can!

This post assumes you have Windows IIS and SQL Express (but SQL Server wouldn't be much different) running on the same box and a working Let's Encrypt certificate with a scheduled task enabling the auto renewal of the certificate. If you haven't heard about Let's Encrypt, I encourage you to go check them out.

https://letsencrypt.org/

Let's Encrypt and most of it's native tools were built for Open platforms such as Linux; however, there are some nifty tools built for Windows to help automate the acquisition and renewal of certificates. The one I like is called letsencrypt-win-simple

On to the script...

The general flow of the script is fairly simple.

  1. Check for a new https certificate thumbprint.
  2. If it differs from the one currently configured with SQL then export the IIS certificate to disk.
  3. Import the certificate into the Personal Store (Cert:\LocalMachine\My).
  4. Update permissions on the Crypto\MachineKeys directory (for the specific cert imported).
  5. Update the Certificate registry key for the SQL service.
  6. Restart the SQL service.
If there is no difference in thumbprints between the IIS cert and the one in the SQL Certificate registry key, no changes are made and the script simply exits.

I ran into a few minor bumps along the way. SQL Server is MUCH more picky when it comes to using SSL certificates.
  • The hostname on the certificate must match the hostname of the machine.
  • The domain on the certificate must match the domain of the machine.
  • The certificate needed to be accessible in the LocalMachine\My location for SQL to use it. (at least that was my experience)
  • The certificate, when exported, needs to have the entire chain and external properties (hence the password in the script).
  • The account used to run SQL needs to have access to read the certificate in the MachineKeys directory. (fixed in the script).

# serviceAccount is the account which your SQL instance runs as
$serviceAccount = 'NT Service\MSSQL$SQLEXPRESS';

# serviceName is the name of the sql service
# this is used to restart the sql instance once a new certificate has been installed
$serviceName = 'MSSQL$SQLEXPRESS';

# regpath is the location of the SuperSocketNetLib node which contains the Certificate thumbprint
# this will change depending on the version of MS SQL you are running
$regpath = 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQLServer\SuperSocketNetLib';

# certPassword is the password used to export and import the certificate
# keep this generic because its right here in this script plain as day
$certPassword = ConvertTo-SecureString -String 'p!ck_@_g00d_One!' -Force -AsPlainText;

# certIssuer is the value of the IIS Let's Encrypt Authority issuer.
# Its this value is used as a search string really so this process will work with other certificates
$certIssuer = "CN=Let's Encrypt Authority X3, O=Let's Encrypt, C=US";

# exportPath is the location in which we will be exporting the cert
# this can be a temporary location (but it must exist)
$exportPath = 'C:\';

# exportCertFile is the exported certificate file name
# this can be pretty much any valid file name
$exportCertFile = 'export.cer';

#####################################################################
# Start of script
#####################################################################
Write-Output "Starting Cert sync for SQL";
Set-Location Cert:\LocalMachine;

Write-Output "Getting the Certificate value in regestry";
$oldthumb = (Get-Itemproperty -Path $regpath).Certificate;

Write-Output "Getting the Certificate value used by IIS";
$newthumb = (Get-ChildItem -Path Cert:\LocalMachine\WebHosting | Where-Object {$_.Issuer -eq "$certIssuer"} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower();

Write-Output "Checking to see if existing thumbprint is the same as the new one";
Write-Output "Current Cert: $oldthumb";
Write-Output "New Cert    : $newthumb";
if($oldthumb -ne $newthumb){
  Write-Output "New thumbprint detected";

  # Specify the user, the permissions and the permission type
  # So later, we can push these permissions to the MachineKeys directory
  Write-Output "Creating Access Rule";
  $permission = "$($serviceAccount)","Read","Allow";
  $accessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $permission;

  # Location of the machine related keys
  # The SQL process, when starting will try and read the Cert here
  # C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
  Write-Output "Building path for MachineKeys to modify permissions";
  $keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\";
  $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName;
  $keyFullPath = $keyPath + $keyName;
  Write-Output "Full key path";
  $keyFullPath

  Write-Output "Setting Access Rule for MachineKeys directory";
  try{
    # Get the current acl of the private key
    $acl = Get-Acl -Path $keyFullPath;
    # Add the new ace to the acl of the private key
    $acl.AddAccessRule($accessRule);

    # Write back the new acl
    Set-Acl -Path $keyFullPath -AclObject $acl;
  }catch{
    Write-Output "ERROR setting ACL";
    throw $_;
  }

  Write-Output "Exporting new cert $newthumb";
  $exportFilePath = $exportPath + $exportCertFile;
  (Get-ChildItem -Path Cert:\LocalMachine\WebHosting | Where-Object {$_.Issuer -eq "$certIssuer"} | Sort-Object -property NotAfter -descending | Select-Object -First 1) | Export-PfxCertificate -FilePath ("$exportPath$exportCertFile") -Password $certPassword

  Write-Output "Importing new cert $newthumb";
  Import-PfxCertificate -FilePath $exportFilePath -CertStoreLocation Cert:\LocalMachine\My -Password $certPassword;

  Write-Output "Updating Certificate value of $regpath";
  Write-Output "New value: $newthumb";
  Set-ItemProperty -Path $regpath -Name 'Certificate' -Value $newthumb;

  Write-Output "Restarting SQL Service";
  Restart-Service $serviceName;
}else{
  Write-Output "No changes detected. Nothing changed";
}

This script was then added to a scheduled task within Task Manager to run shortly after the Let's Encrypt task.

Hope you found this information useful.

You might also like
simple-bash-script-to-email-server-status

Simple Bash Script To Email Server Status

2012-08-22

I didn't want to constantly have to log into my servers in order to check on key performance indicators so I decided to write a simple script that would do the checking for me. After collecting results, the script emails them to me. There are a few tools called within the script you might need to install. I also convert any tabs into spaces in order to make sure things line up nicely inside my email. #!/bin/bash SERVER="myserver001" TOEMAIL="admin@myservers.com" FROMEMAIL="myserver001@myserverscom" # Who is logged in and what are they up to WHO=`w` #


Read More...

how-to-fix-pagehandlerfactory-integrated-has-a-bad-module-when-setting-up-asp-net

How to fix: “PageHandlerFactory-Integrated” has a bad module when setting up ASP.NET

2012-08-22

I was recently setting up IIS 7.5 on Windows 2008 R2 for an ASP.NET site and came across the following 500 error: Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list. After a bit of searching, it turns out ASP.NET was not completely installed with IIS even though I checked that box in the "Add Feature" dialog. I found a number of suggestions but found this command. It fixed my issues and got rid of the error. %windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i For a 32 bit system, use the


Read More...