Programming => Databases => Topic started by: DirkJ on June 16, 2021, 02:38:13 pm
Title: [Solved] Encrypted Connection to MS-SQL Server 2019
Post by: DirkJ 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:
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
Title: Re: Encrypted Connection to MS-SQL Server 2019
Post by: Thaddy 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.
Title: Re: Encrypted Connection to MS-SQL Server 2019
Post by: DirkJ 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?
Title: Re: Encrypted Connection to MS-SQL Server 2019
Post by: LacaK 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))
Title: Re: Encrypted Connection to MS-SQL Server 2019
Post by: DirkJ 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