New SMO Library and Certificate Trust

[potatoqualitee]

Now that we're using the new SMO library, you may encounter an error "The target principal name is incorrect" or "The certificate chain was issued by an authority that is not trusted" or "The received certificate has expired"

This is new and expected behavior if you are using encryption but not using a hostname in your certificate or your certificate chain is not trusted.

From red gate docs:

The Microsoft.Data.SqlClient that our tools use to connect to SQL Server has been updated and now defaults to Trust Server Certificate = False in situations where previously defaulted to true.

From our docs:

Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail. For information about support for certificates whose subject starts with a wildcard character (*), see Accepted wildcards used by server certificates for server authentication.

To resolve this issue, you can:

• Add the hostname to your certificate SAN (recommended) or fix/trust your certificate chain
• Use our config to revert to the previous behavior: Set-DbatoolsConfig -FullName 'sql.connection.trustcert' -Value $true -Register
• Stop forcing encrypted communications (not recommended)

If this doesn't work for you, first, let us know in this issue and second, please check your Kerberos settings using Test-DbaSpn and set them as necessary. The new SMO library, which will eventually be part of SQL Server Management Studio, seems to be a bit more strict about Kerberos as well.

[niphlod]

or, stop using netbios names ^_^ .
And, remember, must be done for aliases, too.
And, for AG listeners, too.
This is the same thing as accessing an https site: remember that you use encryption to:

• make sure noone can read
• make sure you connect to the right server and not an impostor

all things considered it's a good thing MS did by forcing good behaviour to everybody.

And yes, I'm partial to this because for the longest time I've been the only guy in #dbatools that needed to type the FQDN every time, but that's another story ^_^

[wondisha]

Works for me by reverting to the previous behavior

[bilodeauj]

What about using a wild card certificate? I imported the cert and configured it as the ssl cert to user for sql server using the following code

$ComputerName  = $env:COMPUTERNAME
$SQLInstance = $ComputerName
$ReadPFX = Get-PfxCertificate -FilePath $CertificatePath
Get-DbaComputerCertificate -ComputerName $ComputerName |
    Where {($_.Name -eq $ReadPFX.FriendlyName) -and ($_.NotBefore -lt $(Get-Date))} |
        Sort NotBefore -Descending | Select -First 1 |
            Set-DbaNetworkCertificate -SqlInstance $SQLInstance -RestartService -Verbose

Enable-DbaForceNetworkEncryption -SqlInstance $SQLInstance -Verbose

however any time i try and use any dbatools command like Get-DbaConnection -SqlInstance $SqlInstance i get an error such as

WARNING: [16:34:38][Get-DbaConnection] Failure | The target principal name is incorrect

[bilodeauj]

rereading the this whole tread, i think i may have figured out my issues. A number of years ago our organization decided to change it's name as part of a company rebranding campaign. At the time they decided to create a domain alias, and all our external facing sites and ressources are using that alias with the new domain name, however in Active Directory we still use the old domain name, which means that the server fqdn has a different domain listed than that of our wildcard cert.

Based on my understanding the best option would be for us to get a new SAN cert with the servers fqdn, so that it exactly matches the server, or for me to use Set-DbatoolsConfig -FullName 'sql.connection.trustcert' -Value $true -Register to force it to trust the wildcard cert.