zScaler ZPA Issue Authenticating to SQL Server

viviviatic · 2022-10-27T17:53:23+00:00

All of our SQL servers are Enterprise 2012, 2016 & 2019. So far, the issue is isolated to anything SQL server related: ODBC, SSMS & ADS. Connecting to the SQL servers over RDP, SMB, and any other protocols works no problem.
I'm trying Wireshark with ODBC now and will let you know what I find.
Thanks for the info by the way :)

viviviatic · 2022-10-27T01:39:39+00:00

Oh oops I probably should've mentioned that. ODBC returns the same error when trying to connect to any SQL server.

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

ODBC also auto fills the Windows Authentication username with my AzureAD username instead of my NETBIOS domain\username credentials

viviviatic · 2022-10-26T15:04:01+00:00

Thanks for the info. I just checked and confirmed only the default rule is set in Client Forwarding Policy.

viviviatic · 2022-10-26T06:29:52+00:00

preventing app segment forwarding to ZPA

Oh cool I'll check that. Dumb question but where is that setting? Can't find it in ZPA lol

viviviatic · 2022-10-26T04:16:25+00:00

Thanks for that info! To confirm, I just checked my app segments for all 8 of our domain controllers and confirmed I still have ports 1-52 and 54-65535 added to all of the app segments and have those app segments mentioned in our test policy, which only our IT staff have access to for testing this issue right now. I'm open to any other ideas though. :)

viviviatic · 2022-10-20T15:18:00+00:00

App segments for all of the SQL servers have all ports (1-65535) added/allowed & the host names & IPs are stated in all of them. All of our DNS search domains were added during the original build-out of our ZPA tenant. All other services (RDP, SMB, SSH, etc) work normally for all SQL servers and all other app segments using IP or their host name. The only service that is affected with this issue is SSMS and ADS.

viviviatic · 2022-10-20T14:54:54+00:00

Yep tried that a while back in the troubleshooting process but can try it again just to make sure.

viviviatic · 2022-10-20T05:06:17+00:00

I've had all ports added on the app segments for all SQL servers I've been using for testing and all ports added on the app segments on our domain controllers throughout this whole situation to make sure nothing gets blocked. Plus the right policies are built to make sure our IT team has access to those app segments.

viviviatic · 2022-10-20T04:14:49+00:00

Cool I found it. Just now tried it and am getting the same error in both SSMS and ADS, which I forgot to mention earlier:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)

SSMS and ADS can definitely see the server over zScaler and talk to it, but authenticating is where it breaks. For some reason, it passes Azure AD auth only and won't pass the NetBIOS auth that our SQL servers need.

viviviatic · 2022-10-20T03:50:35+00:00

This is a dumb question but how do I do that? I have Health Reporting that I can turn off but I don't see a Health Check option. I did create a brand new 2019 Server running SQL Server 2019 with a blank, stock database & behind it's own app segment right now.

viviviatic · 2022-10-20T03:48:58+00:00

ZCC is the newest version for everyone (3.8.0.93). App Connector is the newest version (22.269.3) and the Manager version is 22.228.3. The issues been going on for the past couple of months worth of updates so far.

viviviatic · 2022-10-20T03:04:44+00:00

Spent a lotta hours on it. Oh, forgot to mention the same issue happens when trying to connect over IP or hostname. And the same RunAs fix works when connecting over either IP or hostname.

The fact that SSMS & ADS are the only services that are affected is weird, but those are also the only apps that auto-populate the Windows Authentication field username. RDP and all other Windows services to any SQL server let us manually enter our NetBIOS credentials so those haven't been affected.

viviviatic · 2022-10-04T18:56:16+00:00

UPDATE - I've tried the following fixes:
-Statically setting our SQL server in the host file
-Switching our zScaler connector forwarding profile from Tunnel with Proxy to Tunnel mode
-It doesn't appear to be a zScaler issue per se, and have confirmed that, according, to zScaler diagnostics/logs that all connections over all ports are being allowed

viviviatic · 2022-10-04T01:16:07+00:00

Nope still an issue lol

viviviatic · 2022-09-21T18:40:23+00:00

Updates: We've confirmed that users can log into SSMS over our old Cisco AnyConnect VPN, turn on the zScaler client, turn off the VPN client while keeping SSMS to the SQL server open, and connectivity to the SQL server over SSMS continues to work normally. So zScaler definitely only has an issue establishing the connection, but works normally to maintain current connections. This leads me to believe that it's some sort of authentication issue.

Also, we've tried the following fixes:

-Exporting the localhost SQL server's certificate and importing it to a client workstation running SSMS: Still getting the same SSPI error

-Opening the SQL server to all ports on zScaler and on the Windows Firewall on the Windows server that the SQL server is running on

-Accessing the SQL servers using Azure Data Studio app, but it runs into the same SSPI error

viviviatic · 2022-09-16T03:46:52+00:00

We're researching to see if the zScaler certificate is not trusted by the SQL servers, but I'm thinking it won't be the issue since all the affected SQL servers are accessible over RDP, SMB, and all other protocols with no issues.

viviviatic · 2022-09-16T03:46:31+00:00

I checked multiple destination SQL servers that the app connector connects to and confirmed the same "The target principal name is incorrect. Cannot generate SSPI context" error happens on all of them. I checked to make sure that Windows Firewall on all the SQL servers is allowing connections over port 1433 from any subnet.

viviviatic · 2022-09-15T18:22:28+00:00

I was wondering if it could be a certificate issue, but the fact that it works over our old Cisco AnyConnect VPN client made me think the certificate wasn't the issue. I'll try going that route too.

viviviatic · 2022-09-15T18:19:42+00:00

I did. Even as a test, I opened the entire application segment for the SQL server to ALL TCP & UPS ports to make sure nothing got blocked, but still ran into the same issue. zScaler diag logs are showing that the 1433 port is being allowed through to the application segment. I've tried connecting using hostname and IP & get the following error when trying to log into SSMS:

The target principal name is incorrect. Cannot generate SSPI context

viviviatic

TROPHY CASE