Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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
?
100 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 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