We are running Windows SQL Server 2012 Standard on Windows Server 2012.
There is a SQL Database with an Access Front End which we migrated from older versions of Access and SQL.
The Access front end communicates with the database most of the time without a problem.
However, sometimes Access just stops responding and then the user gets the following error:
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 is denied.
The person who maintains the Access and the SQL Databases provided the following summary:
The spontaneous issue affects everyone connected to the SQL Server at the same time, and manifests itself in a slow or non-existent connection. It resolves itself in a few minutes to a few hours. The ODBC trace did not yield any useful information about the problem. We tested with up to 5 simultaneous users with no problem. When we observed the issue of Friday, only four users were in the database, and closing these connections did not immediately resolve the issue. There appeared to be no problems with the network for file system access to the server during the down time.
Based on this information, it definitely is not an issue with MS Access, the ODBC configuration, or the database structure. It appears to be an intermittent connectivity issue with SQL Server itself, perhaps due to configuration problems, licensing (as you mentioned), conflicts with other software/processes, or due to some other SQL Server process. Unfortunately, this is outside of my expertise and we do not have dedicated SQL Server or IT resources.
I had another IT Person look at the issue, he found the following:
Sally's PC was getting SQL error. Logged into her PC to look at it
(logged in as Sally and ascleAdmin).Looked at server name, want to verify that in the
Control Panel > Adminstratrative Tools > ODBC > Connections
There was no odbc connections under User DSN, System DSN or Fie DSNCreated new System DSN connection Specified server:
CLE-VM-SQL-FILE\CLESQLSERVERThe rest were default values Clicked test and it was successful
Unfortunately, the problem started again soon after these changes were made.
It was suggested there could be a hardware issue, so we have replaced the switch and made sure the drivers on the Network Adapters are current. Plus we have never seen any indication of a loss of network connectivity while testing using a PING Test.
Has anyone else experienced this issue and if so, how did you solve it?
Best Answer
Depends on situation there are a few most common reasons:
You can check this article for details.