can't (re)start SQLServerAgent after changing its logon credentials

Since Copy Databas Wizard failed to copy anything from remote SS20005 (both are with SP2 3054) in any combination,
I changed SQLServerAgent(myinstance_name) to run under Network from Local System with the help of SQL Server Configuration Manager

For this change it was restarted, failed to start again and continues to be stopped giving
"SQLServerAgent could not be started (reason: Unable to connect to server '<mymachine_name>\<myinstance_name>'; SQLServerAgent cannot start)."

I changed SQL Server Agent (myinstance_name) back to Local Service, then to Local System and still can't start it.

So, my questions:
1)
What has happened and how can I start SQLServerAgent?

2)
Why cannot I start it under Network Service? since it apparently should access remote resources for CDW to copy from remote SS2005

This is on MS Windows XP SP2

PS
After few trials I amnaged to restart it again under Local System but still cannot change to any different one

If you change the account under which SQL Agent starts you need to make sure that the account has permissions within SQL server or else each tim eit tries to access SQL Server it will fail. Have a look at these:

http://support.microsoft.com/kb/283811

http://support.microsoft.com/kb/907557

|||

You can verify the permissions, windows group membership required, etc for the service accounts in the following books online topic:

Setting Up Windows Service Accounts

http://msdn2.microsoft.com/en-us/library/ms143504.aspx

The NT Rights and Privleges section has the local windows group membership requirements - that one often gets overlooked for some reason. The help topic is pretty thorough and does a good job of covering the requirements.

Also note that as the article states, using Network Service isn't recommended. A domain account can provide the same network access but in a more secure manner. Otherwise you open some holes - network service means you give permissions needed for the CDW to the machine account. And then other apps, services running under network service get the permissions as well as they would be operating under the machine account.

-Sue

CANNOT SET PARTNER 'NT AUTHORITY\ANONYMOUS LOGON.' failed

i tried to set up mirroring having only principal and mirror. mirroring endpoints were easily created for both servers. when i try to add the partner name it responds with
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://machine1.domain.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
On the log of machine1 i see,
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.

i have similar problems as in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1020144&SiteID=1

i am a sysadmin on both SQL Servers and have connect permission on both end points.
Both the endpoints are in started state and listening to all IP's
Both services are as 'Local System'
As per what was mentioned in the above post to add a 'domain/machine$' user did not help.
Tried using setspn.exe to add a new SPN MSSQLsvc/<HOST>:<MIRRORINGPORT> did not work. an ERROR comes 'not enough privileges'
setup shows the following SPN only on both
HOST/Machine name
HOST/Machine name.Domin.com
MSSQLSvc/Machine name.Domin.com:1433
SMTPSVC/Machine name
SMTPSVC/machine name.Domain.com

and on second machine
HOST/Machine name

HOST/Machine name.Domin.com

MSSQLSvc/Machine name.Domin.com:1433

any Clues?

Udit Ghai wrote:

Tried

using setspn.exe to add a new SPN

MSSQLsvc/<HOST>:<MIRRORINGPORT> did not work. an ERROR

comes 'not enough privileges'

This is probably your problem. Run this command from an account with enough priviledges on the Active Directory. One easy trick is to run this command as the LocalSystem of the machine <HOST>. To achieve this, you must start a command prompt as LocalSystem on <HOST>. Use the AT service for this, scheduling an interactive CMD.EXE one minute in future. E.g. if the clock is 11:45, run this:
at 11:46 /interactive cmd.exe
In one minute a command prompt running as LocalSystem will popup. From this, run the SETSPN. You must do this on all machines involved (principal, witness, mirror if any)

HTH,
~ Remus

P.S. This assumes you're running the SQL Server service as LocalSystem or as NETWORK SERVICE.