Let's Encrypt

Since December last year, the Let’s Encrypt initiative has gone into public beta. Let’s Encrypt is a free, automated, and open certificate authority, so you can easily get free official SSL certificates. Primarily it is focused on securing the web, but the requested certificate can be used for every server authenticating. I uses the certificate to encrypt my SQL Server connection and of course use it for Reporting Service.

How does it works

Let’s Encrypt provides certificates that are valid for 90 days. This stimulates the use of tools and scripts as no administrator is reassigning a new certificate every 90 days for each server. primarily Let’s Encrypt is focused on (Linux/Apache) websites, but there are tools available to use it with Microsoft IIS, like letsencrypt-win-simple (the tool I use).Basic every tool does the same:

It creates a secret and stores it as a json file within the web server
Calls the Let’s Encrypt server to validate the server with the secret of step 1
The server of Let’s Encrypt requests the secret json file of step 1
Validates the secrets and returns a certificate if validating is correct
The client stores the certificate and assigns it to the web server

The renewal process is almost the same, but there is a step before that checks if the certificate needs renewal. For Windows the certificate with private key is stored in the Local Computer certificate store and can be used for multiple authentication servers.

Scripting part

I have two servers running in Azure, one web server and one SQL server. Advantage of this is that I have an IIS server to request the certificate for the SQL Server. Disadvantage is that my Reporting Service is running on an alternative port. But it is a demo environment, so that will do. (I have no clue yet how the automatic request/renewal is going to work with storing the secret json file in the SSRS website. Probably the need for a separate tool is needed.)

After the request of a certificate I use some PowerShell script to assign the correct values to the Reporting Service configuration and SQL Server.

Setting certificate security

I run my SQL Server 2016 with a Group Managed Service Account, which should be supported now with SQL Server 2016 (It is supported for FCI clusters: https://msdn.microsoft.com/en-us/library/bb510411.aspx). This account needs to be able the read the private key of the Let’s Encrypt certificate. To get to correct certificate I use the following PowerShell script (and I am not a PowerShell expert, so there will probably be a more efficient way to do this:

(gci -path cert:/LocalMachine/My | Where-Object {$_.Issuer -eq "CN=Let's Encrypt Authority X1, O=Let's Encrypt, C=US"} | Sort-Object -property NotAfter -descending | Select-Object -First 1)

And the rest of the code I use a sample from stackoverflow (http://stackoverflow.com/questions/20852807/setting-private-key-permissions-with-powershell):

$serviceAccount = "<<gMSA account>>"
$cert = (gci -path cert:/LocalMachine/My | Where-Object {$_.Issuer -eq "CN=Let's Encrypt Authority X*, O=Let's Encrypt, C=US"} | Sort-Object -property NotAfter -descending | Select-Object -First 1)

# Specify the user, the permissions and the permission type
$permission = "$($serviceAccount)","Read","Allow" 
$accessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $permission;

# Location of the machine related keys
$keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\";
$keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName;
$keyFullPath = $keyPath + $keyName;

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 
{ 
  throw $_; 
}

SQL Server certificate

Setting the SQL Server certificate is pretty easy: the thumbprint is stored in the registry and can easily be changed. Only the SQL Service service needs to be restarted as changes to a certificate needs a service restart. The PowerShell script. Please notice that the registry key is dependant of the SQL Server version and the instance:

# Register is dependent of the SQL Server version and instance name
$regpath = 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
$oldthumb = (Get-Itemproperty -Path $regpath).Certificate
$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Issuer -eq "CN=Let's Encrypt Authority X*, O=Let's Encrypt, C=US"} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()

if ($oldthumb -ne $newthumb) { 
  Set-ItemProperty -Path $regpath -Name 'Certificate' -Value $newthumb
  Restart-Service mssqlserver
}

SSRS certificate

Reporting Service needs to be configured via WMI. luckily PowerShell easily can use WMI to configure the certificates for the ReportManager and the ReportWebService. The WMI path is dependant of the SQL Server version (v13 = SQL Server 2016, v12 = SQL Server 2014, etc). Unfortunately there is no change SSL certificate function, so I have to remove the binding first and after that add a new binding. Also one caveat: the thumbprint needs to be in lower case or else the CreateSSLCertificateBinding gives an error. Here the PowerShell script:

$httpsport = 443
# modify if you used a different port number when the HTTPS endpoint was created.
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "rootMicrosoftSqlServerReportServerRS_MSSQLSERVERv13Admin"
$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Issuer -eq "CN=Let's Encrypt Authority X*, O=Let's Encrypt, C=US"} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()
$oldthumb = $RSObject.ListSSLCertificateBindings(1033).CertificateHash.Item([array]::LastIndexOf($RSObject.ListSSLCertificateBindings(1033).Application, 'ReportManager'))

if ($oldthumb -ne $newthumb) {
  ## RemoveSSLCertificateBindings for ReportServerWebService port $httpsport
write-host "Calling RemoveSSLCertificateBindings port $httpsport, with certificate hash: $newthumb" $r = $RSObject.RemoveSSLCertificateBindings('ReportServerWebService',$oldthumb,'0.0.0.0',$httpsport,1033) 

## RemoveSSLCertificateBindings for ReportManager port $httpsport
write-host "Calling RemoveSSLCertificateBindings port $httpsport, with certificate hash: $newthumb" $r = $RSObject.RemoveSSLCertificateBindings('ReportManager',$oldthumb,'0.0.0.0',$httpsport,1033)

## CreateSSLCertificateBinding for ReportServerWebService port $httpsport
write-host "Calling CreateSSLCertificateBinding port $httpsport, with certificate hash: $newthumb" $r = $RSObject.CreateSSLCertificateBinding('ReportServerWebService',$newthumb,'0.0.0.0',$httpsport,1033)

## CreateSSLCertificateBinding for ReportManager port $httpsport
write-host "Calling CreateSSLCertificateBinding port $httpsport with certificate hash: $newthumb" $r = $RSObject.CreateSSLCertificateBinding('ReportManager',$newthumb,'0.0.0.0',$httpsport,1033) }

Secure SSRS

Conclusion

After configuring and running the scripts I have an automated process to set certificates for my SQL Server and Reporting Service and create secure connections! Next step will be to use the same certificate for Analysis Service to secure that connection. To be continued.

-JP

By:

Jan Pieter Posthuma

Lovingly made in

Azure BI