Solved

Upgraded SQL 2008 from 32-bit to 64-bit, now access database cannot connect from non-domain computers using SQL login.

Posted on 2016-09-16
10
93 Views
Last Modified: 2016-09-21
My client uses an application called Amazing Charts.  We recently had them upgrade the SQL software from 32-bit to 64-bit as the performance was taking a hit.  They have a MS database that all the computers use, most of them from a remote desktop (we adopted this network a few months ago, don't ask why local users are RDP!)  The rest are just workgroup computers, Windows 7.  

The setup before was everyone was setup through ODBC, User DSN, SQL Server, using SQL Authentication.  This stopped working for everyone.  I would get an ODBC - Call Failed.   If I went back through the User DSN setup, it and do the test at the end, it shows test successful, but my database still says Call Failed.

On the SQL Server, I created a new user in the security, and gave them all the roles as a test, same scenario.

I added 'Authenticated Users' and tried with users who login to the Terminal Server and the database works fine.

Leads me to believe it's an authentication issue, but I don't get why the SQL authentication stopped working, and how to get it back to working for the machines that are not on the Domain.
0
Comment
Question by:Silverbrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41801961
These questions bring back memories. Unfortunately, it was so long ago that I don't remember the details, let alone which versions of SQL Server enabled which protocols by default. But you need to have 64-bit TCP/IP enabled for SQL Server and a corresponding 64-bit client enabled on the client side. You might have to upgrade the clients, and update the DSN.

I'm sure that someone that still works with this configuration can give you better information, but I saw the question just sitting here and wanted to give it a first shot.
0
 
LVL 1

Author Comment

by:Silverbrain
ID: 41801980
That would make sense as I believe almost all the clients are 32bit Windows 7.  That's a fresh install of windows, or move them to the RDP which is a 64bit server in that case, or is there another option?
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41801984
I don't think you need to upgrade WIndows, but what is on the machines now probably doesn't include a SQL Server 64-bit client, or the DSN isn't using it. I could have told you exactly what to do back in 2009, but right now it would be nice if someone who actually remembers would happen along. :-)

I will look it up when I am free, if you haven't received a solution by then.
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41802042
While you are checking for the appropriate ODBC drivers, can you check the firewall settings? Newer operating systems have traffic disabled by default and that may causing the server to appear "offline".

Also, on the SQL Server, do ensure that remote access is enabled for user connections (https://msdn.microsoft.com/en-us/library/ms191464.aspx).
0
 
LVL 1

Author Comment

by:Silverbrain
ID: 41802114
Nakul, I checked the Remote Server connections and it is allowed, timeout of 600.  

The workstations are all Windows 7, and they worked fine before the upgrade from 32-bit to 64-bit.

I also disabled the firewalls on both sides, same issue.
0
 
LVL 16

Assisted Solution

by:Megan Brooks
Megan Brooks earned 250 total points
ID: 41802193
I did a little Googling, and remembered why I wanted someone else to answer this. Confusion reigns.

One interesting thing I read about was 3rd party client software that just plain breaks when asked to connect to a 64-bit server. I hope it isn't that.

This might not be an ODBC issue, as you originally noted. Have you looked for login failures in the SQL Server log? I remember seeing connections trying to authenticate anonymously instead of with the Windows credentials. I don't remember how we fixed it. The client PC and server's Windows security logs can also yield clues, although you might have to turn on security auditing.
0
 
LVL 85
ID: 41803646
Try creating your connection directly in Access, and not by using the ODBC applet.

If you can't do that, try creating your connection using the obdbad32.exe applet found in Windows\System32.
0
 
LVL 1

Author Comment

by:Silverbrain
ID: 41805033
Scott, I will try your suggestion, but I honestly do not know much about access.  

Using odb tool shows the test connection is successful, but the program just throws the odbc call failed.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41806279
To create it in Access, you essentially just link the tables, using the External Data - ODBC option. Create  NEW DSN, and go from there. In many cases, developers will put in code that will automatically relink their application on startup (or when needed).

But from what you wrote, it would be best to create it with the obdcad32.exe applet instead.
0
 
LVL 1

Author Comment

by:Silverbrain
ID: 41809220
Linked tables in Access.  I did this for a workstation (file on the server) and it worked, but the next workstation did not, and if I updated the linked tables, the first workstation stops working, even though they have the exact same ODBC DSN and permissions on the server.

At this point, I have set it up so everyone, one way or the other has to authenticate on the domain (the Medical Charting program is not a domain fan, says it causes issue with their server but oh well.  I have copied the access file down to everyone desktop and updated the linked tables and set the DSN the same.  This is working for my client.

I appreciate all the feedback.  (been a member here since 1997, but been a long time, so forget the points system on the site, if I do this wrong, I will correct it.  

Thanks again, Jason
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question