Bizarre SQL Server 2005 Authentication Behavior

Posted on 2011-10-25
Last Modified: 2012-05-12
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?
Question by:dshockey
    LVL 5

    Expert Comment

    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.
    LVL 50

    Expert Comment

    have they applied any software... or patches or upgrades recently...?

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

    which OS are they using?
    LVL 2

    Expert Comment

    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.
    LVL 1

    Accepted Solution

    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.
    LVL 1

    Author Closing Comment

    This is the only "solution" since the problem cannot be recreated.

    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 I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now