Recent

Author Topic: [Solved] Encrypted Connection to MS-SQL Server 2019  (Read 3509 times)

DirkJ

  • Newbie
  • Posts: 3
[Solved] Encrypted Connection to MS-SQL Server 2019
« on: June 16, 2021, 02:38:13 pm »
Hello,

has anyone ever managed to establish an encrypted connection to a MS-SQL Server 2019 using TMSSQLConnection. When I try this I always get the error message:
Code: Diff  [Select][+][-]
  1. TMSSQLConnection Error 20002 Adaptive Server connection failed.

In the event log on the MS-SQL Server it says:
Code: Diff  [Select][+][-]
  1. Event ID 17828
  2. The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.
I am using the latest DBLib.dll 1.1 from ftp://ftp.freepascal.org/fpc/contrib/windows/.

The connection via TODBCConnection works without problems.

Dirk

« Last Edit: June 17, 2021, 03:52:41 pm by DirkJ »

Thaddy

  • Hero Member
  • *****
  • Posts: 10796
Re: Encrypted Connection to MS-SQL Server 2019
« Reply #1 on: June 16, 2021, 03:11:28 pm »
You are probably using a depreated protocol. (any SSL?) You MUST use TLS1.1 or higher. The component supports that, unless you have a crazy old install of OpenSSL and the likes.
« Last Edit: June 16, 2021, 03:14:04 pm by Thaddy »

DirkJ

  • Newbie
  • Posts: 3
Re: Encrypted Connection to MS-SQL Server 2019
« Reply #2 on: June 16, 2021, 03:33:07 pm »
How can I select TLS version. I did not find any property in the component.

I put the two DLLs libeay32.dll and ssleay32.ddl from the OpenSSL package 1.0.2u together with the DBLib.dll in the application directory.

Is it necessary to install OpenSSL on the computer?

LacaK

  • Hero Member
  • *****
  • Posts: 635
Re: Encrypted Connection to MS-SQL Server 2019
« Reply #3 on: June 17, 2021, 07:44:44 am »
I never used encrypted connection, but you can consider these things:
- dblib.dll is precompiled using TDS ver. 7.3 + in MSSQLConnection there is hardcoded version 7.3 (MS SQL Server 2008+). I do not know if encrypted connection does not require higher 7.4 TDS version?
In that case you can play with environment variable TDSVER: https://www.freetds.org/userguide/freetdsconf.html
You can also enable loging for FreeTDS so you can look into log file what precedees error.
- FreeTDS dblib.dll is compiled with some defines enabled and some disabled; may be that encryption support is just not enabled by default - that seems to me the most likely.
- there was already released FreeTDS ver 1.2 and is ready ver 1.3 so may be that encryption support was completed in these later versions. (but according to NEWS file encryption support was added in 0.64 version; (using either GnuTLS or OpenSSL))

DirkJ

  • Newbie
  • Posts: 3
Re: Encrypted Connection to MS-SQL Server 2019
« Reply #4 on: June 17, 2021, 03:52:17 pm »
I have solved the problem. In case someone else has the problem:

The dblib.dll on the ftp://ftp.freepascal.org/fpc/contrib/windows/ page does not seem to support encrypted connections.

I have now renamed the file sybdb.dll from the FreeTDS 1.3 package (vs2017_64-Branch-1_3.zip) to DBLib.dll and copied it to the application directory in addition to the files libcrypto-1_1.dll and libssl-1_1.dll.

A current FreeTDS build for Windows can be downloaded from https://ci.appveyor.com/project/FreeTDS/freetds (select a build and then click on artifacts).

The other two DLL files are from the openssl-1.1.1k.zip package from https://kb.firedaemon.com/support/solutions/articles/4000121705
« Last Edit: June 17, 2021, 03:54:07 pm by DirkJ »

 

TinyPortal © 2005-2018