SQL Server :- “Provider: SSL Provider, error: 0 – The target principal name is incorrect”

Background

Playing around with SSL Certs on SQL Server and ran into a nice well informed error.

 

Error Message

Image

Textual


===================================

Cannot connect to DB01.

===================================

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893022&LinkId=20476

------------------------------
Server Name: DB01
Error Number: -2146893022
Severity: 20
State: 0

 

Remediation

Client

SQL Server Management Studio ( SSMS )

Outline

  1. Access the “Connection Properties” tab
    • Determine whether you really want to Encrypt Connection
      • Based on that determination, please check or uncheck “Encrypt” connection
  2. Access the “Additional Connection Parameters” tab
    • In the “Enter additional connection string parameters” group box
      • Enter “TrustServerCertificate=Yes

Screen Shot

Screen Shot – Tab – Connection Properties

Screen Shot – Tab – Additional Connection Parameters

 

SQL Server Configuration Manager

Outline

  1. Launch SQL Server Configuration Manager
  2. Access each bitness specific configuration
    • SQL Native Client [version] Configuration (32bit)
    • SQL Native Client [version] Configuration
  3. Right click on the configuration node
  4. From the drop down menu, please select Properties
  5. The Flags window appears
    • Set “Trust Server Certificate” to YES

Screen Shot

SQL Native Client [version] Configuration (32it) // SQL Native Client [version] Configuration

Flags

 

Listening

As always there is a lot more to it.

So please choose one of these two:

  1. Kenny Chesney Kid Rock- Luckenback, Texas
    Got us feuding like the Hatfields and McCoys
    This successful life we’re living’s

    Out in Luckenbach, Texas, ain’t nobody feeling no pain
    Ain’t nobody feeling no pain
    The way we are doing it at 4 in the morning
    In Detroit Michigan
    Link
  2. Ghetto Boys – My Mind is playing tricks on me
    I feel like I am the one doing coke …
    Now she is back with her mother
    This year Halloween fell on a weekend
    Me and Geto Boys are trick-or-treating
    Link

 

certutil.exe – View Certs

Background

Let us view certs information from the command line using certutil.exe

Script

Scenario

Scenario :- Certificate in Store

Outline

Using certutil.exe access specific Certificate store and return information for the specified certificate.

Syntax


certutil.exe -v -store [store] [certificateID]

Sample

Sample :- Display Cert info on console

@echo off

setlocal

set "_certID=dbHRDB.lab.org"

certutil.exe -v -store my "%_certID%"

endlocal

Sample :- Display Cert info and send output to a file

@echo off

setlocal

set "_certID=dbHRDB.lab.org"

if not exist log mkdir log

certutil.exe -v -store my "%_certID%" > log\certinfo.log

endlocal

Scenario :- Certificate in File System

Outline

Using certutil.exe read file in File System and return file info.

  1. Reach into the File System and get certificate information.
  2. Pass along certificate password if secured using -p
  3. Pass along -v for verbose mode

Syntax


certutil.exe -p [certPassword] -v [certificateFile] 

Sample


@echo off

setlocal

set "_certFolder=D:\Microsoft\SQLServer\certificates\SSL\20180412"

set "_certificate=hrdbLocal.pfx"

set "_certificateFullname=%_certFolder%\%_certificate%"

set "_certPassword=lovelyDay"

certutil.exe -p "%_certPassword%" -v "%_certificateFullname%" 

endlocal



Output

Sample Output


my "Personal"
================ Certificate 2 ================
X509 Certificate:
Version: 3
Issuer:
    CN=InCommon RSA Server CA
    OU=InCommon
    O=Internet2
    L=Ann Arbor
    S=MI
    C=US
  Name Hash(sha1): 69836d535691d9fcb786abfb77e139c40a56f422
  Name Hash(md5): b2b952036d94176073a235e65f5e308e

 NotBefore: 4/12/2018 12:00 AM
 NotAfter: 4/11/2020 11:59 PM

Subject:
    CN=dbHRDB.lab.org

    2.5.29.15: Flags = 1(Critical), Length = 4
    Key Usage
        Digital Signature, Key Encipherment (a0)

    2.5.29.37: Flags = 0, Length = 16
    Enhanced Key Usage
        Server Authentication (1.3.6.1.5.5.7.3.1)
        Client Authentication (1.3.6.1.5.5.7.3.2)


    2.5.29.17: Flags = 0, Length = 54
    Subject Alternative Name
        DNS Name=dbHRDBExternal.lab.org


  CERT_KEY_PROV_INFO_PROP_ID(2):
    Key Container = {8BE90DBA-B43E-49A8-9000-8B4E5D8CF47E}
  Unique container name: a0feaa75dadd8b70e2c24fb889f45701_f9e27b6e-7c79-4f5d-abaa-b270baf4ddc6
    Provider = Microsoft Enhanced Cryptographic Provider v1.0
    ProviderType = 1
  Flags = 20 (32)
    CRYPT_MACHINE_KEYSET -- 20 (32)
    KeySpec = 1 -- AT_KEYEXCHANGE


  D:AI(A;;GAGR;;;BA)(A;;GAGR;;;SY)(A;;GR;;;S-1-5-5-0-2933760447)

    Allow Full Control	BUILTIN\Administrators
    Allow Full Control	NT AUTHORITY\SYSTEM
    Allow Read	NT AUTHORITY\LogonSessionId_0_2933760447


Private Key:
  PRIVATEKEYBLOB
  Version: 2
  aiKeyAlg: 0xa400
    CALG_RSA_KEYX
    Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE
    Algorithm Type: 0x400(2) ALG_TYPE_RSA
    Algorithm Sub-id: 0x0(0) ALG_SID_RSA_ANY
  0000  52 53 41 32                                        RSA2
  0000  ...
  048c
Encryption test passed
CertUtil: -store command completed successfully.

Explanation

  1. Issuer
    • InCommon RSA Server CA
  2. Date Range
    • NotBefore
    • NotAfter
  3. Subject
    • CN=dbHRDB.lab.org
  4. Key Usage
    • Digital Signature, Key Encipherment
      • Digital Signature
        • Sign code, documents, etc
      • Key Encipherment
        • Encrypt data
  5. Enhanced Key Usage
    • Server Authentication (1.3.6.1.5.5.7.3.1)
    • Client Authentication (1.3.6.1.5.5.7.3.2)
  6. Subject Alternative Name
    • DNS Name=dbHRDBExternal.lab.org
      • Other acceptable/published names that clients can use to refer to server and server will not fail name validation
  7. Cryto Machine keyset
    • KeySpec = 1 — AT_KEYEXCHANGE
  8. Private Key
    • We have private key
  9. Encryption test passed