MSDE revokes database access

I developed a commercial application that utilises an MSDE database as the backend.

The installation process installs a separate instance of MSDE and then generates the database through SQL Scripts. Part of the installation process is to create SQL logins for the services that run on the server to be able to connect to the database. These are then assigned access to stored procedures through roles.

A more and more common occurance amongst my clients (I believe about 10-20% are currently effected) is that these service logins suddenly, and without apparent cause, have their access to the database revoked (or possibly just screwed up).

The message that occurs is "Cannot open database requested in login 'GlobaliQProperty'. Login fails.
Login failed for user 'FusionService'.".

As I mentioned this is happening often enough for it not to be coincidence or to do with the particular setup in a client's office. It occurs on both XP and Server 2003 machines (possibly also 2000 and Server 2000 but I can't remember any off the top of my head) and, try as I might, I can't find any kind of consistent cause of this problem.

The current solution is that there is a part of the installation that repairs the logins (using the master stored proc sp_change_users_login with @Action set to AUTO_FIX and @UserNamePattern set to the broken login) and requires the user repairing the installation on their server. The problem with this is that most of the users of the system don't have access to their server and need to call in their IT guys (at significant cost) to perform this task. While it's not fixed the system doesn't work.

This one has been an issue for me for around 2 years and has baffled every DBA and developer I've ever asked about it.

Hoping someone can help!
PeterMaugerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you might request the nt eventlog and the sql server ERRORLOG file, those might already give some information about any system problems that might have occured...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Cannot open database requested in login 'GlobaliQProperty'. Login fails.
>Login failed for user 'FusionService'.".

that means that the database that is set up as default database (GlobaliQProperty) for the login FusionService is not granted access to that login, does not exists at all, is defined as single-user, is offline ....

check/fix that, and the error should go away.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, how do you deploy the database? by a simple attach? then, your application installation should do the sp_change_users_login itself, or the application should incorporate that call directly as "repair" method...
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
PeterMaugerAuthor Commented:
I understand that the problem is the logins haven't got access to the database but this is happening after 2 or 3 months of perfect function followed by a very sudden, and unexplained, loss of that access by the logins. ie the access is there and granted and then it suddenly isn't, but there is no script or stored proc that has any transact SQL that would perform this task.

The installation process to create the database is purely scripts (not attachment or restoration of an existing file) executed against the newly created MSDE instance. This part works perfectly for every installation and the database is generally functioning and then *pop* it doesn't work and a login repair has to be undertaken.

Most of my clients have never had this problem but a few of them have it happen almost once or twice a month.

It's just bizarre...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if it worked, and all of a sudden, it does not work anymore, something has been done on the database/server.
like, they "manage" the msde database directly, like change logins, settings etc...
msdb database and security do not get changed by something from accesss directly, no way.
0
 
PeterMaugerAuthor Commented:
That's exactly what I thought but, to the best of my knowledge there is nothing I've put in the stored procs that would do this and the only access external entities have is to (most of) the stored procs through the public group. Everything else is locked down with SQL logins and very strong passwords (read 40-60 completely random characters for each login) and so there SHOULD be no way that the accesses could be changed... but they are! Builtin\Administrators is revoked and the dbo is one of the created SQL logins.

I had exactly the same thoughts as you when I first came across this and have since grown to accept that something screwy is going on in the DB engine as that's the only possible solution I can come up with. I've pretty much been googling this issue on and off since it started happening and I've NEVER found anything that comes even close to what's going on for my clients.
0
 
PeterMaugerAuthor Commented:
As a thought... is there anything you can think of that can reliably log an MSDE instance? If there is I might be able to get one of the clients it happens to regularly to log what's going on with their particular instance until it fails.

Obviously that could take months though...
0
 
PeterMaugerAuthor Commented:
OK, what I'll do is try and find a client currently (or recently) experiencing this problem and see if there is anything in the SQL Logs or Event Logs. It could take me a week or so to find the information though.
0
 
PeterMaugerAuthor Commented:
OK... Some interesting information from a client who detected the problem today. I've had a look through the files I asked them to send me (from the Logs folder and lists exported from the event viewer) and it appears that the problem occurred at 12:28 on 30th July. There is a SQLDump2.txt file that corresponds to this event and that indicates that a fatal unexpected error was detected.

The files are available at http://briefcase.yahoo.com/peter.mauger in the My Documents folder.

Unfortunately the event viewer list export didn't dump the property information (ie the details) so I'm going to see if I can get that for the events around the suspect time.
0
 
PeterMaugerAuthor Commented:
Is anyone willing to have a crack at this one? Otherwise I'll just take it to MS...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I must have missed the previous comment notif, and now the uploaded file seems to be no longer present?...
0
 
PeterMaugerAuthor Commented:
Let me have a look... Oh... whoops! Looks like I can't have a public shared folder on Yahoo unless I go 'Premium'. Damn... Do you know of somewhere I can put them that isn't going to cost me money? ;)
0
 
PeterMaugerAuthor Commented:
In the meantime... This is the message in the SQLDump2.txt

=====================================================================          
       BugCheck Dump                                                          
=====================================================================          
                                                                               
This file is generated by Microsoft SQL Server 8.00.760                        
upon detection of fatal unexpected error. Please return this file,            
the query or program that produced the bugcheck, the database and              
the error log, and any other pertinent information with a Service Request.    
                                                                               
Computer type is AT/AT COMPATIBLE.                                            
Bios Version is INTEL  - 64c                                                  
Current time is 12:28:52 07/30/07.                                            
2 Intel x86 level 6, 1864 Mhz processor(s).                                    
Windows NT 5.1 Build 2600 CSD Service Pack 2.                                  
                         
                                                     
Memory                    
MemoryLoad = 41%          
Total Physical = 997 MB    
Available Physical = 586 MB                        
Total Page File = 2404 MB  
Available Page File = 2069 MB                      
Total Virtual = 2047 MB    
Available Virtual = 1010 MB                        
                           
***Symbol load failed - Proceed to dump without any symbols                    
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL$GLOBALIQP
ROPERTY\log\SQLDump0002.txt                                                  
-----------------------------------------------------------------------------------------------------------
The rest is a stack dump for a bunch of modules referenced by hex identifiers.  

The corresponding event log information for the time of the dump is:

Source: Userenv
Time: 12:28:36 PM
Type: Warning
Event ID: 1517
User: NT AUTHORITY\SYSTEM

Description:
Windows saved user <domain\userid> registry while an application or service was still using the registry during log off. The memory used by the user's registry has not been freed. The registry will be unloaded when it is no longer in use.

This is often caused by services running as a user account, try configuring the services to run in either the LocalService or NetworkService account.


If this isn't particularly useful then let me know if you know somewhere to put the files where they'll be accessible.

Cheers.
Peter
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check out www.ee-stuff.com
0
 
PeterMaugerAuthor Commented:
Oh and an additional note to the above, the service is installed and runs under NetworkService account.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the last sentence IS useful:
>This is often caused by services running as a user account, try configuring the services to run in either the LocalService or NetworkService account.

means that your sql server service seems to be running under the logged in account, which is not recommended...
you should be useing a (dedicated) domain account.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the service is installed and runs under NetworkService account.
are you 100% sure about that, that the relevant instance is indeed configured like that?
because the error dump tells a different story...
0
 
PeterMaugerAuthor Commented:
0
 
PeterMaugerAuthor Commented:
On my dev box the MSSQL$GLOBALIQPROPERTY service logs on using Local System. I'm using an InstallShield pre-requisite to perform the installation using an ini file to set the parameters. I'm not setting anything in the ini file with regard to service logins (which I'm just finding out about in the SQL2005Express setup).

My services (three of them, two with individual SQL logins for their connections that are the source of all of these problems) are installed (on XP machines) under NT AUTHORITY\NetworkService (2000 machines require a user login but these don't appear to have the same issues). Interestingly there is nothing in the event log that actually indicates which service caused the warning. The fact that it came from the NT AUTHORITY\SYSTEM user would SEEM to indicate that it is indeed the SQL service but then the reference to the user's current login seems out of place.

Could it have something to do with the fact that the user's domain login is utilised for the SQL connection? (ie having nothing to do with the actual faulty logins, but something in the crash causes a corruption in the user table?).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AFAIU, on your box you don't have the problem?
but did the client(s) change the login settings maybe on their side?

except of the exception dump itself there seems nothing abnormal so far, just 1 thing:
you have antivirus software running on the computer(s)? does that skip the database files?
0
 
PeterMaugerAuthor Commented:
It's extremely unlikely that the login was changed but I will investigate it. Most of my clients aren't particularly technically adept and their IT support is generally not much better than they are. As such I wouldn't see many of them changing the login... Still I'll check it out.

Unknown on the anti-virus issue. My AV isn't specified to skip the database files (but may not scan them anyway)... Actually correct that my AV doesn't scan the db files. Again, I'll look into it. I'll get back to this when I've found out more.

Cheers.
0
 
PeterMaugerAuthor Commented:
Hi angellll, I'm going to put this one to bed because I'm focussed on migrating the whole system to SQL Express 2005. Your help certainly gave me a much better understanding of the problem but I'm not even sure there is a solution to this given the age of the product (ie MSDE being around 6 or 7 years old and 2008 on the way). I've given you the points for your assistance but anyone looking to this question for a solution to a similar problem should realise that it hasn't actually been resolved and you'll need to pursue the information here further.

Cheers,
Peter
0
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.