none
Problem connecting to SQL Server from Access under Windows 7

    Question

  • I have a client running my Carer Software which has an Access 2007 front-end connected via DSN-less ODBC to SQL Server 2005 Express using SQL Server authentication.

    This works fine on all their XP machines but they are having connection issues with the Windows 7 machines.  After the Server has been rebooted they can usually connect and stay connected for a few hours but then get the following error:

    ---------------------------
    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.

    They then need to get everyone out of the system and reboot the server again.  Restarting SQL Server doesn't fix the problem.

    On the Server we are using the default dynamic port allocation, with TCPIP as the protocol.  We have also tried turning off the firewall on the Windows 7 machines. The same errors occured when we used Windows Authentication instead of SQL Server Authentication.

    SQL Server 2005 Express is installed on the Server on a SBS 2003 network.  All the PC's are logged onto the domain, and the DSN-less ODBC connection is set up using the following:

    "Driver={SQL Server};Server=ssss;Database=CarerData;UID=uuuu;PWD=pppp"

    where ssss=Server\Instance; uuuu=User ID; pppp=Password.

    Please help - we are running out of ideas.

     


    Regards Kevin Seerup
    GoalMaker Business Systems Pty Ltd
    www.goalmaker.com.au
    Thursday, April 29, 2010 7:04 AM

All replies

  • Do you mean that:
    - the client machine (Win7) tries to connect to some SQL server
    - The client machine may randomly enter some state such that all subsequence connections failed (even if reconnect, or restart the SQL server). There is no way to leave that state unless restarting the client machine (win7)

    If this is the case, you may do some troubleshooting when the client machine entered that state. For example, check the DNS server and firewall setting, making sure the connection request has reached the SQL server, using other network service between the client machine and the machine installed the SQL server, etc.

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Thursday, April 29, 2010 4:25 PM
    Answerer
  • Error 67 means that the server cannot find the machine. Try pinging the server to make sure you are using the correct server name and that the server is accessible.

     

    Thursday, April 29, 2010 9:47 PM
  • SQL Server 2005 Express is on the network's Server. The network has about 6 x Win7 PCs and 2 x XP PCs, all on the domain. 

    Assuming the Server has been rebooted then all PCs can log on to the domain, and open my Carer application.  All will continue operating for a few hours at which point one or more of the Win7 PCs will start getting the SQL Server connection error.  Note that this doesn't happen with the XP PCs, the Win7 PCs haven't logged off the domain. The Win7 PCs can still browse to the Server via Explorer, they just can't connect to SQL Server via the Carer application anymore.  Other PCs are still connecting okay at this point.

    The only way to get the Win7 PC to run the Carer application without getting the SQL Server connection error is to get everyone to log off and reboot the Server.  Restarting SQL Server doesn't solve the problem, the Server has to be rebooted.


    Regards Kevin Seerup
    GoalMaker Business Systems Pty Ltd
    www.goalmaker.com.au
    Thursday, April 29, 2010 10:08 PM
  • Could you try the following when the issue happened again:

    1. Use SQL profiler on the SQL Server to see whether the request from Win7 clients reached the SQL server or not

       * If not, please take a look at the firewall log on both the client and the machine installed SQL server. Except firewall, are there any more software installed that can block a network connection, such as anti-malware, etc?

       * Otherwise, please take a look at the SQL log to see why the server rejects the request (this is unlikely since the error code is 67)

    2. Try the SNAC 10 driver (can be downloaded from: http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en), which is the newer version of "SQL Server" ODBC driver. SNAC 10 fixed a lot of bugs that are still present in "SQL Server" ODBC driver.

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Friday, April 30, 2010 2:47 AM
    Answerer
  • We had a similar issue with Windows 7 machines losing thier connections to an Access Database, the XP machines had no problems either. We disabled "DNS Client" on Windows 7 machines which just causes them to always goto the DNS server rather than use the local cache which was obviously the culprit. "DNS Client" is found in the "Services" area of "Computer Management"

    Hope this helps...


    "I am not discouraged, because every wrong attempt discarded is another step forward." Thomas Edison
    Monday, July 11, 2011 7:41 PM
  • I realise this is quite an old thread now, but I found it while searching on the SQL 67 & 17 errors I was seeing. I am posting what I found in case it helps anyone else with the issue who comes upon this discussion.

    I had an application on a Windows 2008 terminal server which suddenly stopped working for any users who did not have admin access on the client terminal server, reporting the 67 & 17 errors. After some investigation with Process Monitor I found that any ODBC connection was trying to write to this registry key:

    HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSockeNetLib\LastConnect

    Non admin users only had read permission to the key. I changed it to modify and the application started working again. Since I hadn't made any substantial changes to the server myself I am assuming an MS update has changed something.

    Hope this helps anyone else having a similar problem.

     

    Thursday, January 12, 2012 7:47 AM