Various Errors and failure Audits starting SQL Server service

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.
onekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
NT AUTHORITY\SYSTEM = Local System account, so the answer is "yes".
0
onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
onekAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
onekAuthor Commented:
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
onekAuthor Commented:
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
Jim P.Commented:
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
onekAuthor Commented:
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
Jim P.Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
onekAuthor Commented:
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
Jim P.Commented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.