SQL OLEDB DOESN'T WORKS ACCROSS DIFFERENT SUBNET

[Massimo]

Hello,

i have this configuration on Meraki MX84:

-DATA SUBNET 10.134.160.0/22   (VLAN1)  Sql server is located in this subnet

-WIFI SUBNET 10.134.167.0/24 (VLAN80)  Client wifi

The clients are located on VLAN80 and everythink working fine (file sharing,printers, sql connection using native driver)

The OLEDB connection in this VLAN doesn't works; it seems SQL close the connection or the packet don't find the way to return to the client; Note that MSSMS from VLAN80 to SQL server works , just OLEDB connections and Crystal report (that use OLEDC or ODBC connections) doesn't works.

I hope someone can address me to the correct way

[BrandonS]

I'm not familiar with the specific database services you are referring to, but is OLEDB a different server/VM/IP address than MSSMS?  I ask because if they were two different servers and one did not have the default gateway back to the VLAN IP on the MX it could explain what you describe.  

If that is not the case then do you want to share what your firewall rules look like between these VLANs?  And are they being routed by the MX or somewhere else?

[Inderdeep]

@Massimo : I am more specifically looking for the firewall rules, Check the use cases here 

https://documentation.meraki.com/General_Administration/Cross-Platform_Content/Using_Layer_3_Firewal...

[CptnCrnch]

Another possibility:

Is your DB connection up and running and being torn down after a while? Even more so after having been idle? If so, this would be typical firewall behaviour: long running idle TCP sessions are typically held for an hour. At least, thanks is something I‘ve seen a lot regarding databases connections.

Fix for that: implement TCP keepalives on your DB server.

[PhilipDAth]

Can you ping the SQL server from the client?

Are you running Windows Firewall on the SQL server (or any other software firewall) and it is configured to allow traffic from remote subnets?

If you turn off AMP and IPS in the Meraki dashboard for a test, does it start working?

[Massimo]

Ping works from both side : Client to SQL and SQL to Client

On SQL Server the firewall is disabled.

If i update an OLAP cube (MSSAS) it works fine.

The issue happens  only with OLEDB provider:

- if i try to make a new connection it cant discover the sql server but if a fill the server name i can create the connection

-if i execute a simple query like select * from tablename it works

-if i execute a long query i receive [ODBC SQL Server Driver][TCP/IP Socket]ConnectionRead(recv()) error

if i use the same client connect on SQL subnet all working fine

Very strange and complex to fix

Thanks for you support 

[CptnCrnch]

Have you configured IPS on your MX? Could you please show us Security & SD-WAN > Configure > Threat protection > Intrusion detection and prevention

[Massimo]

Yes, it is enabled with the below config

[CptnCrnch]

OK, I guess that something is blocking these. Are you getting error messages in the Security Center?

[CharlesIsWorkin]

I have a somewhat related issue with PSQL connection issues across VLANs. Any solution for this?

I have had to put the affected clients on the same vlan as a stop gap measure. These clients connect to our ERP software using PSQL.

I get a connection problem when launching the ERP from a different subnet, but after putting it on the same vlan as the office clients and server, it goes through fine......:(

[joshm-tspa]

We're having this same issue with a .NET application running on a client to a SQL server in another local segment.  Works fine if we place the client in server VLAN.

I opened a support case and we are not using the IPS feature set.  Packet captures show premature TCP resets being sent to the client after about 1-2 seconds into the transaction.

Was anyone able to get to the root cause?

[BrandonT85]

I had the same issues...try 16.15 firmware for you MX... fixed all my issues I was having.... I had issues with Crystal Reports, and a few other apps using pretty much anything while having the server and clients on different VLANs.

[joshm-tspa]

In case this helps anyone else, the root issue on our MX was due the Layer-7 P2P rule we had enabled.  After working with support, it was unclear why this rule was affecting SQL client traffic. 

One strategy is to adjust this rule to use only some of the specifically listed P2P programs or use group policies to bypass entirely and attach to the client/server VLANs.

[CharlesIsWorkin]

Heeyyy you might be onto something here! 

In general, is P2P something that needs to be blocked outside of an educational environment?

[joshm-tspa]

It depends on your security policies but generally there is not much legitimate use for P2P in any enterprise environment.  I could not find a published specification on what traffic patterns Meraki classifies as P2P but support did say this is a complex ruleset.

It would be ideal if it could only be applied to Internet ingress/egress and not internal east-west traffic.  Short of group-policies however this is a global setting on the MX.

[BrandonT85]

Once I upgraded to mx 16.15 I had layer 7 issues when accessing our NVR's. Had to do the same thing to fix it. Odd thing was prior to 16.15 nothing showed in the event log for my SQL issues.... once I upgraded to 16.15 those issues were eliminated but the log showed NVR traffic being flagged as p2p traffic.... odd issue indeed.