Solved

Security problems in SQL 2008 R2 management studio

Posted on 2011-03-02
20
1,802 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
  • 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now