locked
Service account issue about Reporting Service

    Question

  • Hi Ruiz,

         Where do I send your beverage of choice.  I had my DBA apply the permissions as outlined in the thread you sent.  
         It is working now.  I can now create a project.  Yeah!!

         I am going to mark your thread as the answer, however, can I ask one additional question.

         In order to make everything work, I had to use my TFSService account.  When I tried using the TFSReport account on the Reporting Services Site.  All reports stop generating, and if I change the DB connection to use the TFSReport, I get a connection error.  Both Accounts have exactly the same permissions.  So, I suspect I messed up something on my TFS AT server (which is where reporting services run - they only connect to a different DB)

    Thanks again for all the help.

     

    Thursday, December 03, 2009 10:17 PM

Answers

  • Hi Stillwell,

    To change to credential used by Reporting Service to connect to the report server database,

    1 make sure you have the administrator right on DataTier
    2 Open Reporting Service Configuration Manager
    3 Select Database page and Click "Change Credentials"
    4 On Database Server Page, Select Current -Integrated Security as Authentication Type, and Test Connection,  Next
    5 On Credentials Page,  Select Windows Credentials as Authentication Type, type the user name and password of TfsReports, Next
    6 On Summary Page, Next,
    7 On Process and finish Page, there are 3 jobs done by Reporting Service, and all of them should be successful. then Finish
              Generating rights scripts
              Applying connection rights
             Setting DSN 

    If TfsReports account still failed to logon database, open the sql server  with SQL Server Management Studio, check TfsReports exists in security=>logins, and the account has correct role in following databases
    1:master  public,RSExecRole
    2:msdb     public,RSExecRole,SQL AgentOperatorRole,AgentReaderRole,AgentUserRole,
    3:ReportServer db_owner,public,RSExecRole
    4 :ReportServerTempDB db_owner,public,RSExecRole

    Best Regards,
    Ruiz




    Ruiz Yi MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg @ microsoft.com Best regards, Ruiz
    Friday, December 11, 2009 1:22 AM
    Moderator

All replies

  • Hi Stillwell,

    The account that run Reporting Service is not important, typically we use network service.

    The account TFSReport is used to connect to SQL Server. Make sure that cuurent user have administrator right on DataTier and open Reporting Service Configuration Manager on App Tier to config the database connection.

    Best Regards,
    Ruiz
    Ruiz Yi MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg @ microsoft.com Best regards, Ruiz
    Monday, December 07, 2009 9:13 AM
    Moderator
  • Hi Ruiz, 

      I guess I should of been a little more specific about this particular issue.
      
      The Reporting Service Service on the App Tier is utilizing the Network Service ID.
     
      The TfsOlapReportDS, TfsReportDS, and the Reporting Service Configuration Manager currently require that I put in the TFSService ID in them so I can create a project.

      If I change TfsOlapReportDS, and TfsReportDS to use TFSReports ID it will not allow me to create, or retrieve reports.
      If I change the Reporting Service Configuration manager to use the TFSReports ID, I get an error.

      According to my DBA, both the TFSReports ID, and the TFSService ID have the same authority levels on the Data Tier.

      Does this help clear up my question?
    Monday, December 07, 2009 5:17 PM
  • Hi Stillwell,

    First the account running Reporting Service is not important.

    Reporting Service uses TFSReport account to access the report database ReportServer and ReportServerTempDB stored in SQL Server

    TfsOlapReportDS and TfsReportDS are used by Reports in Reporting Service to access TfsWarehouse in SQL Server Database and SQL Server Analysis Service. As TfsService has the right to access both database and analysis service, we use TfsService as the account of the 2 DS.

     So that my suggestion is that
    1 Use Network Service to run Reporting Service

    2 Use TFSReports to  connect to dabase ReportServer and ReportServerTempDB, you can edit the account in Reporting Service Configuration Manager=>Database =>Change Credentials

    3 Use TfsService for reports to connect to TfsWarehouse(in database and analysis service). You can edit it in http://<server>/Reports/Pages/DataSource.aspx?ItemPath=%2fTfsOlapReportDS and http://<server>/Reports/Pages/DataSource.aspx?ItemPath=%2fTfsReportDS

    If there is any problem, please feel free to tell me.

    Best Regards,
    Ruiz
    Ruiz Yi MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg @ microsoft.com Best regards, Ruiz
    Wednesday, December 09, 2009 9:36 AM
    Moderator
  • Hello Ruiz, 

      As suggested in number 2 above, I changed my Credentials to TFSReports.  When I do that, I get the following error.  When I change it back to TFSService, it works fine.  Everthing else is configured as you suggested it should be.  I am confident it is something in my configuration, but not sure what.  As long as it is working, I am not too concerned, but I do want to get it corrected, so it will be in a best practice configuration.

    Reporting Services Error

    --------------------------------------------------------------------------------

     

    The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed) Get Online Help

    For more information about this error navigate to the report server on the local server machine, or enable remote errors

     

    --------------------------------------------------------------------------------

    SQL Server Reporting Services

    Thanks again

    Stillwell

    Thursday, December 10, 2009 8:59 PM
  • Hi Stillwell,

    To change to credential used by Reporting Service to connect to the report server database,

    1 make sure you have the administrator right on DataTier
    2 Open Reporting Service Configuration Manager
    3 Select Database page and Click "Change Credentials"
    4 On Database Server Page, Select Current -Integrated Security as Authentication Type, and Test Connection,  Next
    5 On Credentials Page,  Select Windows Credentials as Authentication Type, type the user name and password of TfsReports, Next
    6 On Summary Page, Next,
    7 On Process and finish Page, there are 3 jobs done by Reporting Service, and all of them should be successful. then Finish
              Generating rights scripts
              Applying connection rights
             Setting DSN 

    If TfsReports account still failed to logon database, open the sql server  with SQL Server Management Studio, check TfsReports exists in security=>logins, and the account has correct role in following databases
    1:master  public,RSExecRole
    2:msdb     public,RSExecRole,SQL AgentOperatorRole,AgentReaderRole,AgentUserRole,
    3:ReportServer db_owner,public,RSExecRole
    4 :ReportServerTempDB db_owner,public,RSExecRole

    Best Regards,
    Ruiz




    Ruiz Yi MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg @ microsoft.com Best regards, Ruiz
    Friday, December 11, 2009 1:22 AM
    Moderator
  • Hi Stillwell,

    I will mark my reply as answer. If it does not help you, please feel free to unmark it and I will follow up.

    Best Regards,
    Ruiz


    Ruiz Yi MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg @ microsoft.com Best regards, Ruiz
    Thursday, December 17, 2009 7:15 AM
    Moderator
  • Ruiz,
      Thanks for all the help.  It was greatly appreciated.  Your reply was the answer.  Sorry for the delay in responding, has been a bit crazy.
      Have a wonderful holiday season...

    Thanks again
    Friday, December 18, 2009 4:47 PM