Troubleshooting SQL Server Connection Errors

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

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 SQL Server Service Running?

By default, the SQL Server service starts automatically at system startup.  However, it can fail to start up (or shut down at some point after startup) for a variety of reasons, including (but not limited to):

• Data files not available if stored on Bitlocker-encrypted drives that have not been unlocked
• Permission issues (e.g., the SQL Server service account loses access to the folder where the data files are stored)
• Unexpected shutdown of the SQL Server service (it's rare, but it does happen, especially on end-user workstations [as opposed to dedicated server machines])

If you have access to the server machine, you can check the status of the SQL Server service using a couple of snap-ins in the Microsoft Management Console.

1. Open an admin Command prompt or Powershell session
2. Type mmc and press [Enter] (to open the Microsoft Management Console)
3. Press [Ctrl] + [M] (or go to File > Add/Remove Snap-in...)
4. Choose Services from the Snap-in list, click [Add >], then choose (o) Local computer, then click [Finish]
5. Choose SQL Server Configuration Manager from the Snap-in list then click [Add >]
6. Click [OK] to close the "Add or Remove Snap-ins" dialog
7. Expand "Services" and scroll down to "SQL Server"
8. Check the Status column and ensure the service you need is set to "Running"
9. If it's not Running, right-click and choose "Start"
10. If the service fails to start, check Windows Event Viewer for possible explanation

Here's a screenshot from my development computer showing the various SQL Server services.  Each service name represents an "instance" of SQL Server.  The instance name appears in parentheses after "SQL Server".  The default instance is named "MSSQLSERVER".  All other services are considered "named" instances, where the instance names are "DEV2014", "DEV2022", "GB", etc.

Note that in the screenshot above, the "SQLEXPRESS2014" named instance is not currently running (it's set for "Manual" startup).  To force it to run, I right-click on the instance name, then choose "Start" from the context menu.

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]:

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:

TROUBLESHOOTING TIP

If the user is trying to connect via Windows Authentication and they are a returning former employee, you may need to drop and recreate their SQL Server Login.  See article below for details:

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

External references

Image by Christian Brinkmann from Pixabay

UPDATE [2023-07-19]: Added second possible explanation for failed TCP port access (SQL Server service is not running).

UPDATE [2024-07-08]: Added section, "Is the SQL Server Service Running?"

UPDATE [2024-08-16]: Added Troubleshooting Tip to "ODBC Data Source Administrator" section.