OPNsense 17.7.11 & MSSQL

[hjint]

I've have small network running 1xWin XP, 1xWin7 & 3xWin10 PC's and the one Win10 PC's is also a dedicated MSSQL server not used for anything else.

The current network without OPNsense worked without any problems for the past year and the were no changes to the network prior to or after the introduction of the OPNsense server. The only change is the introduction of the OPNsense server. With OPNsense connected to the network, all traffic goes the via OPNsense server, we can do web browsing, send and receive emails, map drives between the various PC's, print to the shared printer on the network (connected to one of the other Win10 PC's) except for the problem below.

When OPNsense is connected to the network we experience timeouts on the connections to MSSQL server and when OPNsense is disconnected from the network, there are no timeouts. Please note that the timeouts never occured in the past with the current setup prior to the introduction of OPNsense to the network.

-> Webproxy is configured as transparent on HTTP only, HTTPS is not yet configured
-> Lan rules: First rule redirect traffic to Webproxy, second rule is any to any on all settings

Any setting/(s) that I need to change or any ideas what can cause this?

Much appreciated in advance

[ChrisH]

Do the MSSQL connections go through the OPNsense box?
Is there extensive firewall logging or something like that?
What does the OPNsense CPU load look like?
What hardware does OPNsense run on?
Do the timeouts also occur if you run the SQL queries directly on the MSSQL box?

[bartjsmit]

Did you check for IP address conflicts?

Bart...

[hjint]

Bart, none - I've setup MAC binding / static leases on OPNsense for all the machines and each machine is setup to obtain DHCP IP address, the same config is also on the DHCP side of the router

Chris,
1) MSSQL goes through OPNsense box
2) Only blocking outside rules get logged, only a few lines in the log
3) CPU load average is 0.58, 0.81, 0.73
4) Celeron CPU 3Ghz, 1 Core, 2MB Ram, 80G HD, OPNsense 17.7.11-amd64, FreeBSD 11.0 Rel p17, OpenSSL1.0.2n 7Dec2017, memory usage 44% (861/1947), disk usage 8% ufs (5.2G/67G)
5) No timeouts when the queries / software run on the MSSQL box

What I have figured out in the meanwhile is that when I disable both the "Block Proxy Bypass" under LAN Rules and "Traffic Redirect" under NAT | Port Forward, no timeouts occur and when I enable either one the timeouts occur again.

[ChrisH]

Huh. Clutching at straws here - do you even NAT the MSSQL box? Are there so many SQL connections happening that the OPNsense state table runs full?

Could you describe the networks and firewall rules pertaining to the MSSQL connections?

[bartjsmit]

I'm with Chris; a description of your network/subnet(s) would help.

CPU load is a bit high for a single core. Can you test with a more modern box?
Do you firewall the SQL at all? Does the SQL server resolve on its private IP for clients?
Can you bridge the server and client subnets?

Bart...

[hjint]

What I actually forgot to mention at the start of this post is that I'm completely new to OPNsense. Management gave me the task to find the best software firewall and get it working smooth in a test environment before going into production. Management will only spend money on upgrading the hardware for the firewall when we're ready to go into production

I've fiddled a lot with settings and made a complete mess of the setup and have done a clean install from scratch. While fiddling, I've manage to get the SQL connections to work by adding the SQL box's IP to Unrestricted Access and add the SQL TCP port to Allowed Destination TCP under Access Control of the Web Proxy. Chris your question about NAT for the SQL box got me thinking on how to whitelist the SQL box - thanks for that.

Any further ideas or better ideas will be much appreciated, but for the moment I think the above solution is suffice.

[ChrisH]

I don't see why MSSQL connections (TCP/1433) should even go through the web proxy.