SQL Monitor 12

Configuring HTTPS for the default SQL Monitor web server

Since version 12.0, the default SQL Monitor web server has been upgraded to ASP.NET Core 5, and is now hosted by Kestrel instead of IIS Express. Kestrel is a Microsoft-supported production-ready web server that supports HTTPS.

If you choose to use the default SQL Monitor web server in the web installer, then you will need to configure and enable HTTPS after the installer is complete. Otherwise, see Installing SQL Monitor on IIS for instructions on setting up IIS hosting, including configuring HTTPS. In either case, you must also configure the additional HTTPS settings described at the bottom of this page.

Enabling the HTTPS endpoint and configuring a certificate

In order to configure the HTTPS endpoint, you'll need to copy the Webserver.Kestrel.config file from the website installation directory (%ProgramFiles%\Red Gate\SQL Monitor\Web by default) to %ProgramData%\Red Gate\SQL Monitor and edit it accordingly.

You should see that the <http> endpoint is enabled on the port configured by the installer. You will need to uncomment the <https> endpoint, then fill in one of the certificate configurations. The unused certificate configuration elements should be removed.

After editing the config file, restart the SQL Monitor Web Service Windows service to ensure that the configuration has been reloaded.

Configuring HTTPS using a .pfx certificate file on disk

A PFX formatted certificate containing the private key is required. The .pfx certificate configuration is in the following format:

        <certificate>
          <path>/path/to/certificate.pfx</path>
          <password>[PFX cert password]</password>
        </certificate>

Specify the path to the certificate file, as well as the password to decrypt the certificate file if required.

Configuring HTTPS using PEM certificate files on disk

With a PEM formatted certificate, the certificate data and private key are expected to be in separate files. The certificate configuration is in the following format:

        <certificate>
          <path>/path/to/certificate.crt</path>
          <keypath>/path/to/keypath.key</keypath>
          <password>[PEM cert password]</password>
        </certificate>

Specify the path to the certificate file, the path to the private key file, and the password to decrypt the private key if required.

If keeping the password in plain text isn't allowed for you, we recommend configuring HTTPS using a certificate from the Windows Certificate Store as instructed below. Alternatively, you can put the Webserver.Kestrel.config file in C:\ProgramData\Red Gate\SQL Monitor and limit read access to just the account that you choose for SQL Monitor's Kestrel Web Server to run under. The password will be in plain text, but access to it will be locked down. 

Configuring HTTPS using a certificate from the Windows Certificate Store

Using a certificate from the Windows Certificate Store is more secure than the above options, but requires more setup. The certificate configuration is in the following format:

        <certificate>
          <subject>[certificate subject CN]</subject>
          <store>[certificate store]</store>
          <location>LocalMachine</location>
          <allowInvalid>false</allowInvalid>
        </certificate>

The configuration values should be set as follows:

  • subject: Specify the Common Name (CN) for the certificate here. This value will be used to filter the list of available certificates.
  • store: The programmatic name of the certificate store that should be searched for the certificate. 
  • location: LocalMachine or CurrentUser. If CurrentUser is specified, make sure that the SQL Monitor Web Service windows service is running as the appropriate user to see the certificate.
  • allowInvalid: If set to true, this may allow certificates that are expired or otherwise invalid. This should be kept as false unless strictly necessary.

The following PowerShell script may help to find the appropriate configuration values. It will list all certificates that are visible to the user running the script which are eligible for use by Kestrel:

$serverAuth = New-Object -TypeName Microsoft.PowerShell.Commands.EnhancedKeyUsageRepresentation -ArgumentList "Server Authentication", "1.3.6.1.5.5.7.3.1"

Get-ChildItem -Recurse Cert:\ |
    Where-Object { ! $_.PSIsContainer } |
    Where-Object { $_.HasPrivateKey } | 
    Where-Object { $_.EnhancedKeyUsageList -contains $serverAuth } |
    Where-Object { $_.Verify() } |
    Select-Object @{n = 'Path'; e = { Convert-Path $_.PSParentPath } }, Subject, FriendlyName, Thumbprint

Here is some example output from the script:


Path           Subject      FriendlyName
----           -------      ------------ 
CurrentUser\My CN=localhost Example site certificate


In order to use this certificate, we would specify "localhost" as the subject, "CurrentUser" as the location and "My" as the certificate store.

Additional HTTPS settings

Since version 12.0.9, there are a number of additional HTTPS settings that can be configured. Default values for these settings are defined in the  %ProgramFiles%\Red Gate\SQL Monitor\Web\appsettings.json configuration file (by default this will be C:\Program Files\Red Gate\SQL Monitor\Web\appsettings.json). You should not directly edit this file, since it's subject to change when newer versions of SQL Monitor are installed. Instead, settings can be overridden by making changes to the %ProgramData%\Red Gate\SQL Monitor\appsettings.json file (by default this will be C:\ProgramData\Red Gate\SQL Monitor\appsettings.json).

The Strict-Transport-Security HTTP response header

All HTTP responses include the Strict-Transport-Security header. This is a setting that is used by web browsers so that when a user enters the server address directly in their browser's address bar, the browser will automatically prefix the address with https, rather than first making a potentially insecure http request that gets redirected to https.

The default value of the header is max-age=300, which means the setting will be remembered for just 5 minutes (300 seconds = 5 minutes). This is useful when first setting up HTTPS on a new server, but for production use this value should be gradually increased to max-age=63072000 (2 years). See https://HSTSpreload.org/ for more guidance.

{
    "customHeaders": {
        "Strict-Transport-Security": "max-age=300"
    }
}

The isHttpsEnabled setting

This indicates to SQL Monitor whether or not HTTPS is actually being used. This controls a number of internal behaviours in SQL Monitor, such as security flags on various cookies. By the default the value will be automatically determined based on whether or not you've enabled the HTTPS endpoint for the built-in Kestrel webserver, as described above. However, if you've enabled HTTPS using some other means (for example, by hosting SQL Monitor in IIS, or using a reverse proxy such as NGINX) then you should explicitly set this value to true.

{
    "httpsSettings": {
        "isHttpsEnabled": true
    }
}

The shouldRedirectHttpToHttps setting

This indicates that all incoming HTTP requests should be redirected to HTTPS. The default value is true when isHttpsEnabled is true, or false when isHttpsEnabled is false. You might want to explicitly set this value to false if you're using a reverse proxy to deal with incoming HTTPS requests which are then transparently passed through to SQL Monitor using HTTP.

{
    "httpsSettings": {
        "shouldRedirectHttpToHttps": false
    }
}

The shouldDisplayHttpWarning setting

This indicates that a security warning about the use of HTTP should be displayed at the top of any page that's served over HTTP. If you want to disable this warning, set the value to false.

{
    "httpsSettings": {
        "shouldDisplayHttpWarning": false
    }
}

The disableInsecureTlsVersions setting

Set this value to true to disable the use of TLS 1.0 and TLS 1.1, which are considered insecure. SQL Monitor will use TLS 1.3 if supported by the underlying operating system, or fall back to TLS 1.2 if TLS 1.3 is unavailable. Alternatively, consider disabling TLS 1.0 and TLS 1.1 for the underlying Windows operating system.

{
    "httpsSettings": {
        "disableInsecureTlsVersions": true
    }
}




Didn't find what you were looking for?