November 3, 2008 at 10:41 am
I am having issues starting the sql server agent. Any help/suggestions would be appreciated!
Notes:
SQL Server 2005 SP2
Enterprise Edition
Windows NT 5.2
Agent XPs is enabled
We are running in mixed mode
I have tried starting the agent with multiple different logins without success.
The service was running fine until I restarted sql server service last week -- I then decided it would not start again. Sql server service is running with the same login and started successfully.
The login is a domain admin (I know - should be local admin if anything -- but needed it for a work around) so, there should not be security issues there...I don't think.
===============================================
Event Type: Failure Audit
Event Source: MSSQL$
Event Category: (4)
Event ID: 18456
Date: 10/30/2008
Time: 2:02:44 PM
User: NT AUTHORITY\ANONYMOUS LOGON
Computer:
Description:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: ]
Event Type: Failure Audit
Event Source: MSSQL$
Event Category: (4)
Event ID: 18452
Date: 10/30/2008
Time: 2:02:45 PM
User: N/A
Computer:
Description:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ]
Event Type: Error
Event Source: SQLAgent$
Event Category: Service Control
Event ID: 103
Date: 10/30/2008
Time: 2:02:45 PM
User: N/A
Computer:
Description:
SQLServerAgent could not be started (reason: Unable to connect to server ' '; SQLServerAgent cannot start).
Event Type: Error
Event Source: MSSQL$
Event Category: (4)
Event ID: 17806
Date: 10/30/2008
Time: 2:02:45 PM
User: N/A
Computer:
Description:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ]
from SQLAGENT.OUT...
2008-10-30 14:49:07 - ! [298] SQLServer Error: 18452, Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000]
2008-10-30 14:49:07 - ! [000] Unable to connect to server ' '; SQLServerAgent cannot start
2008-10-30 14:49:07 - ! [298] SQLServer Error: 18452, Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000]
2008-10-30 14:49:07 - ! [382] Logon to server ' ' failed (DisableAgentXPs)
2008-10-30 14:49:08 - ? [098] SQLServerAgent terminated (normally)
from ERRORLOG...
2008-10-30 14:49:07.60 Logon Error: 17806, Severity: 20, State: 2.
2008-10-30 14:49:07.60 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ]
2008-10-30 14:49:07.60 Logon Error: 18452, Severity: 14, State: 1.
2008-10-30 14:49:07.60 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ]
I also just tried changing the login to Local System to see if that would work...
2008-10-30 15:03:43 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000]
2008-10-30 15:03:43 - ! [000] Unable to connect to server ' '; SQLServerAgent cannot start
2008-10-30 15:03:43 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000]
2008-10-30 15:03:43 - ! [382] Logon to server ' ' failed (DisableAgentXPs)
2008-10-30 15:03:44 - ? [098] SQLServerAgent terminated (normally)
I found this as well...
Event Type:Warning
Event Source:Schannel
Event Category:None
Event ID:36885
Date:11/3/2008
Time:10:40:34 AM
User:N/A
Computer:
Description:
When asking for client authentication, this server sends a list of trusted certificate authorities to the client. The client uses this list to choose a client certificate that is trusted by the server. Currently, this server trusts so many certificate authorities that the list has grown too long. This list has thus been truncated. The administrator of this machine should review the certificate authorities trusted for client authentication and remove those that do not really need to be trusted.
Thanks,
Jen
November 3, 2008 at 12:37 pm
Use Windows local admin to start the agent
November 3, 2008 at 1:24 pm
Ramu,
Thanks, I have tried starting with a local admin, and the built in accounts as well -- still the same error.
I have also tried starting it 3 different ways - from SQL config manager, via services.msc, and with the net start command.
Any other suggestions?
November 3, 2008 at 1:34 pm
The Service Account for the SQL Agent has to be a sysadmin in the SQL Server for it to startup. The Error 18452 being thrown is coming from the SQL Server Database Engine saying that the service account does not have access to the Database Engine. This was likely caused by removing the 'NT AUTHORITY\ANONYMOUS LOGON' from the Database Engine. Add the Service account back to SQL Server as a sysadmin and it should start up.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
November 3, 2008 at 1:34 pm
Restart the server once.If any changes are made to the logins by your administrator will get updated.
November 3, 2008 at 1:47 pm
I checked and NT Authority\system is sysadmin on the instance. I have added the login for the service as sysadmin -- but, not since a reboot of the server. I will request a reboot be performed and report back on if that helped.
Thank you for your suggestions!
November 4, 2008 at 8:49 am
I rebooted the server and am getting the same results. 🙁
I ran the following and got NTLM...I think this should be kerberos instead - but, don't know for sure...
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
November 4, 2008 at 8:51 am
One more note -- I cannot log into SQL Profiler using windows auth -- I need to use a sql login.
I get the Login failed for user ''. ... error
November 4, 2008 at 9:53 am
Is this your only SQL server or a new server to your environment. If so why arent you using domain account to run the services? I always use one account for each SQL service (static passwords).
This way you have an extra hand in troubleshooting and it also come in use when playing with permissions and cross server authentications.
I did have your problem once and solved it with domain accounts. Good luck. 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 4, 2008 at 9:58 am
Schadenfreude-Mei (11/4/2008)
Is this your only SQL server or a new server to your environment. If so why arent you using domain account to run the services? I always use one account for each SQL service (static passwords).This way you have an extra hand in troubleshooting and it also come in use when playing with permissions and cross server authentications.
I did have your problem once and solved it with domain accounts. Good luck. 🙂
Thanks for the tip. This is not our only environment. This is our staging environment. They currently are running (or trying to run - Agent) under domain accounts.
My next step is to ask the administrators to re-register the SPNs -- I think the problem may lie there - not sure though. It's one more thing to rule out ...
November 4, 2008 at 10:03 am
I work in a very heavily security concious environment and at one point found that although services.msc had said the account had been given logon-as-service rights did not actually make it so.
One of the server techs had to do 'something' to allow this account to run as a service. Check that and I will write back if i can get anymore info.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 4, 2008 at 10:07 am
Schadenfreude-Mei (11/4/2008)
I work in a very heavily security concious environment and at one point found that although services.msc had said the account had been given logon-as-service rights did not actually make it so.One of the server techs had to do 'something' to allow this account to run as a service. Check that and I will write back if i can get anymore info.
Thanks again. The same domain account that is attempting to start the agent is the same login used to start sql server service, and that starts fine. So, this should not be the case. (and yes, I should look into modifying this so the logins are more secure..but, that is the way it is now 🙂
November 4, 2008 at 10:07 am
I'm being told that the 'Local Security Policies' has to be set so the account is in the 'Login as service' group, though not sure if this does or can get overwritten by an AD policy.
The usual guy has gone home. Have a check and let me know.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 4, 2008 at 10:13 am
ahhh, ok scratch that last comment, hadnt seen you response.
Sounds stupid and im grasping at straws now but are they both defined in the same way in services.
I have a similar issues on one of my desktop instances (which i havent had time to look into), where each time i restart the machine the agent fails to log in, BUT if i re-do the login section of services, it works until i restart.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 4, 2008 at 10:16 am
Schadenfreude-Mei (11/4/2008)
I'm being told that the 'Local Security Policies' has to be set so the account is in the 'Login as service' group, though not sure if this does or can get overwritten by an AD policy.The usual guy has gone home. Have a check and let me know.
The current login used does have "Log on as a service" permissions. But the other login I created to do other testing is not. I will ask admin guys to add it here so I can do more testing - thank you.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply