Solved

Security problems in SQL 2008 R2 management studio

Posted on 2011-03-02
20
1,815 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 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