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
69 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

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 13

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
 
LVL 11

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 13

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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now