Access 2013 database with SQL backend crashing after Windows server upgrade from 2008 to 2012

I have combed all the archives and not found any answers so here we go. I have a very custom Access application that we upgraded to Access 2013 with much ado because they removed the lovely .adp functionality. Now the database connects through a file ODBC connection (Native Client 10.0) to SQL Server 2012. All queries (hundreds of them) have been converted to pass-through queries using the same ODBC connection. It was working fine.

And then...upgrade to Windows Server 2012 r2. Now the front ends (each user has their own copy on their RDP session) are intermittently crashing with the message "Your network access was interrupted. To continue, close the database, then open it again.". Many times the user has to use task manager to close it.

We checked on the possibility of SMB causing the issues and it has been ruled out.       https://www.petri.com/how-to-disable-smb-2-on-windows-vista-or-server-2008.

So I'm focusing on the ODBC driver. It is a massive undertaking to upgrade the SQL Native Client driver from 10.0 to the latest 11.0. I don't want to go to all the trouble if it is not going to fix the problem. So the big question is "Can the use of an older Native Client driver cause connection drops on on my Access Database? ".
tlucksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Where did you try to tune or switch SMB2 off? On client's workstations or on the server?

"each user has their own copy on their RDP session" ...
Where is this copy placed? On the local harddrive of the server where the RDS session resides?  Or is it on some mapped (or even virtual) drive?

This does not seem to be a problem of ODBC and you should identify what really means the message "Your network access was interrupted".
This cannot cause SQL Pass through command but rather the Access db placed on a mapped network drive. And the network is unstable in this case.

Of course, if many users are downloading data from SQL Server to their shared Access db then it could cause some drop outs which in turn behaves like overloaded network and causes problems.

So you'll need some monitoring to identify the problematic part of your infrastructure.
0
Luke ChungPresidentCommented:
If you are going to upgrade, you should install tie latest version of the SQL Server driver which will include support thru SQL Server 2016 SP1.

More info in my paper here:
http://fmsinc.com/MicrosoftAccess/cloud/link-to-azure-sql-database.html
0
tlucksAuthor Commented:
I was able to set up test situation with the latest driver by creating a blank Access 2013 database and connecting to to just a few tables in my backend using the the SNCL 11.0  and got the same error so I'm inclined to think it will not be worth it to upgrade that driver.

The client copies of the database are stored on a Windows 2008 file server. Each time a user logs into RDP, a copy of the front end with a unique id is created for that user.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
If your RDP sessions are on W2012R2 then you must copy the database to physical disk on this server for each user. If you are using some file server on another computer then the Access database is accessed over the network with all negative consequences of network interruptions.

Of course, you should also identify why W2008 file server disconnects from a network intermittently. Maybe you could look at this article: https://support.microsoft.com/en-us/help/297684/mapped-drive-connection-to-network-share-may-be-lost
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
The front end database shouldn't have a new name for each user. Our should just be copied to their user profile folder where they'll have write permissions.

More info in this paper I wrote about configuring a remote app environment:
http://fmsinc.com/MicrosoftAccess/terminal-services/remoteapp.htm
0
pcelbaCommented:
Does the "unique id" mean "new name"?
0
tlucksAuthor Commented:
I think we are on the right track pcelba! I had the same thought last night and have had a front-end running on the desktop of my RDP since last night with no interruptions.  As far as the "unique id" yes I mean a name which is created when a user logs onto a RDP session. We were having issues when a user logged onto multiple RDP sessions and getting the "Access DB is Read-only" messages and this is how we solved it. I am going to have the network guys reconfigure so the front-ends live on the Windows Server 2012. I will award points to you when we know for sure this is the solution. Thank you.
0
pcelbaCommented:
Great!  The desktop is on the server's C: drive obviously and if you have sufficient space on this drive then it should be OK.

I also don't see any problem with several different local DB names for the frontend when shared application data are all on the SQL Server.
0
tlucksAuthor Commented:
Although we still don't understand why the upgrade to Windows 2012 r2 is causing our mapped drives to drop  when the configuration was working fine with Windows 2008, this solution allows us to use the database without the error. Disk space is a problem on the RDP server so IT is still looking for an answer as to why the file server is suffering dropped connections.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.