I am trying to connect to a SQL server from a remote machine, with ACCESS 2016, using the "External data" option. Both are on the same network.
It is important to note that I am connected as a domain verified user.
I create a new connection with : "New" > (Data source) "SQL Server", and when selecting the SQL server, I give the DNS name of my SQL server in DNS format "SERVER\AWORKS", and I choose the credentials of the verified user.
Knowing that the server has this instance with a Northwind database and a related table where we want to connect to.
Once I click on "Next" I get the following error.
I looked at the Microsoft docs and it turns out that apparently
Solution: The firewall on the server is blocking incoming connections. Check the firewall settings for the server operating system. Start code 0".
So I looked at the firewall settings needed to make the connection work from a remote machine with access here and it turns out that the important ports are the following.
"Ports Used By the Database Engine By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434."
All are enabled on my SQL Server as shown below. And of course they are enabled at the "domain" level.
Despite the open ports, authentication still doesn't work.
However I noticed that if I disable the firewall at the domain level as shown here,
the connection works with "Server\AWORKS" and I can read the data from "Northwind" as well as the associated tables as shown here.
So I would assume it's due to the firewall.
But I still don't know what exactly makes the "Domain" firewall block connections
I want to avoid to expose the SQL server's domain firewall to any risk.