Solved

Various Errors and failure Audits starting SQL Server service

Posted on 2009-07-16
22
912 Views
Last Modified: 2012-05-07
We are running SQL Server 2005 on Windows 2003 R2 SP2.  Note that SQL 2005 was an upgrade from SQL Express and the instance name is still SQLEXPRESS.

Each time the SQL Server service starts, the following events are logged in the Application log:

Source: MSSQL$SQLEXPRESS
Type: Failure Audit
Event ID: 18456
User: NT AUTHORITY\SYSTEM
Computer: SQL1
Description: Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: 192.168.0.21]

This event is logged an additional 7 times.  Note that the IP address is that of server 'SQL1'.

Source: Report Server Windows
Type: Error
Event ID: 107
User: N/A
Computer: SQL1
Description: Report Server Windows Service (SQLEXPRESS) cannot connect to the report server database.

Source: Windows SharePoint Services 3
Type: Error
Event ID: 3760
User: N/A
Computer: SQL1
Description: SQL Database 'SharePoint_Config' on SQL Server instance 'SQL1\SQLEXPRESS' not found.  Additional error information from SQL Server is included below.  Cannot open datbase "SharePoint_Config" requested by the login.  The login failed.

This error is logged an additional 2 times.


The applications using these databases are all running correctly and as yet there are no end-user issues resulting from this
error.  However we have a maintenance plan that performs a backup each night and this is failing with the following errors logged for each database:

Source: MSSQL$SQLEXPRESS
Type: Error
Event ID: 18204
User: NT AUTHORITY\SYSTEM
Computer: SQL1
Description: BackupDiskFile::CreateMediaBackup: Backup device 'B:\Backup\SQL Server\database1\database1_backup_200907161136.bak' failed to create.  Opertating system error 3(error not found).

Source: MSSQL$SQLEXPRESS
Type: Error
Event ID: 3041
User: NT AUTHORITY\SYSTEM
Computer: SQL1
Description: BACKUP failed to complete the command BACKUP DATABASE database1.  Check the backup application log for detailed messages.


I've done quite a bit of searching to resolve this issue and all advice seems to point to some sort of permission issue.  However I've been unable to find advice with clear steps to resolve it.  

Thanks in advance for your help.
0
Comment
Question by:onek
  • 10
  • 7
  • 3
22 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 24876746
I would start with changing the service accounts (permanent or temporary). This might clean up some service permissions. Though it shouldn't run with those errors logged ... Strange.
0
 

Author Comment

by:onek
ID: 24876890
Thanks Qlemo.  Could you elaborate a little please?

The Logon tab of the service shows the user is set to NT AUTHORITY\NetworkService and has a password set.  Should I change these credentials to something else?  I do not know the password for this account, and am not aware of the implications of changing the password of this account.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24877048
There must be an service (likely Report Server) with account NT AUTHORITY\SYSTEM. NetworkService is a different account.

The implications of changing a service account:
LocalSystem has no network access, but full rights locally
NetworkService is restricted for local actions (non-admin), but can access network
domain or local user accounts are restricted as defined by their access privileges.

Any account used for services needs a special privilege, "login as service account" (or similar, don't know exactly at the moment). That privilege is set when you change the account in the Services applet, but not (necessarily) when using installers or scripts. Changing for and back, or redefining the account, in Services applet will "repair" this.

0
 

Author Comment

by:onek
ID: 24877092
Thanks Qlemo.

If I understand you correctly, you're suggesting I temporaily change the serice logon account for the 'SQL Server' service to some domain account, and then change it back to the NT AUTHORITY\Network Service.  However I'm not aware of the password for this account and would be cautious of resetting that account's password without understanding implications of what else in the system might be affected.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24877115
For that reason I recommend to start with Report Server. However, NetworkService does not have any password (even if it is looking like it does).
0
 

Author Comment

by:onek
ID: 24877179
Thanks Qlemo.  

Are you suggesting I change the logon account on the service 'SQL Server Reporting Services'?  This service is currently set to 'Local System account'?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24877215
NT AUTHORITY\SYSTEM = Local System account, so the answer is "yes".
0
 

Author Comment

by:onek
ID: 24877236
Sorry, still not clear on what you're suggesting.

Logon for 'Reporting Services' service is currently set to 'Local System account'.  You're saying that this is effectively the same as NT AUTHORITY\SYSTEM ?  So what are you suggesting I change?

Thanks for your patience with a beginner!
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24877265
Ok, let's take a step back. I've got the impression that "Reporting Services",  using the LocalSystem account, cannot log into MSSQL, because user information is missing in MSSQL, or the access rights for Windows Authentication in MSSQL are not defined accordingly.
Since it is much easier to change the Reporting Services account then to check for the MSSQL privileges, I recommend to use an account you know of having access in MSSQL.

0
 

Author Comment

by:onek
ID: 24877307
Thanks Qlemo.

It's an odd situtation.  Just to clarify: As with all the SQL databases hosted on the server, Reporting Services is also working fine and we don't have any end user problems.  The only actual probelm that is causing a problem in SQL Server is that the Maintenance plan for daily backups is failing (as detailed in the question).  My thinking was to try and resolve the first SQL-related error in the logsand that is the login failure that's logged when the 'SQL Server' serivces is started.

Based on that, to confirm, you're suggesting I change the logon account of 'Reporting Services' to, say, a domain admin accound, and then restart the service to see if it makes a difference, and then change it back to 'Local System account'?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 68

Expert Comment

by:Qlemo
ID: 24877329
This was confusing me, too, but the first log entry is not from starting MSSQL service - if it were, the service would not run at all. The log entry is made by MSSQL service, but for a login made later, and I suppose it is Reporting Server. It might not have any visible influence, however maybe there are some internal reports which do not run.

The answer again is "yes".
0
 

Author Comment

by:onek
ID: 24877384
Thanks Qlemo.  Understood.  

I've just disabled 'Reporting Services' and then restarted 'SQL Server and this time the only non-information events logged were the 7 x Failure Audits:

Source: MMSSQL$SQLEXPRESS
Event ID: 18456
Description: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. (CLIENT: 192.168.0.21)

On restarting 'Reporting Services' we get the additional error for 'Reporting Services' as well as 4 x Error for Windows SharePoint Services:

Source: Windows SharePoint Services 3
Type: Error
Event ID: 3760
User: N/A
Computer: SQL1
Description: SQL Database 'SharePoint_Config' on SQL Server instance 'SQL1\SQLEXPRESS' not found.  Additional error information from SQL Server is included below.  Cannot open datbase "SharePoint_Config" requested by the login.  The login failed.

As we do not use 'SharePoint Services' as much as 'Reporting Services', I wondered if I should change the logon account on this service first (in case something breaks!).  However, there is no service listed with Name 'Windows SharePoint Services 3' listed in Services.  It lists 'Windows SharePoint Services' for Administration, Search, Timer, Tracing, VSS Writer but nothing 'Windows SharePoint Services 3', so I am confused!
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24877398
Sorry, I'm not SharePoint'ing ;-), so I can only guess it is the Administration or Search.
Interesting, now you tell about "Network Service", while in first post it was "SYSTEM" ...
0
 

Author Comment

by:onek
ID: 24877459
Yes, well spotted.  I've checked the failure audits logged today and 5 of them refer to 'Network Service' with the 6th referring to 'Mydomain\SharePoint'.  Of the events logged earlier in the week, some referred to 'Network Service' and others to 'System'.  
0
 

Author Comment

by:onek
ID: 25067155
Sorry for the delay in updating this question.

Qlemo has made some useful suggestions but I am unsure where to go from here.  Should I be looking at SharePoint, SQL Server, Windows..?  I am reluctant to start changing permissions without clear instruction and an understanding of potential implications.

Can anybody add to Qlemo's advice and point me in the right direction please?

Thanks again to Qlemo and in advnance of further Expert help from others.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25110652
Sorry I didn't pick this up earlier.

SQL Server should be run under a generic user account that has Service and rights to the local SQL folders.

Then change the logon accounts under the SQL Services to the new account.

Make sure you add the account to the security in SQL.
0
 

Author Comment

by:onek
ID: 25115068
Hi Jim Pen,

Thanks for your comment.  I'm very new to SQL Server admin so would you mind adding a few step-by-step instructions and clarifications to ensure I do this right?

Am I correct in assuming you're saying the SQL Server *Serivce* needs to have a logon account of some generic user account?  It's currently set to NT\AUTHORITY\Network Service.

Would I also need to change Logon accounts for the other services, i.e. AD Helper, Agent, Analysis Services, Browser, FulllText Search, Integration Services, Reporting Services, VSS Writer?

When you say 'the local SQL folders', do you mean c:\program files\Microsoft SQL Server  ?

And can you clarify re 'add the account to the security' SQL please?

Thanks in advance.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 25115769
Posting from mobile is a pain -- that's why I didn't get detailed.

I really need to write this up as an article.

The way best security practices says to create a Domain user account (i.e. MyDomain\SQLMgr) that has limited domain privileges and the suggestion is read only to the Windows folders, and full control of the C:\Program Files\Microsoft SQL Server folder (and any other locations that data, backups and logs would be written). They suggest against making him a local admin, but that depends on the applications using SQL -- such as having to write BLOB files to disk.

Then after you have setup the user, go into the SSMS and add the user to the Security folder, give it SysAdmin role and specifically go to Database mapping and make him a a database owner in the Master and MSDB databases.

Then go into your services console, and change all the SQL services to start with that userid. You will have to restart the services for it to take effect.

After that you should be good.
0
 

Author Comment

by:onek
ID: 25171991
Hi jimpen,

Thanks for this.  Just to update you, one of the guys on our team whom I need to confer with on this is on holiday at the moment, so I'll come back to you, hopefully in a few days.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25172110
>> that has limited domain privileges and the suggestion
>> is read only to the Windows folders

The reason for the limited domain privileges, and limits locally is that via the XP_cmdshell (and other functions/apps) a hacked user account could easily pull anything out of your domain.

Some suggestions even go as far as recommending separate accounts for each instance. I think that is overkill except for very highly segmented, confidential data.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

13 Experts available now in Live!

Get 1:1 Help Now