TLS 1.2 Support for SQL Server

Use this forum if you want to suggest a new feature to hMailServer. Before posting, please search the forum to confirm that it has not already been suggested.
Post Reply
matthewwanders
New user
New user
Posts: 2
Joined: 2016-02-09 21:58

TLS 1.2 Support for SQL Server

Post by matthewwanders » 2017-07-10 19:12

The current build of hMailServer utilised the oledb provider for database connections (with a fail-over in the code for sqlncli). Neither of these providers support TLS 1.2 for connections to SQL Server (2008, 20012, 2014 or 2016).

Microsoft has since added support for TLS 1.2 connectson to SQL server, this occured last year. Please refer to Micrisoft Suport website article 3135244.

I would like to request that hMailServer be updated to also support this. I beleive this will mean making use fo alternative providers than currently implemeneted.

Alternatively, .ini config to allow us to tell which provider to use for connections would be useful. Where that the case, we'd stop using the "SQLNCLI" provider (which is the SQL 2005 native client provider, which will never support TLS 1.2) and put in "SQLNCLI11" (which is the SQL 2012 Native Client, who's latest version being 11.3, supports TLS 1.2 connections to SQL server).

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2017-07-10 23:41

Whilst I understand and agree that this should be added, this doesn't affect those of us using MySQL.
I'd like to see the default database changed back to a MySQL database now that hMailserver (since 5.4) is back to Open Source. Then support for the MS SQL CE (which is a variant of SQl Server 2005) could be dropped. Newer operating systems don't support this old database anyway.

In saying that, if you need to secure a connection to your database from an application on a single machine (or on a LAN) then you have bigger issues
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Gimli
New user
New user
Posts: 27
Joined: 2013-10-09 20:17

Re: TLS 1.2 Support for SQL Server

Post by Gimli » 2017-08-06 18:19

mattg wrote:In saying that, if you need to secure a connection to your database from an application on a single machine (or on a LAN) then you have bigger issues
One of the issues this causes is that if you run a web interface to your hmailserver on the same Windows server using IIS, you can't disable TLS 1.0 and TLS 1.1 because then the hmailserver connection to the SQL database breaks. It's it's not just an internal connection security issue but an external one as well.

I too would like to see hmailserver's SQL server client updated to one that supports TLS 1.2.

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2017-08-06 19:14

If you need this, you need to patch the file ADOConnection.cpp Line 102

Now it looks like this:

Code: Select all

      
      String sConnectionString = "Provider=" + sProvider + ";";
      sConnectionString.append("Server=" + sServer + ";");
      sConnectionString.append("Initial Catalog=" + sDatabase + ";");
Add this to it:
sConnectionString.append("Trusted_Connection=yes;Encrypt=yes;";");

So it should like this:

Code: Select all

      
      String sConnectionString = "Provider=" + sProvider + ";";
      sConnectionString.append("Server=" + sServer + ";");
      sConnectionString.append("Initial Catalog=" + sDatabase + ";");
      sConnectionString.append("Trusted_Connection=yes;Encrypt=yes;";");      
Compile hMailServer.exe, register the service (hmailserver.exe /Register) and restart the service.

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2017-08-07 01:03

Dravion

Can you add these little fixes (like this one) to individual pull requests on github please.
It would be great to get them added to official source
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2017-08-07 10:32

Hi Mattg,

I think its better to file an Issue instead of a pull request. There should be an Option in the Installwizzard or at least a hmailserver.ini config param to turn this feature on or off

I filed a GitHub issue for it:
https://github.com/hmailserver/hmailserver/issues/229

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2017-08-07 10:36

All good :)
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2018-11-12 10:41

Martin replied on Github and things arent that easy (in the case of Microsoft SQL-Server 2008/2008_R2/2012/2014/2016/2017 - not the SQL CE Version). The Client-Connect Library (which hMailServer internally uses) needs to be a recent version and properly configured or all connections will stay (silently) unencrypted without further notification.

However:
TLS 1.2 for full MS-SQL Server variants (not CE) is avaiable with latest Servicepack installed for
MS-SQL Server 2008/2008_R2/
2012/2014/2016/2017 and higher but not
activated as default.

According to this thread, the latest MS-SQL Server CE version (4.0) is now in Deprecationmode and there will be no new version of it at all and official support runs allready out in 2016.Users of CE are recommended to switch over to SQL Server LocalDB (a stripped down version of the real SQL-Server) but its harder to configure as CE and which is no intended for Production environments rather then Developer testing scenarios, see:
https://social.msdn.microsoft.com/Forum ... orum=sqlce

I think as embedded DB (as replacement for CE) which could easy shipped with hMailServer could be SQLite.Its reliable, OpenSource and a well tested alternative which is also supported by PHP. It can handle Terrabyte of Flatfile DBs and supports Databasefile file encryption (the flat Database file is securely encrypted, if the DB creator decided he wants Database encryption). TLS 1.2 doesnt needs to be handled because as in the case of MS-SQL CE - SQLLite is not a Network DBServer so there is no need for Network Transportlayer security like TLS/SSL at all.

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2018-11-12 12:12

If was wrong regarding end of Support for Microsoft SQL-Server Compact 4.0, here are the correct dates:

Microsoft SQL Server Compact 4.0
Lifecycle Start Date: 4/13/2011
Mainstream Support End Date: 7/12/2016
Extended Support End Date: 7/13/2021

See:
https://support.microsoft.com/en-us/lif ... pact%204.0

So we have a little bit more time left to lookout for a reliable replacement for a Flatfile SQL-Database. I think the case of SQL-Server Compact shows
how quickly a closed source, commercial product can discontinued and nobody can fix or patch a thing.

Looks like SQL-Server Compact Fans are really frustrated about Microsoft decision
https://visualstudio.uservoice.com/foru ... -compact-5

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2018-11-13 02:35

hmailserver used to come with the community version of MySQL
that changed when hMailserver became closed source for a couple of versions due to licensing issues, but now that hmailserver is open source again (since 5.4) I see no reason why it shouldn't ship with a another SQL Server

It already supports MySQL (and MariaDB) and PostgreSQL, so I'd vote to use one of those
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2018-11-13 09:38

Thats ok to me, but installing and setting up a full blown SQL-Databaseserver, configuring Transport Layer Security, creating a own DB-Schema and setting up a DB-User (using root/admin or Windows Authentication with weak or blank passwords is a bet security habbit) can be overwhelming for newbies.

I think a lot of Users like CE because its verry easy to setup and works decent for small setups so we should keep this option.

Regarding MySQL.Its property of Oracle Inc, and Oracle is known for threatening OpenSource Projects, discontinuing OpenSource Projects ect. Thats why a lot of original MySQL Developers (inclunding Monty, the Inventor of MySQL) decided to quit working for Oracle and creating MariaDB, which is now developed by Maria Inc. ay in the case of MySQL, some Company like Microsoft or Oracle can buy overtake MariaDB anytime and the whole Problem starts all over again anytime.

The only really free DB-Project which cannot be overtaken or purchased by one of thoose big companies is Postgres. So i would prefer SQLLite as CE replacement and Postgres as reliable default DB for a complex Scenarios and keep the Support for MySQL/MariaDB and MSSQL.

ps:
I think we should make it as easy as possible to let hMailServer connect with MySQL/MariaDB/Postgres and MSSQL with TLS 1.2
For MYSQL/MariaDB this requires A standard SSL Certificate a Private Key file while MSQL supports SSL-Certs but doesnt require them to enable TLS1.2

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2018-11-13 11:49

Back in the day...

When hMailserver shipped with MySQL, it was as seamless to install as the MSSQL CE is install in modern releases

Build 4.4.4 and earlier if you want to test
https://www.hmailserver.com/download_archive
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2018-11-13 19:34

Ok, i checked your recomnend version and
installed it in a Windows XP VM

There is a MySQL4 Installation in
"C:\Program Files\hMailServer\MySQL\
Ver 4.1.18-nt for Win32 on ia32 Community Edition (GPL)

[mysqld]
bind-address=localhost
skip-innodb
myisam-recover=FORCE,BACKUP
basedir=C:\Program Files\hMailServer\MySQL
datadir=C:\Program Files\hMailServer\MySQL\data
port=3307

The My.cnf config file shows it listens on non standard port 3307

How it is installed:
I unpacked the hMailServer-4.4.3-B285.exe which has the following structure
C:\Temp\hms_unpacked\{app}
C:\Temp\hms_unpacked\{sys}
C:\Temp\hms_unpacked\{tmp}

And this Script reflects the Install tasks
C:\Temp\hms_unpacked\install_script.iss

The MySQL Default DB is simply copied by the script and also the original, untouched my.ini config file, which will be manipulated durning the setup:
See:
FileName: "{app}\MySQL\my.INI"; Section: "mysqld"; Key: "basedir"; String: "{app}\MySQL"; Flags: createkeyifdoesntexist
FileName: "{app}\MySQL\my.INI"; Section: "mysqld"; Key: "datadir"; String: "{app}\MySQL\data"; Flags: createkeyifdoesntexist
FileName: "{app}\MySQL\my.INI"; Section: "mysqld"; Key: "port"; String: "3307"; Flags: createkeyifdoesntexist

Afterwards C:\Program Files\hMailServer\MySQL\bin\mysqld-nt.exe service is started with the previous prepared my.ini
params are now in effect.

Now this SQL-Script "hMailServer\DBScripts\Internal MySQL\HMS4.3-MySQL4.1.18.sql" is executed and creates the internal hMailServer Database before Setup ask you for a hMailServer Admin password. I dont understand right now which script or process
triggers the execution of HMS4.3-MySQL4.1.18.sql but i assume its something inside hMailServer.exe (version 4.4.3) itself but iam not sure about this because this task is not part of the InnoSetup Installer script.

However: This scenario works with old versions of MySQL4 and maybe it can be redone with nowdays MySQL8 versions, but there are
many changes.Maybe this was the reason for Martin to replace it with MS-SQL-CE

ps: I noticed, this version of hMailServer has way less DCOM dependencies and doesnt need the .NET Framework at all instead of our nowdays hMailserver 5.6.x versions which makes
it way more easy to port hMailServer4 to Linux
the hMailServer5.x

Update: Looks like i was wrong again, regarding porting hMailServer 4.4.x to Linux is easier.

According to this old build instructions, the Admin, DBSetup and Utlities where coded in Visual Basic 6 and switched to C# later on in hMailServer 5.x. This doesnt makes things any easier

https://www.hmailserver.com/documentati ... e=building

User avatar
mattg
Moderator
Moderator
Posts: 22435
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: TLS 1.2 Support for SQL Server

Post by mattg » 2018-11-13 23:15

Dravion wrote:
2018-11-13 19:34
Maybe this was the reason for Martin to replace it with MS-SQL-CE
Nope
That was solely about hmailserver versions 5.0>5.3 being closed source
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: TLS 1.2 Support for SQL Server

Post by Dravion » 2018-11-14 00:37

mattg wrote:
2018-11-13 23:15
Nope
That was solely about hmailserver versions 5.0>5.3 being closed source

Ok, that make sense.
How ever, we need a CE replacement DB and i think its difficult to support a dramatically down stripped MySQL 8 and later version for 32 and 64-Installers.

SQLLite on the other hand is extremely minimalistic and its code can be integrated directly into hMailServer.exe

SQLLite is used in Firefox in Thunderbird, in PHP and lots of other products and is completely OpenSource. If we replace CE completely with SQLLite it will reduce the Installersize and we dont need to ship extra files or config settings or register and remove a system service.

But there is only one thing i dont like about SQLLite and has todo with the Database file encryption feature.Its a extra payware module which requires a license key.Its not required but its a nice feature.

However: Somebody developed a SQLlite OpenSource Database encryption feature which uses a AES-256 cypher. Such a encrypted hMailServer Flat Database file could not be decrypted without the correct passphrase.

The second step could be encrypting/decrypting
all \Data\*.eml files by hMailServer which would fullfill the PIC and GDPR Law requirements.

BeSmart
New user
New user
Posts: 7
Joined: 2019-05-21 10:26

Re: TLS 1.2 Support for SQL Server

Post by BeSmart » 2019-05-21 10:43

Installing the latest version of Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) and adding

Code: Select all

Provider=MSOLEDBSQL
under the

Code: Select all

[Database]
section of the INI file works just fine.

See also https://docs.microsoft.com/en-us/sql/co ... sql-server.

Edit: It seems Martin has not yet released a build with related changes (see https://github.com/hmailserver/hmailser ... 6b3caa0067).

Post Reply