Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
104 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:Jason Ivey
  • 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:Jason Ivey
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

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:Jason Ivey
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 1000 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:Jason Ivey
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 1000 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:Jason Ivey
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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