Bizarre SQL Server 2005 Authentication Behavior
Posted on 2011-10-25
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?