Access users can't use SQL 2016 (?)

  • I'm migrating several Access backends from SQL Server 2005 to SQL Server 2016. I have successfully gotten the ODBC connections to work and I can see and use the Access databases (on the network). However when the users open a 2016 copy of the backends to their Access databases they get an error "Connection failed, SQL State: ‘01000’, SQL Server Error: 53" AND "Connection failed, SQL State: ‘08001’, SQL Server Error: 17"
    Here's what I have done so far: I made sure TCP/IP was enabled on SQL Server 2016, I made sure Port 1433 was open, SQL Server and Windows Authentication enabled, allow remote connections to the server. 
    The user's machine is using 
    64-bit Windows 7 and the 32-bit version of Office. I gave the user full rights to all schemas and membership. Is there a driver update needed? I don't see any online that is for SQL 2016 and compatible with the user machines. Any ideas? Thanks in advance!

  • briancampbellmcad - Tuesday, February 6, 2018 12:00 PM

    I'm migrating several Access backends from SQL Server 2005 to SQL Server 2016. I have successfully gotten the ODBC connections to work and I can see and use the Access databases (on the network). However when the users open a 2016 copy of the backends to their Access databases they get an error "Connection failed, SQL State: ‘01000’, SQL Server Error: 53" AND "Connection failed, SQL State: ‘08001’, SQL Server Error: 17"
    Here's what I have done so far: I made sure TCP/IP was enabled on SQL Server 2016, I made sure Port 1433 was open, SQL Server and Windows Authentication enabled, allow remote connections to the server. 
    The user's machine is using 
    64-bit Windows 7 and the 32-bit version of Office. I gave the user full rights to all schemas and membership. Is there a driver update needed? I don't see any online that is for SQL 2016 and compatible with the user machines. Any ideas? Thanks in advance!

    I've used Access to view SQL Server 2016 tables, so this is possible. Can you install SSMS for one of the users and check their access from there?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Check that you can connect from the user/application pc using an odbc connection. If that is possible trace the SQL Server instance and make sure that Access is actually trying to connect to the server.

  • I put SSMS on the User's machine and tried to login with Windows Authentication. I had to create the same user in the Database's Security node, and only then would it allow connecting through SSMS. But still the connection attempt to the Access database gives the same errors as before.

  • Evgeny Garaev - Tuesday, February 6, 2018 1:34 PM

    Check that you can connect from the user/application pc using an odbc connection. If that is possible trace the SQL Server instance and make sure that Access is actually trying to connect to the server.

    The user can read any table of the DB through SSMS, but cannot through Access even though the ODBC linked tables are accessible to me through the Access database. From the user's machine in SSMS I can access anything with their loin, but I can't do anything through Access with her machine, even directly opening the tables or using the linked table manager.

  • For the users to open Access they have to have a .reg file imported to their machine that looks like this:
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\CE_DB]
    "Driver"="C:\\Windows\\system32\\sqlsrv32.dll"
    "Server"="NBCP-SQL01-VM\NBCSQL01VM"
    "Database"="CE_DB"
    "LastUser"="williams"
    "Trusted_Connection"="Yes"

    The entry "NBCP-SQL01-VM\NBCSQL01VM" may be a problem - "NBCP-SQL01-VM" is the name of the virtual server machine, whereas "NBCSQL01VM" I think is the name of the instance, BUT
    In SSMS the server name is indeed "NBCP-SQL01-VM\NBCSQL01VM"

  • briancampbellmcad - Tuesday, February 6, 2018 1:53 PM

    The user can read any table of the DB through SSMS, but cannot through Access even though the ODBC linked tables are accessible to me through the Access database. From the user's machine in SSMS I can access anything with their loin, but I can't do anything through Access with her machine, even directly opening the tables or using the linked table manager.

    how is the ODBC connection setup?  Is it a User DSN or System DSN?  Is it Integrated Windows Authentication or SQL Server Authentication?

  • In Access it is a User DSN with Integrated Windows Authentication which with the registry files has worked fine in SQL2005.

  • System DSNs persistently fail when I try to create them.

  • briancampbellmcad - Tuesday, February 6, 2018 3:16 PM

    System DSNs persistently fail when I try to create them.

    If you use SQL Server Native Client 11.0 as the driver, rather than just 'SQL Server' (which is what I think you are using now), does the connection work?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • From Access when I was creating my ODBC connections I believe I chose SQL Server Native Client 11.0. Is there a way to tell for sure short of creating a new ODBC?

  • briancampbellmcad - Wednesday, February 7, 2018 7:39 AM

    From Access when I was creating my ODBC connections I believe I chose SQL Server Native Client 11.0. Is there a way to tell for sure short of creating a new ODBC?

    Driver should be SQLNCLI11.DLL

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • briancampbellmcad - Wednesday, February 7, 2018 7:39 AM

    From Access when I was creating my ODBC connections I believe I chose SQL Server Native Client 11.0. Is there a way to tell for sure short of creating a new ODBC?

    For user DSNs, you can check the registry and check the driver. If that same DSN is on your PC, check:
    HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<YourDSNName>

    Sue

  • In my registry I have this for the database in question:

    Description CE_DB
    Driver   C:\Windows\SysWOW64\SQLNCLI11.DLL
    Server   NBCP-SQL01-VM\NBCSQL01VM

    So I changed my .reg file to read:

    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\CE_DB]
    "Driver"="C:\\Windows\\SysWOW64\\SQLNCLI11.DLL"
    "Server"="NBCP-SQL01-VM\NBCSQL01VM"
    "Database"="CE_DB"
    "LastUser"="campbell"
    "Trusted_Connection"="Yes"

    I imported this into the user's registry as a .reg file and still get the errors.

  • briancampbellmcad - Tuesday, February 6, 2018 12:00 PM

    I'm migrating several Access backends from SQL Server 2005 to SQL Server 2016. I have successfully gotten the ODBC connections to work and I can see and use the Access databases (on the network). However when the users open a 2016 copy of the backends to their Access databases they get an error "Connection failed, SQL State: ‘01000’, SQL Server Error: 53" AND "Connection failed, SQL State: ‘08001’, SQL Server Error: 17"
    Here's what I have done so far: I made sure TCP/IP was enabled on SQL Server 2016, I made sure Port 1433 was open, SQL Server and Windows Authentication enabled, allow remote connections to the server. 
    The user's machine is using 
    64-bit Windows 7 and the 32-bit version of Office. I gave the user full rights to all schemas and membership. Is there a driver update needed? I don't see any online that is for SQL 2016 and compatible with the user machines. Any ideas? Thanks in advance!

    This issue could be due to DNS error,  If you could try to ping your DNS domain ? I suspect something wrong with your connectivity with DNS which prevents the ODBC connection.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply