[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSDE revokes database access

Posted on 2007-08-09
22
Medium Priority
?
373 Views
Last Modified: 2013-11-05
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!
0
Comment
Question by:PeterMauger
  • 13
  • 9
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19667783
>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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19667787
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
 

Author Comment

by:PeterMauger
ID: 19667866
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19668064
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
 

Author Comment

by:PeterMauger
ID: 19668203
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
 

Author Comment

by:PeterMauger
ID: 19668210
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 19668289
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
 

Author Comment

by:PeterMauger
ID: 19674702
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
 

Author Comment

by:PeterMauger
ID: 19681627
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
 

Author Comment

by:PeterMauger
ID: 19788509
Is anyone willing to have a crack at this one? Otherwise I'll just take it to MS...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19788698
I must have missed the previous comment notif, and now the uploaded file seems to be no longer present?...
0
 

Author Comment

by:PeterMauger
ID: 19788799
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
 

Author Comment

by:PeterMauger
ID: 19788820
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19788824
check out www.ee-stuff.com
0
 

Author Comment

by:PeterMauger
ID: 19788826
Oh and an additional note to the above, the service is installed and runs under NetworkService account.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19788830
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19788842
>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
 

Author Comment

by:PeterMauger
ID: 19789004
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19789126
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
 

Author Comment

by:PeterMauger
ID: 19789549
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
 

Author Comment

by:PeterMauger
ID: 19975700
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 setup several different housekeeping processes for a SQL Server.

830 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