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.

Troubleshooting SQL Server Connection Errors

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 Blocked by a Firewall?

Ensure the SQL Server port is open and available.  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 3389 

If you can ping the computer but the above command fails, then chances are there is a firewall between your computer and the server blocking access to the SQL Server port.

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.  

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:

Reader Tips

What methods and tricks do you use when troubleshooting SQL Server connections?  Let us know in the comments below.


Referenced articles

Checking Specific TCP Port Access to a Remote Machine
Troubleshooting network connections is tough. Generic error messages make it hard to identify the root of the problem. This PowerShell cmdlet can help.
Using Windows Authentication to Connect to SQL Server from Outside the Domain
You don’t need to join your computer to a domain to be able to connect to SQL Server using Windows Authentication. You just need to know this simple trick.

External references

Use the SSMS XEvent Profiler - SQL Server
The XEvent Profiler displays a live viewer of extended events. Learn why to use this profiler, key features, and how to get started viewing extended events.

Image by Christian Brinkmann from Pixabay