Error Connection to SQL Server using [HOSTNAME]\instance or [FQDN]\Instance, [IP]\instance working

Solution 1:

In your SQL Server Configuration Manager, check the Network Configuration TCP/IP settings - on the IP addresses tab, is the IP address that your FQDN resolves to actually enabled?

If you connect to the local machine from SSMS, I think it'll use Shared Memory above all other protocols.

Solution 2:

Check your hosts file. You may have an errant entry in there. nslookup intentionally looks at a DNS server but does not consult the local hosts file first.

Solution 3:

Have you explored NETBIOS failure as a possible cause?

Maybe try establishing a NETBIOS Session with the server first and see if you can connect like in the below post:

https://stackoverflow.com/questions/5237654/sql-server-management-studio-2008-runas-user-on-different-domain-over-vpn

NET USE \DBSERVER /USER:DOMAIN\USERNAME