Debugging

Troubleshooting SQL Server Connection Errors

"SQL Server does not exist or access denied." One error. Dozens of possible causes. Read on for a bunch of practical troubleshooting tips.

Mike Wolfe

Feb 28, 2022 • 6 min read

One of the most frustrating things to troubleshoot in Access is when you can't connect to SQL Server.

Connection failed: SQL Server does not exist or access denied.

Microsoft SQL Server Login
Connection failed:
SQLState: '01000'
SQL Server Error: 67
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

This error is annoying because:

it often involves a 60-second timeout
it's impossible to trap using traditional VBA error handling
the error message is close to useless
there are so many possible causes for this error
it often occurs on a user's computer making it (A) hard to troubleshoot and (B) impossible to reproduce on a development computer

In this article, I want to take you through some of the troubleshooting steps that I use when trying to resolve this error.

Is the Server Reachable?

The first tool to use here is ping. Open a command prompt and type the command ping followed by the name of the database server:

C:\> ping MyDbServer

Can you ping the server by IP address but not by name? If so, then there's likely a DNS problem.

C:\> ping 10.20.30.40

Can't ping the server by IP address or name? Try a tracert next. The trace will often bounce from node to node until it hits a deadend. The last node prior to the deadend should provide a hint as to where the problem lies.

C:\> tracert 10.20.30.40

If one or more of these tools fails to connect, it should help you narrow down the source of the problem. HOWEVER, some network admins disable ICMP (the ping protocol), so just because ping fails that does not necessarily mean it's a network problem. Continue on to the next section.

TIP: Virtual private networks (VPNs) are frequent sources of intermittent network routing and DNS problems. Try disconnecting any active VPNs (unless needed to connect to the database server).

Is the Port Open and Listening?

Ensure the SQL Server port is open and listening. The default port is 1433, but that can be set to something different. You can use PowerShell to test TCP access:

Test-NetConnection -ComputerName MyDbServer -Port 1433

If you can ping the computer but the above command fails, then there are two likely possibilities:

there is a firewall between your computer and the server blocking access to the SQL Server port
the SQL Server service is Stopped and needs to be started

Is TCP/IP Enabled at the Server?

If you are trying to connect to a brand new database server, then the problem could be at the server itself. Open SQL Server Configuration Manager and make sure that TCP/IP is Enabled:

Does the User Have Needed Permissions?

When you can connect to the database from your development computer, but another user can't connect from their computer, it could be a permissions issue. One way to rule that out is to try running the application with your credentials from the user's computer.

On the end user's computer, launch Access as a different user:

Enter your user credentials and then try to connect to the database.

If you are able to connect while running as a different user, then chances are it is a permissions problem.

Verify that:

the user has been granted access to the database OR
if you assign rights to Active Directory security groups, verify they are a member of the expected security group

NOTE: this only makes sense if you are using Windows Authentication to connect to SQL Server.

Troubleshooting Tip: /runas /netonly
If you get a message about a missing "trust relationship" using the above approach, you may need to fall back on using a combination of the /runas and /netonly flags. See here for details.

Reader Clarification: Network vs. Database Permissions

UPDATE [2022-12-26]: As Philipp Stiefel points out in the comments below, there are two different kinds of permissions that could be blocking access to the database (or a particular table, stored procedure, view, etc.). Here's Philipp's explanation:

The "access denied" part of the quoted error message only pertains to access on network level, as you covered in the "Server Reachable?" and "Port Blocked?" sections. The message does not apply to missing permissions in SQL Server. Missing permissions will raise the different error message: "Login failed for user 'YourUserName'" (SQL Server-Error 18456). Being aware of these different error messages will get you to the cause of the problem quicker.

Thanks for the clarification and explanation, Philipp!

Monitor Access Attempts from SQL Server

Open SSMS and connect to the database server from your development computer. Launch a "Standard" XEvent Profiler session.

Then attempt to connect from the end user's computer. Review the entries logged in the XEvent session for hints as to why the user is failing to connect:

ODBC Data Source Administrator

If you don't have access to the database server or a copy of SSMS, you can use the ODBC Data Source Administrator to do some basic debugging from the client side.

Press [Win] + [R] then type odbcad32.exe and click [OK]:

NOTE: Don't worry about the "32" in the file name; this will open the 64-bit version if needed.

On the User DSN tab, click the [Add...] button. Firstly, this will open up a list of available ODBC drivers. One possible reason for failure is if the required ODBC driver is not installed on the end user's computer. This helps troubleshoot that possibility.

Follow the prompts to enter your server name and credentials. The third screen should give you the option to set a default database. If you check that box, you can use the dropdown to show all the databases that the user has access to. If the desired database is missing from the list, but other databases are shown, then it's likely a permission issue.

When you are finished creating your temporary DSN, you can click [Test Data Source...] to make sure everything is working correctly:

Viewing Available Tables from Excel

To see a list of tables the user has access to from a machine that does not have a full version of Access, but that does have a copy of Excel, you can use that to view what tables are available to the user.

Go to Data > Get Data > From Database > From SQL Server Database

Enter the requested information in the dialog boxes. Assuming you are able to connect to the database, you should then see a list of tables to which the user has at least a minimal set of permissions: