February 6, 2018 at 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!
February 6, 2018 at 12:13 pm
briancampbellmcad - Tuesday, February 6, 2018 12:00 PMI'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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2018 at 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.
February 6, 2018 at 1:44 pm
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.
February 6, 2018 at 1:53 pm
Evgeny Garaev - Tuesday, February 6, 2018 1:34 PMCheck 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.
February 6, 2018 at 2:56 pm
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"
February 6, 2018 at 3:03 pm
briancampbellmcad - Tuesday, February 6, 2018 1:53 PMThe 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?
February 6, 2018 at 3:13 pm
In Access it is a User DSN with Integrated Windows Authentication which with the registry files has worked fine in SQL2005.
February 6, 2018 at 3:16 pm
System DSNs persistently fail when I try to create them.
February 7, 2018 at 7:25 am
briancampbellmcad - Tuesday, February 6, 2018 3:16 PMSystem 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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2018 at 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?
February 7, 2018 at 7:47 am
briancampbellmcad - Wednesday, February 7, 2018 7:39 AMFrom 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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2018 at 7:54 am
briancampbellmcad - Wednesday, February 7, 2018 7:39 AMFrom 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
February 7, 2018 at 10:00 am
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.
February 8, 2018 at 12:26 am
briancampbellmcad - Tuesday, February 6, 2018 12:00 PMI'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