SQL Login Timeout

Question

I am working with an older mdb file as a Front End running in Access 2015 on a local computer.  The Back End is SQL Server 2014.  SQL Server runs on a server.

An ODBC DSN on the local computer links the SQL Server tables to the mdb.

A form is displayed when the mdb opens based on a setting in Access Options\Current Database\Display form.

The following scenario happens the first time the mdb is opened after the local computer has been closed and then turned on:

The form named in Access Options opens.

The user then opens another form that gets its data from a linked SQL table.

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.  The user can press <OK> on this message box and the form opens and the mdb operates as normal.

The mdb can be closed and opened again and the two SQL messages do not appear.

The mdb will continue to operate normally until the computer is turned off and turned back on.

The ODBC DSN was created under a User DSN with the ‘SQL Server Driver’ and it never fails to connect to the database when it is tested.

I can work around the timeout error with an error trap in the first form that is displayed when the mdb is opened but my questions are:

If the ODBC DSN operates then why does SQL Server throw a timeout error?

Secondly, why is the timeout error thrown only when the local computer is started after being closed?

Finally, what have other people done to work around this or similar issues?

I suspect this is a DSN issue but I am not sure.  I am not sure how to troubleshot the issue and I don't know what can be done. 

Thanks All.

Answer 1

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

Answer 2

I created the File DSN and the SQL Server does not throw the 'Connection Failed' message.

Thank you so very much!

Answer 3

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.

Answer 4

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