Self-signed certificate - can't get SQL Server client to trust it

Question

I have created a self-signed certificate for TLS 1.2 for my SQL Server 2014 SP3 server.

Trying to get another computer to connect and I'm getting errors that

The certificate chain was issued by an authority that is not trusted

However, I have imported the self-signed certificate into Trusted Root Certification Authorities on that computer... to no avail. I'm still getting the error.

Am I missing something obvious to make this work?

Answer 1

There are some properties that need to be in the certificate for it to work correctly. Here's a procedure that I used when running up internal SQL Servers...

On the server:

• Run the below PowerShell script in a Run-As-Administrator PowerShell prompt.
• In "Manage Machine Certificates" > Personal > Certificates > $name > All Tasks > Manage Private Keys...
  • Add the same Windows account used to run the SQL Server service with READ permissions.
    • WARNING: It wants to add with Full Control by default.
• In SQL Server Configuration Manager
  • In Protocols for $instanceName > Properties
    • On the Certificate tab, select your certificate's friendly name.
      • If it's not visible then either the permissions or key properties are not correct.
    • On the Flags tab, set Force Encryption to Yes.
  • In SQL Server Services, right-click your SQL Server instance > Restart.

On the client(s):

• Import the .crt file:
  • Right-click on the .crt file and select Install Certificate
  • Choose: Current User
  • Choose: Place all certificates in the following store
  • Select: Trusted Root Certification Authorities
• In SQL Server Management Studio:
  • On the Connect to Server dialog, expand Options
    • On the Connection Properties tab tick Encrypt Connection
  • On the Login tab the Server name must match one of the names or IP addresses listed in the -DnsName parameter.
    • If a match cannot be found you'll get "SQL Provider, error: 0 - The target principal name is incorrect."
    • SQL Logins can use any name or IP address that matches a -DnsName parameter.
    • If you're using Windows Authentication you'll likely only be able to connect with short host names, or fully qualified names that match your directory. IP address connections will probably fail with "The login is from an untrusted domain and cannot be used with Integrated authentication."

After connecting, right-click on the server node > Properties and click "View connection properties." In Connection > Encrypted it should say "Yes."

$3years = [DateTime]::UtcNow.AddYears(3)
$enhancedKeyUsageServerAuthentication = @("2.5.29.37={text}1.3.6.1.5.5.7.3.1")
$name = $env:COMPUTERNAME.ToLower()
$fqdn = "$($name).example.com"
$ip = '192.168.0.79' # for example
$cert = New-SelfSignedCertificate `
    -CertStoreLocation 'cert:\LocalMachine\My' `
    -DnsName $fqdn,$name,$ip,"localhost","127.0.0.1" `
    -FriendlyName "$($name)-sql-server" `
    -Hash 'SHA256' `
    -KeyAlgorithm 'RSA' `
    -KeyExportPolicy ExportableEncrypted `
    -KeySpec KeyExchange `
    -KeyFriendlyName "$($name)-sql-server" `
    -KeyLength 2048 `
    -KeyUsage DigitalSignature,KeyEncipherment,DataEncipherment `
    -NotAfter $3years `
    -Provider 'Microsoft RSA SChannel Cryptographic Provider' `
    -Subject $name `
    -TextExtension $enhancedKeyUsageServerAuthentication `
    -Type SSLServerAuthentication

$cert | Export-Certificate -Type CERT -FilePath $name-sql-server.crt