HI all,
I have been trying to set up reporting for about 3 weeks now and are just about to give up, I'll give a quick run through of everything I have done.
1. I went through all the steps for both configuring the Reporting Config Manager and then added the Role in the SCCM server. This seemed to work, the only issue was that the service account I had created to run the reports returned a permission error, it only worked for my own account despite granting the service account admin permission both on the SQL and SCCM server and also in Config Manager itself.
2. After trying different things for this I though I'd check my credentials again to ensure I could still get reports and it kept giving me a permission denied error pointing to the service account even though I had removed it completely both in the Reporting Config Manager and in SCCM. So I decided to delete the encrypted data in hope of removing any credentials pointing to the service account....this is where everything broke!!!
3. After this I could no longer get to the reports because it kept prompting me for credentials when I clicked on any reports and no credentials would work giving me a "Connection was not initialized" error, my guess was that it wasn't using the service account credentials. I went into the data source and changed the "Connection Using" option to all the other option but this did not make a difference either. I also tried to specify the Connection String to the exact database and no change.
4. Lastly, I set everything back in the settings page and now I think it is using the Service account information because it does "Initialize" the connection but the error I get when clicking on any report is the below.
"""An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSetAdminID'. (rsErrorExecutingCommand)
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_rbac_GetAdminIDsfromUserSIDs", or the name is ambiguous.""""
I have also tried to create a new Report Service in the Reporting Config Mgr but when I navigate to it there is no folder or data sources there, in the original Report Server it automatically points to the database with all the reports in it. I even tried to manually add the data source and point it to the database but it has no dependent Items in it so I cant get any reports, I have no idea how to point it to the SCCM preloaded reports. Finally, I removed the Reporting Service Role each time from Config Manager to ensure it was configured with the newest data from Reporting Config Mgr but again no help.
Please let me know if you have any questions or suggections, I have run out of ideas and really want to get this up and running as I want to move on to all the other things we still need to configure within SCCM.
Thank you in advance for any input
Xhovana