Solved

Security problems in SQL 2008 R2 management studio

Posted on 2011-03-02
20
1,842 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:Bart001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
20 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35024253
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
 

Author Comment

by:Bart001
ID: 35024435
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024484
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Bart001
ID: 35024593
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024610
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
 

Author Comment

by:Bart001
ID: 35024649
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024657
Are u using any third party software for backups/maintenance? This usually happens when third party backup solution is used.
0
 

Author Comment

by:Bart001
ID: 35024681
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024697
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
 

Author Comment

by:Bart001
ID: 35024763
jobs still fail but now the login is failing for NT AUTHORITY\SYSTEM instead  of anonymous user.
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024807
Ok..Now add the login NT AUTHORITY\SYSTEM and provide sysadmin privileges.
After which, check if there are any errors/issues.
0
 

Author Comment

by:Bart001
ID: 35024840
No change
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024878
Are you seeing the same error corresponding to NT Authority\System even after adding and providing sysadmin privileges?
0
 

Author Comment

by:Bart001
ID: 35024918
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35024957
>>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
 

Author Comment

by:Bart001
ID: 35032806
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
 
LVL 13

Expert Comment

by:geek_vj
ID: 35033849
Yep..please proceed with reinstallation. Kindly post incase of any issues.
0
 

Author Comment

by:Bart001
ID: 35034175
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
 

Accepted Solution

by:
Bart001 earned 0 total points
ID: 35034252
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
 

Author Closing Comment

by:Bart001
ID: 35067801
perseverance furthers. solved my own problem again
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

624 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