Test-DbaNetworkCertificate

Synopsis

Tests network certificate configuration and suitability for SQL Server instances

Description

Tests network certificate configuration for SQL Server instances in two ways.

Without the Thumbprint parameter (Way One): Calls Get-DbaNetworkConfiguration to retrieve
information about the currently configured certificate and available suitable certificates.
Returns a summary indicating whether the configured certificate is valid for the minimum
required days and whether any suitable certificates are available.

With the Thumbprint parameter (Way Two): Executes detailed certificate validation tests
on the target machine to determine if the specified certificate is suitable for SQL Server
network encryption. Returns individual test results for each requirement, making it easy
to identify which specific tests failed.

The certificate validation logic is aligned with Get-DbaNetworkConfiguration to ensure
consistent behavior. For details on certificate requirements, see
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements

Syntax

Test-DbaNetworkCertificate
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-Credential] <PSCredential>]
    [[-Thumbprint] <String>]
    [[-MinimumValidDays] <Int32>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaNetworkCertificate -SqlInstance sql2019

Tests the configured network certificate for the default instance on sql2019.
Returns whether the configured certificate is valid and whether suitable certificates are available.

Example: 2

PS C:\> Test-DbaNetworkCertificate -SqlInstance sql2019 -MinimumValidDays 30

Tests the network certificate configuration for sql2019, requiring certificates to be valid
for at least 30 more days.

Example: 3

PS C:\> Test-DbaNetworkCertificate -SqlInstance sql2019 -Thumbprint 1223FB1ACBCA44D3EE9640F81B6BA14A92F3D6E2

Tests whether the certificate with the given thumbprint is suitable for SQL Server network
encryption on sql2019. Returns detailed test results for each requirement.

Example: 4

PS C:\> Test-DbaNetworkCertificate -SqlInstance sql2019 -Thumbprint 1223FB1ACBCA44D3EE9640F81B6BA14A92F3D6E2 -MinimumValidDays 30

Tests whether the certificate is suitable for sql2019 and will remain valid for at least 30 days.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Optional Parameters

-Credential

Credential object used to connect to the Computer as a different user.

-Thumbprint

The thumbprint of a specific certificate to test for suitability (Way Two).
When specified, the command performs detailed validation of that certificate and returns
individual test results for each requirement.
When omitted, the command checks the configured certificate and available suitable
certificates using Get-DbaNetworkConfiguration (Way One).

-MinimumValidDays

The minimum number of days the certificate must be valid from today.
A certificate expiring within fewer than this many days will not be considered valid.
Defaults to 0, meaning the certificate just needs to be currently valid.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Without -Thumbprint (Way One), returns one object per instance with:

• ComputerName: Computer name of the SQL Server instance
• InstanceName: SQL Server instance name
• SqlInstance: Full SQL Server instance name (computer\instance format)
• ConfiguredCertificateValid: Boolean indicating if the configured certificate is valid for at least MinimumValidDays
• ConfiguredCertificateThumbprint: Thumbprint of the configured certificate, or $null if none is configured
• ConfiguredCertificateExpires: Expiration date of the configured certificate, or $null if none is configured
• ConfiguredCertificateDaysValid: Number of days until the configured certificate expires, or $null if none is configured
• SuitableCertificateAvailable: Boolean indicating if at least one suitable certificate is available for the minimum valid days
• SuitableCertificateCount: Number of suitable certificates available for the minimum valid days
• SuitableCertificates: Array of suitable certificate objects (Thumbprint, FriendlyName, NotBefore, NotAfter, DaysValid)

With -Thumbprint (Way Two), returns one object per instance with:

• ComputerName: Computer name of the SQL Server instance
• InstanceName: SQL Server instance name
• SqlInstance: Full SQL Server instance name (computer\instance format)
• Thumbprint: The thumbprint of the tested certificate
• IsSuitable: Boolean indicating if the certificate passes all validation tests
• CertificateFound: Boolean indicating if the certificate was found in LocalMachine\My
• KeyUsagesValid: Boolean indicating if the certificate has the required key usages (DigitalSignature and KeyEncipherment)
• DnsNamesValid: Boolean indicating if the certificate’s DNS names include the server’s network name
• PrivateKeyValid: Boolean indicating if the private key is RSACryptoServiceProvider with KeyNumber Exchange
• PublicKeyValid: Boolean indicating if the public key is RSA with at least 2048 bits
• SignatureAlgorithmValid: Boolean indicating if the signature algorithm is SHA-256, SHA-384, or SHA-512
• EnhancedKeyUsageValid: Boolean indicating if the certificate has the Server Authentication enhanced key usage
• ValidityPeriodOk: Boolean indicating if the certificate is currently valid and valid for at least MinimumValidDays
• KeyUsages: The actual key usage flags value
• DnsNames: Array of DNS names from the certificate
• PrivateKeyType: Full type name of the private key object
• PrivateKeyNumber: Key number from the CspKeyContainerInfo
• PublicKeySize: Public key size in bits
• PublicKeyAlgorithm: Public key algorithm friendly name
• SignatureAlgorithm: Signature algorithm friendly name
• EnhancedKeyUsageList: Array of enhanced key usage friendly names
• NotBefore: Certificate validity start date
• NotAfter: Certificate validity end date (expiration)
• DaysValid: Number of days until the certificate expires