Delphi 10.2 / FireDAC / SQL Server

[Martyn Spencer 0]

Having problems connecting to a SQL server. It used to work fine, but after changes by the database host, I am now having issues. I want to rule out anything I am doing. One major change is that they are now using encryption (which they should have used before). I have the OLE DB Driver for SQL server installed (the most recent version, I believe). The log is shown below:

 

================================
Connection definition parameters
================================
Database=XXXXXXX
User_Name=XXXXXXX
Password=XXXXXXX
Server=XXXXXXXX
Encrypt=Yes
ODBCAdvanced=TrustServerCertificate=yes
DriverID=MSSQL
Pooled=False
================================
FireDAC info
================================
Tool = RAD Studio 10.2
FireDAC = 16.0.0 (Build 88974)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
    Checking for ODBC driver [ODBC DRIVER 13 FOR SQL SERVER] ...
    Checking for ODBC driver [ODBC DRIVER 11 FOR SQL SERVER] ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 10.0] ...
    Checking for ODBC driver [SQL NATIVE CLIENT] ...
    Checking for ODBC driver [SQL SERVER] ...
      Found [SQL Server]
================================
Session info
================================
Failed to connect to DBMS !
[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

 

[Lars Fosdal 1792]

Firstly, I highly recommend using the new MSSQLODBC Driver 17, or the SQLNCLI 11 diver over the default one.

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/microsoft-odbc-driver-for-sql-server-on-windows?redirectedfrom=MSDN&view=sql-server-ver15

 

What generation is the SQL Server?  2008? 2012? 2016? 2017? 2019?

 

What did you do to set up the SSL support on the client side?

This article outlines the steps needed.
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-identity-and-access-management/single-sign-on/12-52-02/configuring/policy-server-configuration/user-directories/configure-a-microsoft-sql-server-database-connection-over-ssl.html

[Martyn Spencer 0]

Thanks for the links, Lars. I'll have a read. I do not control the server and I am only pulling a small amount of data, so I don't have a great deal of information. I'll look at what you have posted and find out what I can then post back here with either more info, or how it was solved.

[Lars Fosdal 1792]

Your client connection needs to set up SSL correctly to be able to connect to an SSL enabled SQL Server.

The driver installation is done on the client-side.