4

I created three certificate by using openssl

  1. Root CA certificate
  2. Server certificate
  3. Client certificate

Server side configuration :

I installed server.cer(peronal folder) and RootCA(trust root certification authority folder) on server machine.Then i attached the server certificate to MSSSQL(SQL Configuration manager->MSSQL properties->certificate dropdown). Made the Force Encryption to No and restarted the service.

Client side configuration:

I installed client.cer(peronal folder) and RootCA(trust root certification authority folder).

Below are the steps to create RootCA(CertAuthority) and server.cert and client.crt :

openssl genrsa -des3 -out CertAuthority.key 4096
openssl req -new -x509 -days 3650 -key CertAuthority.key -out CertAuthority.crt
openssl req -new -sha256 -key CertAuthority.key -out server.csr
openssl x509 -req -days 3650 -in server.csr -CA CertAuthority.crt -CAkey CertAuthority.key -set_serial 01 -out server.crt
openssl req -new -sha256 -key CertAuthority.key -out client.csr
openssl x509 -req -days 3650 -in client.csr -CA CertAuthority.crt -CAkey CertAuthority.key -set_serial 01 -out client.crt

Details given at the time of CertAuthority.crt creation

Common Name : asia.com
Country : IN

remaining fields are left blank

Details given at the time of server.crt creation

Common Name : Myserver.asia.com
Country : IN

remaining fields are left blank

Details given at the time of Client.crt creation

Common Name : Myclient.asia.com
Country : IN

remaining fields are left blank

Here is the connection string i am using for sqlclient

Data Source=Myserver;Initial Catalog=MyDB;User ID=sa;Password=;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False

Whenever i am trying to connect via SQLOLEDB provider by using "Use Encryption For Data=True" as an alternate of Encrypt and TrustServerCertificate property, I am able to establish SSL connection successfully.

But by using SqlConnection i am getting following error: 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.)

Can somebody help me out in this regard. Thanks

jww
  • 97,681
  • 90
  • 411
  • 885
Nihal Kumar
  • 305
  • 3
  • 16
  • Check out this: http://stackoverflow.com/questions/37734311/mssql-with-ssl-the-target-principal-name-is-incorrect – monstertjie_za Aug 10 '16 at 08:58
  • I already tried this and didn't work – Nihal Kumar Aug 10 '16 at 09:03
  • [The target principal name is incorrect. Cannot generate SSPI context](https://blogs.msdn.microsoft.com/meer_alam/2015/05/10/the-target-principal-name-is-incorrect-cannot-generate-sspi-context/) on MSDN. It takes you through the troubleshooting steps. – jww Aug 10 '16 at 11:54
  • *"`Common Name : Myserver.asia.com` ... remaining fields are left blank"* is probably wrong. Also see [How do you sign Certificate Signing Request with your Certification Authority](http://stackoverflow.com/a/21340898/608639) and [How to create a self-signed certificate with openssl?](http://stackoverflow.com/q/10175812/608639) It provides a lot of background information on X.509 server certificates, and where the various rules come from. – jww Aug 10 '16 at 11:56
  • *`Common Name : Myclient.asia.com`* may be wrong. Also see [Step-by-Step Guide to Mapping Certificates to User Accounts](http://msdn.microsoft.com/en-us/library/bb742438.aspx) on MSDN. Microsoft uses special sauce for identifying principals (UPNs) in client certificates. – jww Aug 10 '16 at 12:02
  • Review the SPNs for that service and verify that the DNS suffix list is not missing entries. I suspect that Target Principal Name might refer to SPN. – Michael Keleher Aug 28 '16 at 22:23

2 Answers2

7

Add this parameter for the connection string: TrustServerCertificate=true

Andy
  • 49,085
  • 60
  • 166
  • 233
Vishe
  • 3,383
  • 1
  • 24
  • 23
  • 2
    If I add this parameter then it will work will all the certificates which i don't want. i.e If client has any certificate signed by same CA then it will work. I want the client to be able to communicate only if they have proper CA certificate. – Nihal Kumar Nov 07 '16 at 10:17
0

When generating DBContext using the Scaffolding, if you are getting the error while connection "provider ssl provider error 0 - the target principal name is incorrect" use this parameter for the connection string: TrustServerCertificate=true

AS the server connection is based on the certificate errors.

mohammad- javed
  • 25
  • 1