Solved

Previously Stable DSN Suddenly Stops Working.  Why?

Posted on 2015-01-16
16
349 Views
Last Modified: 2016-02-11
Does anyone know what might cause a SQL Server DSN that has been working for years suddenly stop working?  Yesterday, one of my legacy apps (designed and running on Access 2007) stopped working (ODBC connection failed).  When I went to check the DSN configuration, I was told over and over again that the login failed (the same login that has been in use since about 2010 or so).  I am the only person who has access to the inner workings of this application and the database (SQL Server 2008 R2 Express) that feeds it and I haven't changed anything since 2010 (when the same thing happened).

I thought a Windows Update might have been the culprit so I did a system restore to reverse it, with no luck.  All I could do was create a new DSN and then go through the very tedious process of updating all my linked tables and (even more tedious) updating the connection string on over 100 pass through queries.  I'd sure like to know what's causing this and how to avoid it in the future.

Thanks!
0
Comment
Question by:penlandt
  • 6
  • 4
  • 2
  • +3
16 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40554020
I received an email on Wednesday from a client reporting receiving an ODBC connection error (Access front-end to SQL Server) similar to what you're describing.  I replied requesting to have the machine rebooted and to have them contact me if no joy.  I haven't heard back but it is a small office so it's not unusual.  I'll follow up with them to see if the problem persists and post back.  Seems awfully coincidental.
OM Gang
0
 
LVL 57
ID: 40554055
Check the event log on the station where the ODBC login failed....see if you have any errors or critical events.

There are a multitude of problems that all can result in a failed login.

Are you using a trusted connection or SQL server login?

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40554081
Did you try simply refreshing the links?

Are you performing regular maintenance on the Front end.
Compiling the code, and secondarily, running the compact/repair utility.

Also check to see if the ODBC drives need to be updated.

I haven't changed anything since 2010 (when the same thing happened).
Not sure, wild guess here......but check to see if all our vba references are OK

When you moved to 2010, did you also change the db format .mdb-->.accdb?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40554087
I have no joy for you on cause.
However, on pain relief, I have some analgesics
This enumerates my passthroughs and their connection string
Public Function RelinkPassThroughQueries(ConnType As Integer)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

On Error GoTo Err_RelinkPassThroughQueries
Set db = CurrentDb

For Each qdf In db.QueryDefs
    If qdf.Type = dbQSQLPassThrough Then
        MsgBox qdf.Name & vbCrLf & qdf.Connect
    End If
Next
RelinkPassThroughQueries = True

Exit_RelinkPassThroughQueries:
Set qdf = Nothing
Set db = Nothing
Exit Function

Err_RelinkPassThroughQueries:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Relink pass-through queries"
Resume Exit_RelinkPassThroughQueries
End Function

Open in new window

Change MsgBox qdf.Name & vbCrLf & qdf.Connect to
qdf.Connect = "YournewValidConnectionString"  and you can get the passthroughs done programmatically


Similarly, this is adapted from my code for QueryDefs
Function Refresh_Query_Link()
'On Error GoTo myErr
Dim QD As QueryDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each QD In CurrentDb.QueryDefs
    If Len(QD.Connect) > 0 Then
        'MsgBox QD.Name
        intSubStringLoc = InStr(QD.Connect, "SomeStringThatLet'sMeKnowThisIsOneIWantUpdated")
        If intSubStringLoc > 0 Then
           QD.Connect = "TheNewConnString"
        End If
    End If
Next


Exit Function
myerr:
MsgBox QD.Name
Resume Next
End Function

Open in new window


And tables
Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "SomeStringThatLet'sMeKnowThisIsOneIWantUpdated")
        If intSubStringLoc > 0 Then
            TD.Connect = "TheNewConnString"
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

Open in new window


It remains a PITA, but perhaps a less tedious one.
0
 
LVL 1

Accepted Solution

by:
penlandt earned 0 total points
ID: 40554133
Thanks for the comments.  Answers to the questions that have been asked:

* The database uses a SQL Server login, not a trusted connection.
* The app has never been upgraded beyond Access 2007 (there was no need since it has been stable and the client is happy with it).  All changes have been made within the Access 2007 IDE to avoid having any unexpected modifications occur due to working within a more recent version.
* I tried refreshing the links first, however I was prevented from doing so by the login failure.  All I could do (as far as I know) is create a new DSN and use that to refresh the links.

Thanks for the suggestion Nick67.  I will definitely use that if (when) this happens again.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40562158
The issue still persists or didn't happen again?
0
 
LVL 1

Author Comment

by:penlandt
ID: 40563511
It has happened twice, once in 2010 and again just before I posted this question.  Still no idea why though.  It has not happened again since then though, if that's what you mean.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40563811
This one will be hard to solve then. Let's wait for 2020? :)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40567618
What the HELL did MS do?
I'm glad I replied and was interested in this one.
This afternoon, at ~4:00 MST all of my machines that WEREN'T my SQL Server no longer accept the following connection Provider=SQLNCLI11;SERVER=MyServer\MyInstance;DATABASE=MyDatabase;Integrated Security=SSPI

There's no SQNCLxx entries in any of the affected machines HKEY_CLASSES_ROOT\SQNCLIxx anymore!

I've had to switch the connection string to:

Provider=SQLOLEDB;Data Source=MyServer\MyInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI

Did they stealth uninstall all SQL Native Client libraries?
0
 
LVL 1

Author Comment

by:penlandt
ID: 40579175
I've requested that this question be deleted for the following reason:

Evidently there is no solution.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40578749
I would ask that you NOT delete this.
There may have been no solution, but it is clearly not an isolated issue as I had it occur after you did.
And there is little or anything else in Google about a potential problem.
Please ask for moderator aid in closing this question instead of deleting it.
It has long term value.

Nick67
0
 
LVL 1

Author Comment

by:penlandt
ID: 40579176
I don't mind Nick67, but Is there some way to get EE to stop bugging me about it?  They marked it an "abandoned" question which sounds like something I wouldn't like to have on my record here.  If you don't know I'll check with them.  Thanks!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40579179
It can be as simple as writing up a summarizing post and accepting it as the best answer of multiple answers and assigning points to other posts.  You can accept a post of your own as a sole answer, and that tosses the Q into a waiting period for any objections as no points are then allocated.

Nick67
0
 
LVL 1

Author Comment

by:penlandt
ID: 40579184
Your last post sounds like a good summation to me.  You wrote it so you get the points. ;-)  Thank you!.
0
 
LVL 1

Author Closing Comment

by:penlandt
ID: 40588272
Accepted as answer as instructed by admin.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now