Connect Access 2016 to remote SQL Server - SQL Server Error 17

Question

Good morning,

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.

Tables

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.

Any Ideas?

You need to confirm which port is used by your SQL server instance. An instance will use a different port than 1433 which would be only for a default instance. — Greg Askew, Jul 21 '22 at 14:45
https://stackoverflow.com/questions/19451490/how-to-identify-port-number-of-sql-server — Greg Askew, Jul 21 '22 at 15:20

score 2 · Answer 1 · answered Jul 21 '22 at 18:23

By default, named instances use dynamic ports.

Take a look at the SQL Server Configurator Manager. The named instance will probably use a different TCP port (static/dynamic).

Configure the firewall as shown in section Ports Used By the Database Engine and Dynamic Ports

Manu · Accepted Answer · 2022-07-28T09:23:55.580

Your Windows Firewall is off for the Domain Profile. Make sure you are using the Domain Profile. Check by using PowerShell command Get-NetConnectionProfile, see property NetworkCategory and change either your connection profile or your firewall settings if it does not match.
Make sure you know on which port your instance is listening. See this manual for details. My personal preference: Set Listen All: Yes and just set a TCP Port on IPAll (empty TCP Dynamic Ports)
Enable your Server Browser if it isn't. Using a named instance you'd need it if you won't give a port with the connection string.
Check if your client can connect to this listener. Use PowerShell command Test-NetConnection -Computername Server -Port [instance port/browser port] See first link for infos on port numbers.
Check with SQL Server Profiler for errors by setting Show all events, extend Errors and Warnings and enable User Error Message let it run and try your connection again. Read the red entries and adjust accordingly.

From your question it is not 100% clear which authentication method you are using. At the last step of this list you should be able to see if your method is an issue.

Connect Access 2016 to remote SQL Server - SQL Server Error 17

2 Answers2