• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • I can't establish a connection with Powerbuilder 2022 & MSOLEDBSQL19 using a self-signed certificate

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved I can't establish a connection with Powerbuilder 2022 & MSOLEDBSQL19 using a self-signed certificate

  1. Issue
0
Votes
Undo
  1. Christophe Feyte
    Christophe Feyte
  2. PowerBuilder
  3. Tuesday, 20 December 2022 10:31 AM UTC

Hi, I am having trouble getting the new MSOLEDBSQL19 driver (v19.2.0.0) to work with PowerBuilder2022 build 1892.
I generated a self-signed certificate for SQL Server 2019 using the PowerShell script provided at the bottom of this page: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver15

In SQL Server Configuration Manager, I forced the ForceEncryption option to TRUE, and selected my self-signed certificate. And after that, I restarted the SQL service successfully.

Then I exported the certificate and installed it on my computer.
When I try to set up the database in PowerBuilder, I get the following error message:

(SSL Provider: The target principal name is incorrect)

I don't understand what I missed or what more needs to be done.
Here is the database connection syntax:
// Profile MyDataBase
SQLCA.DBMS = "MSOLEDBSQL SQL Server
SQLCA.ServerName = "MYSERVER\SQL2019"
SQLCA.AutoCommit = False
SQLCA.DBParm = "TrustedConnection=1,Database='MyDataBase',Encrypt=1,TrustServerCertificate=1,Provider='MSOLEDBSQL19'"

If I use the old MSOLEDBSQL driver (v18.6.4.0) it works.

Loss of an OCX
Saved data in psr file?
Who is viewing this page
Tim Moulaison
Accepted Answer
Christophe Feyte
Christophe Feyte Accepted Answer Pending Moderation
  1. Wednesday, 21 December 2022 09:02 AM UTC
  2. PowerBuilder
  3. # Permalink

Hello, I managed to solve my problem by creating a new self-signed certificate by putting several names for my server in the powershell command.

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=MYSERVER.domain.com" `
-DnsName 'MYSERVER','MYSERVER.domain.com','localhost' `
-KeyAlgorithm "RSA" -KeyLength 2048 -HashAlgorithm "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" `
-NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider" `
-CertStoreLocation "cert:\LocalMachine\My"

 

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 21 December 2022 16:39 PM UTC
Thanks for sharing the solution!
  1. Helpful
There are no comments made yet.
Responses (3)
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 21 December 2022 01:40 AM UTC
  2. PowerBuilder
  3. # 1

Hi Christophe,

For the TrustServerCertificate issue, it is related to the OLE DB driver 19, please note the application settings never reduce the level of security set in the registry, please refer to the article below for details.
https://docs.microsoft.com/en-us/sql/connect/oledb/features/encryption-and-certificate-validation?view=sql-server-ver16

There are two solutions for this issue:
1. Refer to the above article to change {base_registry_key}\Flag2 from 0 to 1, please try restart machine after registry changed.
2. Refer to the article below to install a valid certificate on SQL Server and set SQLCA.ServerName to use a valid domain name that matches the certificate.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16

Regards,
Kai

Comment
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 20 December 2022 15:20 PM UTC
  2. PowerBuilder
  3. # 2

Are you sure that the server name in the certificate and what you put in sqlca.ServerName are the same?

Comment
Christophe Feyte
  1. Christophe Feyte
  2. Tuesday, 20 December 2022 15:30 PM UTC
Hello, in sqlca.ServerName i have the name of the sql server instance (MyServer\SQL2019) and in the certificate i just have the name of the server (MyServer).
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Tuesday, 20 December 2022 15:43 PM UTC
I don't know if this will work but you could try creating the certificate with MyServer\SQL2019 as the server name.
  1. Helpful
Christophe Feyte
  1. Christophe Feyte
  2. Tuesday, 20 December 2022 16:03 PM UTC
Unfortunately it didn't work.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.