Security problems in SQL 2008 R2 management studio

I have been setting up two new SQL 2008 R2 x64 Servers on Windows Server 2008 x64 boxes in a remote data centre. All was well until the last weekend. I had been adding maintenance plans and jobs but when looked in early this week I noted no jobs had been running. It seems the SQL Agent is suddenly unable to connect to the server and the service will not start. There are numerous login failures for 'NT AUTHORITY\ANONYMOUS LOGON', where this came into the picture i don't know, the Agent service is assigned to the Local System Account. I got the service to run by adding the server name to the registry under the SQL Agent key, but this didn't solve any of the login issues. Plenty of people seem to have had this issue but none of scenarios quite fit this one. The SQL service is running ok and it is a default server instance. Anybody dealt with this before?
Bart001Asked:
Who is Participating?
 
Bart001Connect With a Mentor Author Commented:
woo hoo! found the answer here. http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/567d77bf-4f23-4ea7-8eae-7a6f295f7701/

The problem was solved by adding the machine name to the local host entry in the hosts file. One of the sysadmins had made some entries which apparently broke everything. The machine name was entered with a fully qualified domain name and  I think the server must have decided it was a different domain or something. Now everything works.
0
 
dbaSQLCommented:
I've always enabled a domain login for all of my SQL Server service accounts.  It's much more controllable across the network, or when there is server to server activity.  And more manageable, in m opinion.  I wonder if you could try that.
0
 
Bart001Author Commented:
Yes that's true, but this is in a data center, there is no network as such, no active directory and no overriding domain. The machine is it's own domain and it doesn't know about any others. It's also in a secure tier with no outward facing access.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
geek_vjCommented:
Try running SQL Server Agent account under the same account as SQL Service account. Also, ensure that u remove access for all non authorized folks.
0
 
Bart001Author Commented:
The SQL Service is running under the NETWORK SERVICE account. I changed the Agent over and got a 'process terminated  unexpectedly [0x8007042b]' error.

sql log opened in notepad shows
'2011-03-03 17:35:53.92 Logon       Error: 18456, Severity: 14, State: 11.
2011-03-03 17:35:53.92 Logon       Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
I think that state 11 indicates that the login is valid but server cannot be accessed.
0
 
geek_vjCommented:
Yes..seems this is an access violation error. Try changing the account to Local system and restart the service.
Let me know in case of any issues.
0
 
Bart001Author Commented:
changed back to Local System. I've added the np:<servername> value to the serverhost key in the registry, the service will start but I can't open sql logs, maintenance plans or get any jobs to start. The logs show 'Login failed for user...' etc as previously. Not sure why anonymous logon is the user, that will never work. The jobs from the maintenance plan say the LoadFromSQLServer method failed. Just cannot connect to the server. I'm starting to think about removing the entire installation and starting again.
0
 
geek_vjCommented:
Are u using any third party software for backups/maintenance? This usually happens when third party backup solution is used.
0
 
Bart001Author Commented:
No, i believe there's  a net backup which backs up files, otherwise I've set up backup jobs using the maintenance plans, but of course these haven't run for several days now.
0
 
geek_vjCommented:
Okie...After reinstallation also, we cant guarantee that these errors will stop. So, we have to find the rootcause and eliminate the same.
As the first step, add your login (windows login) as sysadmin under logins of Management studio.
If u r sure that no application is using this server, you can remove other logins except for the logins with '$' sign.

Once the cleanup of necessary logins is done at SQL Server end, check if the login errors are still happening.
0
 
Bart001Author Commented:
jobs still fail but now the login is failing for NT AUTHORITY\SYSTEM instead  of anonymous user.
0
 
geek_vjCommented:
Ok..Now add the login NT AUTHORITY\SYSTEM and provide sysadmin privileges.
After which, check if there are any errors/issues.
0
 
Bart001Author Commented:
No change
0
 
geek_vjCommented:
Are you seeing the same error corresponding to NT Authority\System even after adding and providing sysadmin privileges?
0
 
Bart001Author Commented:
No it's gone back to Anonymous Logon. Also a new one from Network Service - Failed attempt retry of a process token validation, Error: 18456, Severity: 14, State: 56.
0
 
geek_vjCommented:
>>Network Service - Failed attempt retry of a process token validation, Error: 18456, Severity: 14, State: 56.
This error is due to the fact that Mixed mode authentication is not enabled. So, please set the authentication mode as 'Windows+SQL Authentication mode' from Management Studio --> Right click properties --> Security
After which, you have to restart the SQL Services to get this change into effect. Post which, check for any errors and let me know the same.
0
 
Bart001Author Commented:
It was already set as mixed mode. I unset it and set it again, restarting the service both times. most recent attempt to open a maintenance plan resulted in the same errors:

2011-03-04 11:35:14.31 Logon       Error: 18456, Severity: 14, State: 56.
2011-03-04 11:35:14.31 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed attempted retry of a process token validation. [CLIENT: <local machine>]
2011-03-04 11:35:15.81 Logon       Error: 18456, Severity: 14, State: 11.
2011-03-04 11:35:15.81 Logon       Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Also a number of errors from the msdb. syspolicy_ event_queue, because I 've removed the ##MS_PolicyEventProcessingLogin## so I better reinstate that.
0
 
geek_vjCommented:
Yep..please proceed with reinstallation. Kindly post incase of any issues.
0
 
Bart001Author Commented:
Uninstalled and reinstalled SQL Server, still have exactly the same issue. I've tried creating a new server login and assigning to the SQLAgent and MSSQLUser groups. The agent still won't start, sspi handshake failed messages, login from untrusted domain and so on.

There is a line in the error log saying that the Network Interface Library  could not register an SPN for the SQL Service.  Do I need to something about this?  I've actually had the server running happily for a couple of months without this being required before.
0
 
Bart001Author Commented:
perseverance furthers. solved my own problem again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.