Page 1 of 1

Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-02 17:37
by insomniac2k2
Below is v2 of my working central MySQL based whitelist and banlisting utility. It's a small executable which is called from your EventHandlers.vbs. It's entire function is the following:

Create, delete, or verify a banlist entry
Create, delete, or verify a whitelist entry

The utility itself is designed to keep your information safe. When the utility is first run, it creates a unique "hash" key in your existing hMailServer.INI file. There is no ties to hmailserver. It is just using the file because it is there. aka. this will never break your installation!

Using the utility is relatively simple (considering..). You will need to do the following things:

Install the latest MySQL connector on your Hmail server:

https://dev.mysql.com/downloads/connector/net/8.0.html
At the time of writing this, the connector was: mysql-connector-net-8.0.15.msi
This will enable DBBan.exe to make a call to the newer Mysql installations.

Install the latest MySQL server on a new server that will act as your central ban and whitelist database server that many servers will access-
Once installed, create a database on it. You will need to create or use the existing username and password that you created when installing MYSQL when connecting to this database with DBBAN.exe.
Once the database, is created add 2 tables. See copy paste examples. Presently there are 2 names hard coded into DBBAN.exe. The ban table is named "snowshoe". The whitelist table is "whitelist". If you want to change this, you will need to edit my source code and recompile it to your liking. Otherwise, do the following exactly:

Ban table:

Code: Select all

use <database name>; 
CREATE TABLE IF NOT EXISTS <snowshoe> (
    id INT AUTO_INCREMENT PRIMARY KEY,
    IPBANS VARCHAR(45),date VARCHAR(45)
);
Whitelist table:

Code: Select all

use <database name>;
CREATE TABLE IF NOT EXISTS <whitelist> (
    id INT AUTO_INCREMENT PRIMARY KEY,
    approved_IP VARCHAR(45),dateExpiry VARCHAR(45)
);
Copy DBBAN.exe to your hmailserver bin directory
Once copied, open a command prompt and set your connection string that DBBAN.exe will use to connect to this new server. You can access this syntax by calling dbban.exe /? .But for simplification, here is an example of working syntax:

Code: Select all

dbban.exe -setconnstring <server=10.20.30.40;Port=3306;database=banlist;uid=ban;pwd=superdupersecretdatabasepassword;>"
Note: This must be your servers information. In my case, my server's IP address is 10.20.30.40. My database name is "banlist". My user ID is "ban". My database password is "superdupersecretdatabasepassword"
Note: This connection string is encrypted against the unique hash that was created. It is not stored in plain text.
Once you enter this connection string, you can verify its syntax by the following command:

Code: Select all

dbban.exe -getconnstring
You can also reset it by just running the command again (until you get it right :) )

Once you have this set, you are ready to start whitelisting and banning to a central database! Presently, my installation has been drastically sped up and improved, while reducing my incoming spam by 85% or so. While I use many different methods and reasoning for banning people, i find that snowshoe is my #1. Below is an example of my working banning and whitelisting logic. You can use this utility however you want.

My logic chain is as such:
Check connected IP address to see if it is already in the banned list on my database
If it is, nothing else is necessary. reject IP.
If it is not, verify IP against whitelist. If the IP exists in the database whitelist, you are done. Exit all tests. Do not verify against any outside sources or waste anymore cycles.
If the IP address does not exist in either list, do a snowshoe test. If snowshoe comes up positive, then ban IP and reject.
If the IP address is not in the snowshoe database, then add this IP address to my central database whitelist. This IP will never get checked against snowshoe or anything else, until I groom the IP out of the database (I groom the whitelist database by expiring whitelisted IP's that are a month old+).

Again. You can use this utility for any logic you choose. The above works VERY well for me. My servers see so much less traffic and no longer hammer outside resources for lookups. As i keep all lookups local after the first hit. Presently, i have 69000 bans and 120000 whitelisted addresses, and query times are still in the milliseconds.

Code: Select all

Sub OnHELO(oClient)
	If(Left(oClient.IPAddress, 8) = "127.0.0.") Then Exit Sub ' Webmail should not process
	  '''''check to see if incoming IP is already added to banlist
   If IsAlreadyBanned(oClient.IPAddress) Then
	ClientIp				= oClient.IpAddress			'Connecting remote IP address
	'EventLog.Write("Previous Ban Check Positive: " & ClientIp & "")
      	Result.Value = 2
      	Result.Message = "5.7.1 CODE01 Your access to this mail system has been rejected due to the sending MTA's poor reputation. If you believe that this failure is in error, please contact the intended recipient via alternate means."
	Exit Sub
   End If
  '''''check to see if incoming IP is already added to banlist

   '''''check to see if incoming IP is already added to whitelist
   If IsWhitelist(oClient.IPAddress) Then
	'EventLog.Write("Whitelist Verified: " & oClient.IPAddress & "")
	Exit Sub
   End If
  '''''check to see if incoming IP is already added to whitelist

   '
   ' SnowShoe SPAM detection
   '
   If IsSnowShoe(oClient.IPAddress) Then
      Result.Value = 2
      Result.Message = "5.7.1 CODE01 Your access to this mail system has been rejected due to the sending MTA's poor reputation. If you believe that this failure is in error, please contact the intended recipient via alternate means."
	'NEW
	Dim objShell
	Dim objExec
	Dim strPSResults
	Dim ip
	ClientIp				= oClient.IpAddress			'Connecting remote IP address
   'test SQL ban
	EventLog.Write("Writing SQL BAN: " & ClientIp & "")
	Call SQLBan(ClientIp)
   'test SQL ban
      Exit Sub
   Else 
	'EventLog.Write("Attempting to add to Whitelist: " & oClient.IPAddress & "")
	Add2WL(oClient.IPAddress)
   End If
End Sub

Function Wait(sec)
  With CreateObject("WScript.Shell")
	 .Run "timeout /T " & Int(sec), 0, True
'        .Run "sleep -m " & Int(sec * 1000), 0, True
'        .Run "powershell Start-Sleep -Milliseconds " & Int(sec * 1000), 0, True
  End With
End Function

'
' System Scripting Runtime COM object ("SScripting.IPNetwork")
' http://www.netal.com/ssr.htm
' Binary -> http://www.netal.com/software/ssr15.zip
'
' ALTERNATIVE: DNSBL      = sbl.spamhaus.org
'              ReturnCode = 127.0.0.3
'              Score      = 5
'
Function IsSnowShoe(strIP) : IsSnowShoe = False
   Dim a
   a = Split(strIP, ".")
   With CreateObject("SScripting.IPNetwork")
      strIP = .DNSLookup(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".sbl.spamhaus.org")
   End With
   If (strIP = "127.0.0.3") Then IsSnowShoe = True
End Function

Function IsAlreadyBanned(chkIP) : IsAlreadyBanned = False
	shellCommand="""C:\Program Files (x86)\hMailServer\Bin\dbban.exe"" -verifyban "
	Set oShell=CreateObject("Wscript.Shell")
	iReturn=oShell.run(shellCommand & chkIP,1,True)
   If (iReturn = "0") Then IsAlreadyBanned = True
End Function

Function SQLBan(banIP)
	shellCommand="""C:\Program Files (x86)\hMailServer\Bin\dbban.exe"" -ban "
	Set oShell=CreateObject("Wscript.Shell")
	iReturn=oShell.Run(shellCommand & banIP,0,True)
End Function

Function IsWhitelist(chkIP) : IsWhitelist = False
	shellCommand="""C:\Program Files (x86)\hMailServer\Bin\dbban.exe"" -verifywl "
	Set oShell=CreateObject("Wscript.Shell")
	iReturn=oShell.run(shellCommand & chkIP,1,True)
   If (iReturn = "0") Then IsWhitelist = True
End Function

Function Add2WL(addIP)
	shellCommand="""C:\Program Files (x86)\hMailServer\Bin\dbban.exe"" -whitelist "
	Set oShell=CreateObject("Wscript.Shell")
	iReturn=oShell.Run(shellCommand & addIP,0,True)
End Function
Attached is the executable and source, should you have a need to modify it.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-03 15:14
by fjansen04
DBBAN.EXE fails with an IO error because the available connector is version 8.0.16, version 8.0.15 is not available anymore.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-04 03:27
by insomniac2k2
That's an interesting and short sided dependency. Let me dig a little to see if I can come up with. Doesn't the site offer previous version downloads?
fjansen04 wrote:
2019-06-03 15:14
DBBAN.EXE fails with an IO error because the available connector is version 8.0.16, version 8.0.15 is not available anymore.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-04 03:48
by insomniac2k2
I traced my download link, and its still live. Give this link a go:
https://dev.mysql.com/downloads/file/?id=484706

When i get a little time, i'll see if i do anything such as enabling backwards compatibility.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-04 10:44
by fjansen04
Thanks, I will try this one.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-04 19:45
by fjansen04
I don't think I will get this to work, I get lots of errors.

Thanks anyhow.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-04 21:47
by insomniac2k2
I'm curious of what your errors are. Maybe i caused them by sanitized the source code before sharing it. I'm sure there is an easy fix in there somewhere.
fjansen04 wrote:
2019-06-04 19:45
I don't think I will get this to work, I get lots of errors.

Thanks anyhow.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-05 07:58
by fjansen04
I got as far as:

"Script Error: Source: Microsoft VBScript runtime error - Error: 800A01AD - Description: ActiveX component can't create object: 'SScripting.IPNetwork'"

I tried to install the COM object, but that is apparently not possible on Windows 10, the dll will not register. It is a very old file.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-05 11:16
by SorenR
Maybe you guys need to chat with RvdH about an alternative to SScripting.IPNetwork !!

https://www.hmailserver.com/forum/viewt ... 28#p196628
RvdH wrote:
2019-01-15 12:30
.A, .IPv4A and .DNSLookup are exactly the same in my component, but i left it there to be easily replaceable with the SScripting.IPNetwork component

Code: Select all

Create object of type DNSLibrary.DNSResolver

This object has 13 public functions:

	-- version() * returns the version number
	-- help() * shows this help
	-- IPv4A(<Domain name>) * query IPv4 A-Record(s)
	-- A(<Domain name>) * same as IPv4A (deprecated)
	-- DNSLookup(<Domain name>) * same as IPv4A (deprecated)
	-- IPv6A(<Domain name>) * query IPv6 A-Record(s)
	-- AAAA(<Domain name>) * same as IPv6A (deprecated)
	-- CNAME(<Domain name>) * query CNAME-Record(s)
	-- MX(<Domain name>) * query MX-Record(s)
	-- NS(<Domain name>) * query NS-Record(s)
	-- PTR(<IP address>) * query PTR-Record(s)
	-- SOA(<Domain name>) * query SOA-Record(s)
	-- TXT(<Domain name>) * query TXT-Record(s)


VBScript example:

Dim ObjDNS
Set ObjDNS = CreateObject("DNSLibrary.DNSResolver")
WScript.Echo("A-Record (IP4): " & ObjDNS.IPv4A("vdhout.nl"))
Set ObjDNS = Nothing

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-05 16:47
by insomniac2k2
My bad. I should have posted that there is a dependency on the lookup. You should be able to install that on everything up to windows 2019. I have it installed on all my servers.

You should also be able to test your database with DBBan.exe as well. As the lookup is only a function that calls dbban to do a task.

EDIT: It's been a while since I registered it, but i believe you just download the zip file and extract it. I extracted mine to the hmailserver bin directory. Then run SSRTest.vbs. I'm pretty sure that's the process. Someone correct me if im wrong.
fjansen04 wrote:
2019-06-05 07:58
I got as far as:

"Script Error: Source: Microsoft VBScript runtime error - Error: 800A01AD - Description: ActiveX component can't create object: 'SScripting.IPNetwork'"

I tried to install the COM object, but that is apparently not possible on Windows 10, the dll will not register. It is a very old file.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-05 17:25
by insomniac2k2
NVM. Brain fart:

Installation
Prerequisites:
Windows 98 / Windows NT 4.0 / Windows 2000 / Windows XP / Windows Server 2003
TCP/IP
Installing the Product
To install the product, copy SScrRun.dll to a directory of your choice and execute the command
"RegSvr32.exe SScrRun.dll".
If the system is running Windows NT 4.0 SP3+ / 2000 / 2003 / XP and you want to generate SNMP traps with the GenerateTrap method of the SNMPManager object, you must also register the STGenExt.dll module. To do this, copy STGenExt.dll to a directory of your choice and execute the command "RegSvr32.exe STGenExt.dll". Please note that you must be an administrator to successfully register the STGenExt.dll module. You must stop and start the SNMP service after registering the STGenExt.dll module to activate this module.
Uninstalling the Product
To uninstall the product, execute the command "RegSvr32.exe /u SScrRun.dll".
To deregister the STGenExt.dll module, execute the command "RegSvr32.exe /u STGenExt.dll".

I believe it worked fine when i ran it as administrator on my windows 2019 servers.

Re: Utility to create and maintain a central MySQL based banlist and whitelist for multiple servers.

Posted: 2019-06-07 15:55
by fjansen04
I already followed this to the letter.

I finally could register SScrRun.dll using an other account, but not STGenExt.dll

I'll see what happens.