asked on Access Client session timeout
Hello Experts,
We have an issue with Access losing connection to Databases.
We have the same issue with multiple Access databases. Some databases use data sources from SQL, some do not.
To Simplify my question and provide as much detail as possible with keeping it simple, lets say;
I have "Access Database A" that resides on Server1, and uses SQL server 2008 as back end.
I have "Access Database B" that resides on NAS1, and does not use SQL.
Our existing terminal server (Server 2008 R2) does not have any issues, a user can open either of the two databases in Access, and work in them with no problems. Server is running Office 2013 x86.
Now, we stood up a new 2012 R2 Terminal server (RDS), also with Office 2013 x86, and users are getting disconnected from the databases. They get disconnected from either of the two databases, so I have ruled out SQL being an issue. ODBC connectors are set up identical on both terminal servers. I cannot find any data in the event log that is relevant to any Access disconnects.
Also, network connectivity is not the problem, when a user loses connectivity, their RDP session is not dropped, and they have mapped drives (that exist on server1 and NAS1) that are also not affected.
I am looking for a way to enable logging to determine why Access is dropping connections randomly. Is there an advanced logging feature? Are there any timeout or keepalive settings I can look for?
Some more information, it seams like Access loses connection to the databases after some time of inactivity (we are working on gathering this data too), but no users have yet been disconnected while actively working in Access.
Any help is appreciated. Thank you.
We have an issue with Access losing connection to Databases.
We have the same issue with multiple Access databases. Some databases use data sources from SQL, some do not.
To Simplify my question and provide as much detail as possible with keeping it simple, lets say;
I have "Access Database A" that resides on Server1, and uses SQL server 2008 as back end.
I have "Access Database B" that resides on NAS1, and does not use SQL.
Our existing terminal server (Server 2008 R2) does not have any issues, a user can open either of the two databases in Access, and work in them with no problems. Server is running Office 2013 x86.
Now, we stood up a new 2012 R2 Terminal server (RDS), also with Office 2013 x86, and users are getting disconnected from the databases. They get disconnected from either of the two databases, so I have ruled out SQL being an issue. ODBC connectors are set up identical on both terminal servers. I cannot find any data in the event log that is relevant to any Access disconnects.
Also, network connectivity is not the problem, when a user loses connectivity, their RDP session is not dropped, and they have mapped drives (that exist on server1 and NAS1) that are also not affected.
I am looking for a way to enable logging to determine why Access is dropping connections randomly. Is there an advanced logging feature? Are there any timeout or keepalive settings I can look for?
Some more information, it seams like Access loses connection to the databases after some time of inactivity (we are working on gathering this data too), but no users have yet been disconnected while actively working in Access.
Any help is appreciated. Thank you.
Microsoft AccessDatabases
Last Comment
CCtech
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
"Perplexing problem...is the new server running in a VM?
Do these stations loose connectivity to the NAS as well?
What are you seeing when a disconnect occurs?
Jim."
Thanks Jim.
All servers are VMs running on the same hosts. The host is ESXi 6, VMs are using VMXnet 3 NICs.
Yes, they lose connectivity to the NAS (Access Database, but not mapped drives on NAS) as well.
As far as the user experience when this occurs; They may be working in Excel or Outlook, ect. and click back over to Access and have the error message "Your network access was interrupted. To continue, close the database, and then open it again." This error message overlays the Access screen, which shows empty values, for example the screen may have had Date / Employee March / Brian and now shows #name with no data.
As for the network latency, I have been running PingPlotter from the RDS server to the servers and NAS IPs containing the databases, and there have been no drops for 48 hours, the highest latency was 1ms, so that cannot be an issue.
Looking through logs I do see many kerberos errors indicating that one of the servers hosting SQL / Access database has duplicate SPNs. I am not sure if it is related or not, but I would like to resolve to eliminate as a possible interruption. When I run
setspn -x
it shows duplicate entries for servername;
SERVERNAME.DOMAIN
SERVERNAME.DOMAIN:1433
All technet articles I read say to only have one SPN, but I am not sure how to find out which of these two SPNs are actually in use. Is there an easy way to go about this?
Do these stations loose connectivity to the NAS as well?
What are you seeing when a disconnect occurs?
Jim."
Thanks Jim.
All servers are VMs running on the same hosts. The host is ESXi 6, VMs are using VMXnet 3 NICs.
Yes, they lose connectivity to the NAS (Access Database, but not mapped drives on NAS) as well.
As far as the user experience when this occurs; They may be working in Excel or Outlook, ect. and click back over to Access and have the error message "Your network access was interrupted. To continue, close the database, and then open it again." This error message overlays the Access screen, which shows empty values, for example the screen may have had Date / Employee March / Brian and now shows #name with no data.
As for the network latency, I have been running PingPlotter from the RDS server to the servers and NAS IPs containing the databases, and there have been no drops for 48 hours, the highest latency was 1ms, so that cannot be an issue.
Looking through logs I do see many kerberos errors indicating that one of the servers hosting SQL / Access database has duplicate SPNs. I am not sure if it is related or not, but I would like to resolve to eliminate as a possible interruption. When I run
setspn -x
it shows duplicate entries for servername;
SERVERNAME.DOMAIN
SERVERNAME.DOMAIN:1433
All technet articles I read say to only have one SPN, but I am not sure how to find out which of these two SPNs are actually in use. Is there an easy way to go about this?
Well for the moment, I would move the Access databases to the new RDP server.
If all users are on that server, then networking is no longer involved and you should not see any disconnects.
As far as the SPN's, you certainly can have more than one for a given domain account. I don't see why that would be an issue.
Jim.
If all users are on that server, then networking is no longer involved and you should not see any disconnects.
As far as the SPN's, you certainly can have more than one for a given domain account. I don't see why that would be an issue.
Jim.
Did you check out the firewall? What is different between the working and non-working environments?
ASKER
Jim - I resolved the duplicate SPN issue and the kerberos errors went away, did not solve the problem as you suspected. Unfortunately I cannot move the databases; the new server has a small group of test users, the rest are still on old terminal server. If I moved the database it may break production.
Pat - The firewall is disabled on both old and new terminal servers. I am trying to compare environments, all looks the same accept for server OS.
Pat - The firewall is disabled on both old and new terminal servers. I am trying to compare environments, all looks the same accept for server OS.
Need a little clearer picture of what's where.
1. Are the Access DB apps split? Would seem so, but want to confirm. In other words, there is a front end on the RDP server and a backend MDB/ACCDB file some where.
2. Are the Front Ends shared by all users or does each have their own?
3. Where does the SQL database live?
I think I would focus on the apps using the SQL back end. SQL has a lot of logging between SQL's own logs and the system event logs. You should be able to find the reason why disconnects occur there from those.
Jim.
1. Are the Access DB apps split? Would seem so, but want to confirm. In other words, there is a front end on the RDP server and a backend MDB/ACCDB file some where.
2. Are the Front Ends shared by all users or does each have their own?
3. Where does the SQL database live?
I think I would focus on the apps using the SQL back end. SQL has a lot of logging between SQL's own logs and the system event logs. You should be able to find the reason why disconnects occur there from those.
Jim.
ASKER
Jim, there is a front end and a back end, they are split. All users share the front end.
We have moved two of the JET databases directly to the RDS server, and connection drops went away. I am troubleshooting networking with other databases now.
I will report back with more information from testing. We know that the database uses connectors of X;\something for example, and X is a mapped drive. We are now testing mapping drives manually via IP instead of DNS via GPO. On the networking end of diagnostics, The highest latency we have logged between RDS server and NAS holding a JET database is .9ms, so still does not make sense that it is networking.
Unfortunately this is intermediate issue, so when I make one change we need to test for a day or two before I see results, as they do not always use these databases. I will keep you updated with further diagnostics.
We have moved two of the JET databases directly to the RDS server, and connection drops went away. I am troubleshooting networking with other databases now.
I will report back with more information from testing. We know that the database uses connectors of X;\something for example, and X is a mapped drive. We are now testing mapping drives manually via IP instead of DNS via GPO. On the networking end of diagnostics, The highest latency we have logged between RDS server and NAS holding a JET database is .9ms, so still does not make sense that it is networking.
Unfortunately this is intermediate issue, so when I make one change we need to test for a day or two before I see results, as they do not always use these databases. I will keep you updated with further diagnostics.
Well good to hear your making progress. These types of problems can be very frustrating at times because Access will often have issues when every other application will not.
That's a by-product of the way the database engine processes and maintains it cache, which is on the milli-second level. It's far more active network wise than anything else.
<< All users share the front end. >>
This is something that should be corrected, even on the TS server for a number of reasons. I'm not quite as fervent about this as some, but it's a good practice and sharing should be the exception rather than the rule.
On a TS Server, it's not quite as critical, but it's still important.
In the end, I think you'll find it has to do with VM's and your move to the TS server may be the only way to live with it.
I've had a number of run-in's with VM's both with Access and other products where network errors occurred on a frequent basis, but were of course not supposed to happen<g>
I believe (but have no proof - I'm not a VM expert by any stretch), that you get the same type of problem which you had in the past with physical hardware and NIC's with diag protocols; they'd disconnect for a bit, run their diag, and then come back on-line. For everything but Access, that works, but not for Access.
Jim.
That's a by-product of the way the database engine processes and maintains it cache, which is on the milli-second level. It's far more active network wise than anything else.
<< All users share the front end. >>
This is something that should be corrected, even on the TS server for a number of reasons. I'm not quite as fervent about this as some, but it's a good practice and sharing should be the exception rather than the rule.
On a TS Server, it's not quite as critical, but it's still important.
In the end, I think you'll find it has to do with VM's and your move to the TS server may be the only way to live with it.
I've had a number of run-in's with VM's both with Access and other products where network errors occurred on a frequent basis, but were of course not supposed to happen<g>
I believe (but have no proof - I'm not a VM expert by any stretch), that you get the same type of problem which you had in the past with physical hardware and NIC's with diag protocols; they'd disconnect for a bit, run their diag, and then come back on-line. For everything but Access, that works, but not for Access.
Jim.
ASKER
Thanks guys, it does look to be a networking related issue that we cannot target the exact cause, but when databases are moved to local terminal server the issue goes away. We will continue to troubleshoot networking. Thank you.
There is none for the JET/ACE (access) DB's.
For SQL, there is the SQL logs and event logs.
<<Are there any timeout or keepalive settings I can look for? >>
There's no keep alive for either. LANMAN parameters can get in the way of the JET/ACE DB's, but not SQL server.
Default in Win 7 is 10 minutes I believe, but you say the mapped drives are not disconnecting, so that would seem to nullify that. Also it works OK with one server, but not the other (although there are server side settings for that).
Perplexing problem...is the new server running in a VM?
Do these stations loose connectivity to the NAS as well?
What are you seeing when a disconnect occurs?
Jim.