Explorer II

Question

SQL Server connection not working via ZTNA Rule

Forum|Forum|3 years ago
June 12, 2023
8 replies
8342 views

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?

FortiGate

Demir21

Staff

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.

chrisW4Author

Explorer II

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

Staff

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-debugging

chrisW4Author

Explorer II

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

Demir21

Staff

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

New Member

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-error

------------------------------
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.

chrisW4Author

Explorer II

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.

stevediaz

New Member

Hello

To connect to a SQL DB via TOAD GUI using ZTNA:

Open port 6434 (or the fixed port you've set) for your SQL instance on your ZTNA server's firewall.
Check if any other ports are required based on encryption or specific SQL features.
Configure ZTNA policies to allow traffic between TOAD and the SQL server.
Ensure proper security and authentication measures align with ZTNA principles.
Troubleshoot using logs if you encounter connectivity issues.

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.../Salesforce Marketing Cloud Course

Thank you

Steve diaz

chrisW4Author

Explorer II

Does ZTNA support Kerberos at all?

If not how is the SMB connection established then?

tbrown

New Member

I think it does given SMB and RDP connections work, there's just something about connecting to a MS SQL Server using the FQDN and Windows Auth using SQL Server Management Studio or ODBC

ZTNA Proxy IP + Windows Auth = works
FQDN + Windows Auth = fails
ZTNA Proxy IP + SQL Auth = works
FQDN + SQL Auth = works

I thought maybe there might be a case-sensitive issue, with the FQDN, but I've ruled that out.