firedac FireDAC MSSQL behaviour changed between 11.1 and 11.3

[Lars Fosdal 1793]

So, my project group with 25 applications, built 7.6M lines of code in 3 min 16 secs without a hitch on my Lenovo P16.

I was not surprised, having already tested the builds in my VM during the subscriber beta.

 

But - when I ran the applications against certain production systems - I got an error I had never seen before.

 

 

After numerous fiddlings with breakpoints, I discovered that the hosts that failed all were ip.ad.dr.es\instance instead of hostname\instance. When googling the error message, I eventually found references to Kerberos security failure and spelunking further, I found a suggestion to add the following to the connection string:

Quote

ODBCAdvanced=TrustServerCertificate=yes

In Delphi code, that means adding

FDConnection.Params.Values['ODBCAdvanced'] := 'TrustServerCertificate=yes';

to your connection initalization code.

 

Studying the release notes after 11.1, the 11.2 release states

Quote

FireDAC supports the latest Microsoft ODBC Driver for SQL Server (version 18).

which is the one I had installed, but perhaps the Kerberos authentication bit was ignored because the older FireDAC code didn't twiddle the right params?

 

Anyways - problem solved - No more The target principal name is incorrect for ip address host names.

[programmerdelphi2k 237]

28 minutes ago, Lars Fosdal said:

ip.ad.dr.es\instance instead of hostname\instance

maybe... because is waiting for a "IP" address as show the "ip.ad.dr.es" mask = nnn.nnn.nnn.nnn   not?

[weirdo12 21]

Thanks, Lars.

[Lars Fosdal 1793]

7 minutes ago, programmerdelphi2k said:

maybe... because is waiting for a "IP" address as show the "ip.ad.dr.es" mask = nnn.nnn.nnn.nnn   not?

No, it is related to the use of ip address instead of hostname which creates a trust failure, unless the client automatically approves the server side certificate for establishing a secure connection.

[Lars Fosdal 1793]

Pfft. Now I discovered that it is already documented at

https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Connect_to_Microsoft_SQL_Server_(FireDAC)

 

Quote

Using a Secure Connection

To use TLS/SSL encryption, pass the key-value pair TrustServerCertificate=yes on the value of the ODBCAdvanced parameter:

ODBCAdvanced=TrustServerCertificate=yes

There is just too much doc to plow 🙂

[Brian Evans 109]

Not really a Delphi issue. It is from breaking changes in Microsoft SQL Server ODBC driver 18.  Validating the certificate is just part of what happens when encrypt it set to yes which is now the default. Connection strings or certificate configurations need to be updated. 

 

Changes

• BREAKING CHANGE - Default Encrypt to Yes/Mandatory.
   

Ref: ODBC Driver 18.0 for SQL Server Released - Microsoft Community Hub

Edited March 15, 2023 by Brian Evans

[Uwe Raabe 2063]

Just yesterday I had a similar case where I had to add an Encrypt=No to the connection parameters for ODBC 18.