When you use SQL Aliases (something that most people believe to be a good practice as it’s a locally controlled link to the database, and unlike DNS it updates instantly, and Microsoft recommend it here
http://technet.microsoft.com/en-us/library/cc298801.aspx#Section6_3
) and you have installed SPDiag from the SharePoint Administrator Toolkit, you also need to ensure the Alias name resolves to the IP Address of the cluster / DB Server. These guys hit the same issue –
http://social.technet.microsoft.com/Forums/en/sharepoint2010setup/thread/4b397f6c-249a-43f0-aa91-ff210e4e132b
and this guy mentions it in his “Least Privilege” build documentation –
http://craighumphrey.blogspot.com/2010/06/sharepoint-2010-rtm-least-privledge.html
– as an extra bonus SQL Step 
What happens is that the timer jobs that the SPDiag application creates in SharePoint make an RPC call to start up a Performance / logging Trace on the SQL Server; however while local SQL Servers, Remote SQL Servers and SQL Clusters addressed by their DNS name and instance work fine, SQL Aliases confuse SPDiag into thinking that you have a Database Server out there somewhere with the Alias name.
The errors we saw were:
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPDatabaseServerDiagnosticsPerformanceCounterProvider (ID 4fc4e5b8-c7ab-4b51-b5e7-57f40038dc81) threw an exception. More information is included below.
SPNL_Prod:The network path was not found.
Please ensure that the database server is available and that the SharePoint Timer service account is a member of the Performance Monitor Users group on the database server.
And
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPSqlBlockingReportDiagnosticProvider (ID d26d4741-0244-4e6a-b4ff-b78c3421ca8c) threw an exception. More information is included below.
The blocking query diagnostics provider could not set the ‘blocked process threshold’ for the sql instance SPNL_Prod. Blocking queries will not be captured in this instance. Error Code: User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.
User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.
And
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPSqlDeadlockDiagnosticProvider (ID 8d638793-8aa8-4f55-ae04-0efa42b679ee) threw an exception. More information is included below.
You do not have permission to run ‘SP_TRACE_CREATE’.
And
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPSqlBlockingReportDiagnosticProvider (ID d26d4741-0244-4e6a-b4ff-b78c3421ca8c) threw an exception. More information is included below.
You do not have permission to run ‘SP_TRACE_CREATE’.
And
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPIOIntensiveQueryDiagnosticProvider (ID 0b0f8d31-08d5-4592-96ef-05e84a2f33a6) threw an exception. More information is included below.
You do not have permission to run ‘SP_TRACE_CREATE’.
Phew! Also we saw these every 5 minutes, which appear if you have Event Log Flooding protection enabled in the SharePoint Settings…
Event 6398 (SharePoint Foundation) of severity ‘Critical’ occurred 22 more time(s) and was suppressed in the event log
Easy to fix: Uninstall the Admin toolkit or (if you must have it) set up entries in your DNS system or Host File.
B
Brad,
I’m hitting exactly this error, but *exactly* what can I add to the hosts file to resolve?
I have a SQL alias of the form “servername\instance” What should I add to the hosts file?
“Servername\instance [IP]”
Thanks!
-Jon
Hi Jon.
Using your example, you have a SQL Instance (not Alias) of servername\instance – So assuming you created an Alias on your Web Front ends called MyFunkyAlias which (using cliconfg) was mapped to servername\instance, you need to create a DNS entry or Hosts file entry that maps MyFunkyAlias to the IP address associated with servername (your SQL Server or Cluster’s IP). In the hosts file, it would take the format
0.1.2.3 MyFunkyAlias
Hope this helps…
Hi Brad,
Interesting. I have the same problems (admin toolkit not working on a remote cluster, jobs fail), but the servername resolves ok. No sql alias is declared on any WFE afaik. The symptoms are there, thou, as i see numerous “A transport-level error has occurred when receiving results from the server” SQL 121.
Any additional ideas to solve this? thanks in advance
Hi Emmanuel – It does not look like it’s the same issue – The error in your comment refers to an issue sending the trace communications back to the SharePoint server running the trace (normally the Admin Server) – I did not see that error in my scenario.
Based on the info provided, check that:
You are not using a SQL Alias
You can ping the database name SharePoint is pointing to (get this out of Central Admin)
You can ping the SharePoint server from the SQL server
If they all work, it might be time to call in a pro / raise a support call with Microsoft.
Thanks Brad!
i’ll check if the evil DBAs have not removed the trace creation rights from the farm account, which now looks likely
Hi Brad,
I still get this message still, even after I have uninstalled Sharepoint Administration Toolkit.
Anything that you can help me, or tell me what I can do?
Thanks
Hi Peter – you may have the same issue as Emmanuel… check your account has trace permissions on the SQL Server…
Hi Brad
No this is not I have only issue with this job “Diagnostic Data Provider: Performance Counters – Database Servers” and the issue is today only this :
The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPDatabaseServerDiagnosticsPerformanceCounterProvider (xy) threw an exception. More information is included below.
xy:Attempted to perform an unauthorized operation.;
Please ensure that the database server is available and that the SharePoint Timer service account is a member of the Performance Monitor Users group on the database server.
I also followed this microsoft issue :
http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/4b397f6c-249a-43f0-aa91-ff210e4e132b/
but It does not help. I added it to host to, but probably my account can hit something and I get this issue Attempted to perform an unauthorized operation.
Is the SharePoint Timer service account a member of the Performance Monitor Users group on the database server? It would have to be a domain account in order for this to work… not Network Service or a local machine account…
Pingback: SharePoint Error 6398 - You do not have permission to run 'SP_TRACE_CREATE' - Kirk Evans Blog - Site Home - MSDN Blogs
Some good information here on security changes required to resolve permission issues that throw a similar error as well… – http://blogs.msdn.com/b/kaevans/archive/2012/02/09/sharepoint-error-6398-you-do-not-have-permission-to-run-sp-trace-create.aspx
I’d suggest to only set the permissions required. Essentially you need to give the farm account ‘Alter settings’ and ‘Alter trace’ from the Permissions page under Server Properties on the SQL Server…
Agreed – As per the previous comments, setting the correct permissions is the way to address the problem of the farm account not having the necessary rights on the SQL Server. Essentially the farm account needs to have the following in order for the Admin toolkit to run properly:
In my case (and the reason for the Blog article in the first place) my SharePoint farm was having trouble accessing the SQL server, because I was using a SQL Alias (and the Admin Toolkit could not resolve the Database setting / Value stored in SharePoint to a physical machine / cluster – not sure if it’s a bit smarter in v4 http://www.microsoft.com/en-us/download/details.aspx?id=4388 – we were using v2). As this was a system that was shortly going to be “stood up” in production and as the Admin toolkit was not part of the architecture, we pulled it out to solve the problem. The trickiest part was working out what was throwing the errors in the first place!
thankyou! You just solved my problem!
Despite the comments on this blog, I can still not get beyond the following error:
Please ensure that the database server is available and that the SharePoint Timer service account is a member of the Performance Monitor Users group on the database server.
I also installed SPDiag and despite uninstalling, this log is persistent….unless I disable the Timer Job (which I’m currrently doing)
I’m certain my issue is related to me implementing a SQL Alias pointing to a named SQL instance. Updating the local hosts file on the SharePoint box has been in vain.
Thanks for this article, I removed the admin tools and my problem has been solved.