Cannot create project (TF30225: xp_sqlagent_notify)

Question

The installation finished correctly but I still cannot create any project. (I already searched the forum and no luck).

This is the error I am getting:

---begin Exception entry---
Time: 2006-08-23 17:39:03Z
Module: Engine
Event Description: TF30162: Task "Populate Reports" from Group "Reporting" failed
Exception Type: Microsoft.TeamFoundation.Client.PcwException
Exception Message: TF30225: Error uploading report : Work Item with Tasks
Exception Details: The Project Creation Wizard encountered a problem while
creating reports on the SQL Server Reporting Services on MARS.
The reason for the failure cannot be determined at this time.
Because the operation failed, the wizard was not able to finish
creating the SQL Server Reporting Services site.
Stack Trace:
   at Microsoft.VisualStudio.TeamFoundation.RosettaReportUploader.UploadReport(ProjectCreationContext context, XmlNode report)
   at Microsoft.VisualStudio.TeamFoundation.RosettaReportUploader.HandleCreateReports(ProjectCreationContext context, XmlNode node)
   at Microsoft.VisualStudio.TeamFoundation.RosettaReportUploader.Execute(ProjectCreationContext context, XmlNode taskXml)
   at Microsoft.VisualStudio.TeamFoundation.ProjectCreationEngine.TaskExecutor.PerformTask(IProjectComponentCreator componentCreator, ProjectCreationContext context, XmlNode taskXml)
   at Microsoft.VisualStudio.TeamFoundation.ProjectCreationEngine.RunTask(Object taskObj)
--   Inner Exception   --
Exception Type: System.Web.Services.Protocols.SoapException
Exception Message: An internal error occurred on the report server. See the error log for more details. ---> An internal error occurred on the report server. See the error log for more details. ---> EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
SoapException Details: <detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsInternalError</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An internal error occurred on the report server. See the error log for more details.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsInternalError&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=9.00.2047.00</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">9.00.2047.00</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode="rsInternalError" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsInternalError&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=9.00.2047.00" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An internal error occurred on the report server. See the error log for more details.</Message><MoreInformation><Source>.Net SqlClient Data Provider</Source><Message>EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.</Message></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" /></detail>
Stack Trace:
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.TeamFoundation.Proxy.Reporting.ReportingService.SetCacheOptions(String Report, Boolean CacheReport, ExpirationDefinition Item)
   at Microsoft.VisualStudio.TeamFoundation.RosettaReportUploader.UploadReport(ProjectCreationContext context, XmlNode report)
-- end Inner Exception --
--- end Exception entry ---

 

I already tried the following suggestion:

 Hi,

The problem is with uploading a report to the report server.  I think you have the correct permissions since you were able to create the site.  The problem may have to do with the security settings on the datasources (TfsOlapReportDS and TfsReportDS) as these are the datasources for the reports.

Please verify the following settings:  Navigate to the reporting site (/Reports/Pages/Folder.aspx">http://<reportserver>/Reports/Pages/Folder.aspx), click on the TfsOlapReportsDS and the TfsReportDS and verify the connection settings for each, specifically the 'Connect using:' section.  This is generally set to 'Credentials stored securely in the report server' and a valid username/password is specified.  Also, the 'Use as Windows credentials when connection to the data source' is checked.

-Dennis

 

Does anyone have any other suggestion?

 

Thanks!

Answer 1

I called MS support and got it resolved.

The nt authority\network service account was missing a permission. We added RSExecRole role to the master database.

Answer 2

Can you walk me through that procedure. I am having the same problem.

Answer 3

Hey AbsoluteIT,

I had the same problem when I tried to update the reports after upgrading from RC to RTM (the reports had been updated between versions). I got around the problem at the time by manually importing the reports one by one from the template. Then I had the exact same problem when I tried to create a new team project. Just when I thought I had removed all problems....

To fix, create a new database role in master and msdb: RSExecRole. In "Securables" add the following:

master:
  xp_sqlagent_notify (execute)
  xp_sqlagent_enum_jobs  (execute)
msdb:
  sp_add_category  (execute)
  sp_verify_job_identifiers  (execute)
  sysjobs (select)

Assign the role from each db to Network Service.

This fixed it for me. Not sure if will work for everyone as each case might be different, but again, it worked for me, I can now create team projects without any errors.

As to why I had this problem - a couple of factors. I had no problems at all with installation. But I deleted the encryption key from Reporting Services since I couldn't remember the passphrase for it, and then created a new encryption key and recreated the datasources etc. Maybe this process removed these permissions on master and msdb???

Enjoy!

Answer 4

Thanks Scotty,

this really fix the error.

I try and works.

Regards,

Abu

Answer 5

Hi. I've got a similar problem with reporting service subscriptions.

I've setup the roles you've specified, unfortuanately I've still come across the same error.

The only step I'm not too certain about is the "Assign the role from the each db to Network Service".

Perhaps rephrase that. Are you suggesting I go to my db, go to security->roles->database roles-> and then....? I'm not too sure what you mean by this step.

Thanks

Answer 6

After reading some more posts, it seems like I need to:

Go to System Databases->master->Security->Users->  And assign the role to the Network Service User.

I don't have a network service user setup.

Is my assumption correct and do I need to setup that user?

Lastly, my datasource in the RS uses a specific user.

Answer 7

I'VE GOT IT. Okay, sometimes its a bit confusing understanding what other mean, so I'll try to be clear and specific towards describing my solution.

1) You need to setup the roles Scotty suggested above.

To fix, create a new database role in master and msdb: RSExecRole. (Databases -> System Databases -> master -> Security -> Roles -> Database Roles) and (Databases -> System Databases -> msdb-> Security -> Roles -> Database Roles) In "Securables" add the following:

master: 
  xp_sqlagent_notify (execute) 
  xp_sqlagent_enum_jobs  (execute) 
msdb: 
  sp_add_category  (execute) 
  sp_verify_job_identifiers  (execute) 
  sysjobs (select)

2) Because you already have reporting services setup. There should be two standard reporting service tables setup. One I think by default is named ReportServer$SQL2005  and the other ReportServer$SQL2005TempDB. The RSExecRole needs to be setup there aswell. Now becuase mine was already configured, I'm not too sure what the permissions of this role should be.

3) Now this is the part that may be configured differently depending on your Environment. 

3.1)Now Scotty suggested we give the NTUser, which can be found in  (Databases -> System Databases -> master -> Security -> users) permissions to the RSExecRole. How to do that is go the location I've mentioned in this section. Double click on the NTUser, and on Owned Schemas and Database role members, tick the RSExecRole (on owned schemas and database role membership).

3.2) Now I had a database user that was mapped to my datasource in reporting services. I.E if I startup my report service website, go to my datasources, I checked the "credentials stored securely..." and ideally used a sql login. So effectively I would've needed to give that user RSExecrole rights. 

Thus, back to sqlmanagementstudio, I went to server-> security -> logins -> MyRSSQLUser. Double clicked him and went to user mappings. I then selected the master and msdb tables, and the default schema was set to RSExecRole. I then needed to go back to the master and msdb db's. Go to Security -> Users. Get my MyRSSQLUser, and double click the user and on Owned Schemas and Role Members, selected RSExecRole.

 

Hope this proves useful.

 

Regards,

Answer 8

Full list of all permissions required are in this article on MSDN:

http://msdn.microsoft.com/en-us/library/cc281308.aspx

Answer 9

Hi All,

I had the same problem. I had to migrate just the Team Foundation database server to a new server. It was after a few weeks that we got a request from a client to create a new team project and thats when I got this error. The error message is exactly the same as mentioned in the beginning of the post. I fwd this post to our DBA . He verified that the RSExecRole was already in both databases  (Master and MSDB). He granted the required execute permissons as mentioned above and that fixed my problem.

 

Thanks,

Ibheri