Bizarre SQL Server 2005 Authentication Behavior

This is a very weird issue that came up with one of my customers. On their computer I have a Windows service executable that runs as the local system account. On this same computer, I have a named instance of SQL Server 2005 Express Edition. The SQL Server service for this instance also runs as the local system account. My executable authenticates with the named instance of SQL Server via OLE using integrated authentication.

Today my customer called me to tell me that the software wasn't working. When I started looking into it, I found that the authentication of my service was being rejected by SQL Server. I looked very closely at all of the settings and couldn't find anything that was wrong. I stopped and restarted both the SQL service and my program's service. No change. In desperation, I backed up the original database, created a second database, and restored the data from the original database to the second database. I then modified the configuration for my program to use the second database. Suddenly everything worked. I reevaluated the settings and still couldn't find anything. Finally, I had the user reboot the whole computer. Everything worked again.

I'm very worried about some underlying condition that still exists and will reappear at a future time. Does anybody have any ideas for what this might be or how to go about getting good diagnostic information if this happens again?
Who is Participating?
dshockeyAuthor Commented:
Thank you for all of the suggestions. The issue has not reappeared since when I first wrote about it and the customer has been using the software daily. For this reason, and because it's never happened elsewhere, I have no way to try or test the things that were suggested.

If it does happen again, I will try out the things suggested here. For now, i have to close it as unresolved.
Sounds to me like a domain policy re local system accounts might have changed?

Just a thumb suck though, hard to tell without knowing the environment / infrastructure etc.
have they applied any software... or patches or upgrades recently...?

have they disabled/recreated any accounts/security policies recently?

which OS are they using?
I know SQL 2008 express has a "feature" in the database properties called Auto Close that by default (on express only) is set to TRUE. With this as true, it will close the database when it isn't in use and then open it again. But, i have seen situations where it doesn't open fast enough and the program trying to access it times out and gets a connection error.
I recommend trying this: Set the Auto CLose to FALSE and see if that solves the issue.
dshockeyAuthor Commented:
This is the only "solution" since the problem cannot be recreated.
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.

All Courses

From novice to tech pro — start learning today.