Sql Server Timeout From net Does Sql Continue Running the Process
If you are saying that:
You launch Access. Connects to sql server without problem.
Now, KEEP access open, keep the program running.
Now you allow your computer to go to sleep, or close the lid (which puts computer in sleep mode).
You now "wake up" computer (such as open lid).
You of course NEVER shut down access here yet.
You never exited access here.
At this point, your network connection is broken. You sitting on that access form looking at what you had before you "sleep" the computer.
At this point, any attempt to get/grab/use/pull data from SQL server is going to show a lost connection and you get that logon prompt.
There is not really a fix for this issues.
When you put the computer to sleep, but LEAVE access running, the problem is the network connection is cut.
Access (unfortunately) does not have provisions for re-establishing the broken connection.
You "break" that connection, then only solution is to exit access, and re-start.
You only real solution here is to ensure that you exit Access before you let your computer sleep.
In other words, you have to re-start Access.
I don't believe the "time out" is the real issue. You can test above, and only let the computer sleep for 1 minute. It not really a timeout, but that's the error message – the problem is that your network card (connection) went to sleep.
However, if you are saying that after you exit access, let the computer sleep (close lid), and the wake up the computer, launch access and you get this message? That is "different".
So VERY important to determine the above "steps" that occred.
This seems to suggest that your network card (or wi-fi) is NOT YET active. On some computers, until such time you "do something" to cause network activity, the sleeping network card does not wake until something attempts to use the network card.
The only real solution here is to ensure uses exit access before they "sleep" the computer.
However, if you are using Access 2016, then install the "latest" ODBC driver.
Install the latest native driver 17 from here:
https://www.microsoft.com/en-us/download/details.aspx?id=56567
After you install the above, you can try and re-link the tables. (you MUST create a new table link, and choose native 17.
The newer ODBC drivers have some "recovery" options, and they can work with Access 2016 that can "recover" from this broken network connection.
However, with the "legacy" and "standard" ODBC driver, Access is not able to recover from a broken connection.
Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Was this reply helpful?
Thanks.
Connection: The SQL Server Tables are linked to the mdb with a User DSN. This DSN is based on the 'SQL Server' driver. (Microsoft SQL Server ODBC Driver Version 06.01.7601)
Authenticity of the login is verified with Windows NT using a network login ID.
The data source can be tested successfully in the ODBC Data Source Administrator.
Turn off the computer. Turn on the computer. Launch the mdb for the first time since the computer has been turned off and this error appears:
Microsoft SQL Server Login
Connection failed:
SQL Server Error: 0
[Microsoft] [ODBC SQL Server Driver] login timeout expired
Press <OK>.
Another message appears:
A SQL Server login is presented with the Use Trusted Connection checkbox marked. The Login ID and Password text boxes are not enabled.
Press <OK>
The mdb opens and operates as normal.
The mdb can be closed and opened again and the two SQL messages do not appear.
If the computer is turned off then the messages appear the first time the mdb file is opened.
Thanks.
Was this reply helpful?
Great follow up. That "sequence" of events is important and tells a "story".
This sounds like some network issue as opposed to your DSN.
However, what I would do is re-link the tables using the linked table manager, and MAKE SURE you use a FILE DSN.
The reason for above is access then when re-linking the tables will use a DSN-less connection. Since you using windows auth, then you really don't need a DSN anyway.
With tables linked as a FILE DSN, then the access front end application can be deployed to any workstation, and no DSN is required on those workstations at all.
The message suggests that the current links have some kind of password (they are not setup for windows auth).
Access is quite smart, and even with WRONG linked tables, the ODBC logon prompt will pop up – and if you just "ok" to these prompts, access will try and connect, and if success, the it IGNORES you "incorrect" linked table connection, and using "anything" that allowed it to connect.
So a re-link (you can re-refresh as to not have to delete the links).
So hold down the shift key as to NOT allow any ODBC prompt, or connection attempt to occur. (no startup code + forms is to be allowed to run - if something does THEN YOU MUST start over). Launch the linked table manager, and make sure you check [x] Always prompt for new location.
At this point, Access will launch the ODBC connection manager, and you create a FILE dsn.
Doing the above should fix this issue. My best guess is that the linked tables are not setup for windows auth, and likely have a sql logon + password. As noted, once Access connects, any way, then it will work, and actually "ignores" the information in the linked tables (including that DSN).
As noted, make sure you create a FILE DSN, since once that works, then you 100% eliminate the need to setup a DSN on each computer. You only need Access + linked tables.
Regards
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
2 people found this reply helpful
·
Was this reply helpful?
I created the File DSN and the SQL Server does not throw the 'Connection Failed' message.
Thank you so very much!
Was this reply helpful?
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/sql-login-timeout/eb9f0331-c41b-42b1-ab4c-05313ccb42e8
0 Response to "Sql Server Timeout From net Does Sql Continue Running the Process"
Post a Comment