After we migrated to a new Unifi Dream Machine Pro, we updated the IP addresses for our servers and VMs. However, since changing the IP for our SQL Server VM to 10.10.10.31, all Windows devices on the network can no longer connect, while all Macs can connect without issues. We're using the same identity enterprise VPN for everybody. This connectivity problem occurs both on the local network and over VPN.
Here's what we've observed: SQL Server is listening on port 1433, pings from Windows to the SQL VM successfully go through, and tracert shows a straightforward two-hop route. However, tests to connect to port 1433 fail sometimes but not consistently. The error messages we're seeing include timeout errors and incorrect login details. Capturing packets reveals that while the Mac connections perform smoothly, there are numerous TCP retransmissions and small window sizes for Windows traffic.
I have tried disabling firewalls and antivirus software, ensuring SQL Server allows remote connections, restarting services, configuring firewalls on the Unifi system, and adjusting the MTU size on the VPN adapter, among other troubleshooting steps. I'm puzzled as to why Macs can connect while Windows devices are blocked. Any suggestions? Thanks!
6 Answers
Have you checked if the new IP address is configured in the SQL Server network settings? Make sure it's enabled in the TCP/IP protocols section. If SQL Server isn't aware of the change, it won't allow connections.
Have you set the IP in your hosts file? You could try flushing the DNS cache and rebooting the machine. I once faced a similar issue that was resolved by clearing the Internet Explorer cache, which strangely held on to stale connection info.
I did flush the DNS and restarted the PC, but that didn’t fix it for me.
Could there be a TLS version mismatch between the Unifi setup and what you were using before? Windows 11 can be pretty strict about TLS versions.
I believe both systems are using TLS version 1.2, so I don’t think that’s the issue.
Windows clients might be having packet fragmentation issues. Try lowering the MTU size for your VPN connection; something like 1350 might help. Just make sure to reset the connection after changing it!
I actually tried lowering the MTU but forgot to mention it in my post. No luck there unfortunately.
Are you using Windows Authentication from the Windows PCs and SQL accounts from the Macs? It might be worth checking if you're connecting using FQDN or just the IP. Sometimes, an old service principal could cause issues with Kerberos authentication when the IP address changes.
We're using the SQL admin account with IPs for both Windows and Mac connections. I don't think any old service principals are tied to the previous IP.
Check if there's a DSN entry for the SQL connection in the ODBC Data Source Administrator. Sometimes deleting and recreating the DSN can fix connectivity issues. You could also run a test connection through the ODBC tool.
Totally agree. If SQL is clueless about the new setup, that could really mess things up.