?
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
?
96 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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: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 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: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 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: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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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