SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access users can't use SQL 2016 (?)


Access users can't use SQL 2016 (?)

Author
Message
briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
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!
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172650 Visits: 23542
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Evgeny Garaev
Evgeny Garaev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3463 Visits: 1501
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.
briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
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.
briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
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.

briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
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"
Chris Harshman
Chris Harshman
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29433 Visits: 6377
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?

briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
In Access it is a User DSN with Integrated Windows Authentication which with the registry files has worked fine in SQL2005.
briancampbellmcad
briancampbellmcad
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1976 Visits: 590
System DSNs persistently fail when I try to create them.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172650 Visits: 23542
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search