dshockey
asked on
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?
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?
have they applied any software... or patches or upgrades recently...?
have they disabled/recreated any accounts/security policies recently?
which OS are they using?
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.
I recommend trying this: Set the Auto CLose to FALSE and see if that solves the issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is the only "solution" since the problem cannot be recreated.
Just a thumb suck though, hard to tell without knowing the environment / infrastructure etc.