SQL Aliases and SP_TRACE_CREATE errors on SharePoint 2010

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 Smile

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

About these ads

About Brad Saide

I'm a SharePoint consultant. I'm also slowly going bald, seem to have a permanent spare tyre around my waist and enjoy socialising with friends over a beer or 10. The last 2 may possibly be related. Started working with SharePoint when the first version was in limited beta release (participated in the Technology Adoption Program while at Woolworths) and have been committed to the adoption of the technology as a business enabler ever since.
This entry was posted in Uncategorized. Bookmark the permalink.

23 Responses to SQL Aliases and SP_TRACE_CREATE errors on SharePoint 2010

  1. Jon says:

    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

    • Brad Saide says:

      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…

  2. 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 :)

    • Brad Saide says:

      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.

  3. peterga says:

    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

    • Brad Saide says:

      Hi Peter – you may have the same issue as Emmanuel… check your account has trace permissions on the SQL Server…

      • peterga says:

        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.

  4. Brad Saide says:

    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…

  5. Pingback: SharePoint Error 6398 - You do not have permission to run 'SP_TRACE_CREATE' - Kirk Evans Blog - Site Home - MSDN Blogs

  6. Brad Saide says:

    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

  7. 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…

    • Brad Saide says:

      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:

      1. Be a domain account (Local machine accounts and things like Network service will not work if the SQL server is a separate machine)
      2. Be a member of the “Performance Monitor Users” group on the database server
      3. Have the “Alter settings” and “Alter trace” permissions from the Permissions page under Server Properties on the SQL Server (Accessible through SQL Server Management studio or by running the necessary SQL command against the server)

      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!

  8. clarky says:

    thankyou! You just solved my problem!

  9. Adam5132 says:

    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.

  10. Jo says:

    Thanks for this article, I removed the admin tools and my problem has been solved.

  11. alip737 says:

    Hi Brad,
    Thanks for your article very useful. I also have same requirement as yourself where I had to pulled this out.

    Although I uninstalled the toolkit, disable the provider jobs (using PwoerShell) and also retracted and removed the the extendeddiagnosticproviders.wsp the “Diagnostic Data Provider: Performance Counters – Database Servers” timer job still running every minute.

    I was wondering if it would be safe to diablo this job manually? Or I can ignore this…

    Any advice greatly appreciated.

  12. alip737 says:

    Many thanks Brad. Much appreciated!

    This might be a blunt question but are you referring to ‘these’ as in to disable all in the following list:

    Diagnostic Data Provider: Event Log
    Diagnostic Data Provider: Performance Counters – Database Servers
    Diagnostic Data Provider: Performance Counters – Web Front Ends
    Diagnostic Data Provider: SQL Blocking Queries
    Diagnostic Data Provider: SQL DMV
    Diagnostic Data Provider: SQL Memory DMV
    Diagnostic Data Provider: Trace Log

    Hope I’m not repeating myself just wanted to be on safe side.
    Thanks

  13. Brad Saide says:

    Hi – It’s fine to disable the timer jobs you mention – They are just used to monitor performance, you do not lose any functionality by disabling them.

    If you find you need them again at some stage in the future, just re-enable them!

  14. Pingback: Uninstall the Microsoft SharePoint 2010 Administration Toolkit v2.0 (SPDiag) | mysharepointexclusive

  15. Kong says:

    Excellent explanation!.. It resolve my issue. I have added the entry on all WFEs and APPs servers’ host file.

    Thanks – Cong

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s