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

Microsoft – SQL Server – Error – Startup Error – Unable to load user-specified certificate (0x8009030d / 0x80092004

 

 

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
The SQL Server (MSSQLSERVER) service terminated with service-specific error 2148081668 (0x80092004).

The error messages listed above are due to in-correct permissions on SSL Certificates for the MS SQL Server Account.

If on MS Windows 2003, then use:

“X.509 Certificate Tool”
http://msdn.microsoft.com/en-us/library/aa529278.aspx

to assign permissions on certificate to MS SQL Server Start-up Account

If on MS Windows 2008 and above, then use:

MMC Certificate to assign permissions