SQL Server connection not working via ZTNA Rule

[chrisW4]

Hi,

I'm new and this is my first post.

I'm currently configuring ZTNA, but I have problems I'm not able to solve.

I want to connect to a SQL DB via TOAD GUI.

Therefore I changed the SQL instance port from dynamic to fixed (Port 6434).

Is there any other port next to may be 1433 and 1434 I need to enable in my ZTNA Server on the Fortigate?

[Demir21]

Hi, 

Welcome in the community! Typical ports used by SQL Server are: TCP 1433, 4022, 135, 1434, UDP 1434.

I would also recommend checking ZTNA traffic logs and see what traffic is being denied.

[chrisW4]

Hi Demir21,

thanks for your answer.

Regarding UDP, how is this configured as there is only TCP available for ZTNA Server on the Fortigate.

Cheers

Christoph

[Demir21]

Hi, 

ZTNA allows to configure only TCP ports. Please to check on ZTNA logs or troubleshoot WAD to find how the Proxy handles a client request. You can find more information on troubleshooting in the following link: 

https://docs.fortinet.com/document/fortigate/7.0.0/new-features/286458/ztna-troubleshooting-and-debu...

[chrisW4]

OK so my Toad for SQL can not be used with ZTNA?

[Demir21]

That doesn't necessarily mean that. You need to check with what SQL service you bind Toad. You may want to check this Microsoft link to find the port for each SQL service http://support.microsoft.com/kb/287932

The most common port used is TCP 1433. Debugging ZTNA logs is useful to have a better look on where the issue may reside.

[tbrown]

I have a similar situation only I'm connecting to a Microsoft SQL Server from Microsoft SQL Server Management Studio (SSMS)

We currently have ZTNA deployed and functioning, with access to RDP and SMB shares. However, when connecting to a SQL Server using the fully qualified domain name, Windows Authentication, it fails with the error:

TITLE: Connect to Server
------------------------------

Cannot connect to myserver.mydomain.local.

------------------------------
ADDITIONAL INFORMATION:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-0-database-engine-erro...

------------------------------
BUTTONS:

OK
------------------------------

The ZTNA logs on the Fortigate show no problems accessing any resources, no access denied, everything is allowed through.

The strange thing is if I ping my server's hostname "myserver.mydomain.local" (changed for privacy) and the proxy IP is returned 10.222.0.22 (changed for privacy), if swap out the hostname in the connection dialog, still using Windows authentication it connects without problem.

I can also connect via RDP to the hostname "myserver.mydomain.local" and SMB shares via hostname "myserver.mydomain.local" so the only thing that won't connect via hostname is SQL Server connections.

Given that I can connect via the proxy IP returned from pinging the FQDN of the sql server, that tells me all the necessary ports are open, but it does seem to be a name resolution issue affecting SQL Connections. 


Another test I did was create an ODBC System DSN, if I used the proxy IP, I had no issue connecting using Windows Authentication. If I created another connection using the FQDN the following error is displayed:

Connection failed:
SQLState: 'HY000'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context


When connecting from the LAN we have no issues using the FQDN to connect to SQL Server, it is just when utilizing the ZTNA connection.

[chrisW4]

Hi,

the Issue is related to Kerbereos, when using SQL Server Athentication it will work. I have the same problem at the moment, but don't know why Kerbereos isn't working.

[tbrown]

Thanks Christoph, I believe you are correct. SQL authentication works just fine with the FQDN. 

[adw2323]

Disabling loopback check fixed my SQL ztna auth issues 

In Registry Editor, locate the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

Right-click Lsa, point to New, and then click DWORD Value. (In Win 2008, its DWORD 32bit)

Type DisableLoopbackCheck, and then press ENTER.

Right-click DisableLoopbackCheck, and then click Modify.

In the Value data box, type 1 and then click OK.

Quit Registry Editor.