PowerBuilder and SQL Server: Replacing SQL Native Client with MSOLEDBSQL

Why SNC has to go

Microsoft calls SQL Server 2012's Native Client 11 “the last available version”. Its support policy lists connections to SQL Server 2008 through 2014 and Azure SQL Database, and says the remaining lifecycle exception does not cover “using SQL Native Client 11.0 as a driver for database applications”.

Appeon's documentation still lists the SNC interface for SQL Server 2016 to 2025, so the two vendors' documents do not match. On the Appeon Community, Appeon's Armeen Mazda backs the move: “You should use the new native client replacement as John said, and also it is what Microsoft recommends.”

What changes

In PowerScript:

SQLCA.DBMS       = "MSOLEDBSQL SQL Server"
SQLCA.ServerName = "sql01.example.com"
SQLCA.DBParm     = "Provider='MSOLEDBSQL19',Database='sales',Encrypt=1"

Appeon's connection guide notes that version 19 syntax also works with the version 18 driver when version 19 is not installed, so a PC without version 19 runs version 18 without any error: check the driver version on each client. The two versions install side by side, and the x64 msoledbsql.msi also installs the 32-bit driver. Appeon's Runtime Packager does not include it: distribute the MSI yourself, with IACCEPTMSOLEDBSQLLICENSETERMS=YES for a silent install.

Failure 1: the certificate chain error

The connection can now fail with:

SSL Provider: The certificate chain was issued by an authority
that is not trusted.

Version 19 turns encryption on by default (Encrypt is Mandatory) and validates the server's certificate (TrustServerCertificate is no), so a server certificate the client does not trust fails the check. Microsoft's troubleshooting article gives the fixes in this order:

Three PowerBuilder details decide whether a fix takes effect:

• The TrustServerCertificate DBParm is ignored unless Encrypt=1.
• The registry wins. If Flag2 under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI19.0\GeneralFlags is 0, the driver validates the certificate whatever the DBParm says: it “chooses the most secure option between the registry and the connection property/keyword settings”. Appeon's fix is to set Flag2 to 1 or provide a verifiable certificate; installers before 19.4.1 could copy a 0 over from version 18.
• Appeon documents Encrypt=0 as the DBParm default, for driver 19.x too, with “No encryption occurs”, while the driver's own default is Mandatory. Neither vendor says what PowerBuilder passes to the driver when the DBParm is 0, so set Encrypt explicitly and test it.

Encrypt=2 selects Strict encryption (TDS 8.0), which needs SQL Server 2022 or later; the driver then always validates the certificate and ignores TrustServerCertificate. For TLS 1.3, Appeon lists driver 19.2.0 or later and SQL Server 2022 build 16.0.4025.1 or later.

Failure 2: updates on tables with triggers

An update on a table whose trigger changes other tables can fail with “Row changed between retrieve and update”. With a trigger, the number of affected rows that reaches PowerBuilder “depends on the driver”, Appeon says. Its troubleshooting guide lists the problem for the ADO.NET, OLE DB and SNC interfaces, and a 2025 R2 user on the Appeon Community hit it after moving to MSOLEDBSQL 19.

Two fixes:

• In the trigger: SET NOCOUNT ON. Microsoft's guide to updating OLE DB applications says a statement that fires a trigger while NOCOUNT is off can return different results and an error: “To resolve this error, set NOCOUNT ON in the trigger.”
• In PowerBuilder: RecheckRows=1. Appeon's troubleshooting guide says it “solves the problem”. It runs SELECT @@ROWCOUNT after each change, so Appeon advises setting it only when required and resetting it to 0 afterwards.

Check after the switch

The order of work

1. List the applications on SNC, their SQL Server versions and the Windows versions of their clients.
2. Install driver 19.x with the Visual C++ Redistributable on one test client, and check the registry flags.
3. Change the DBMS and the Provider, and set Encrypt and DisableBind explicitly.
4. Fix the server certificate, or agree an end date for any temporary trust setting.
5. Test updates on every table with triggers, then inserts with defaults or identity columns, then datetime updates.
6. Roll the driver out with your own installer, then switch the applications.

Moving a large application? We do this as fixed-scope work for one defined problem: the switch, the trigger, binding and date checks, and a written list of what changed.