locked
Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008 RRS feed

  • Question


  • Hi Friends,

    We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

    We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

    I was able to publish my 2005 reports using VS 2005 easily.


    1) But when I try to run the reports I am getting below errors at my website home page.

    The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

    I could not get why there is no user name in above error and how it says machine name does not have permissioin?

    There should be at least some user id mentioned as per me.

    What could be the reason of above error?

    2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

    "An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

    Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

    What should I be doing to resolve above errors?

    3) Also one more point about the server that we cant access IIS manager through administrative tools even though we cloned this machine and that machine has IIS installed. Though we are able to run above URLs of Reporting Service toaccess and run reports. Do we need to install IIS again and then SQL Server 2008 again to make sure everything is fine?

    Appreciate your help and time.

    Thanks.
    Jit
    Monday, September 27, 2010 7:36 PM

Answers

  • Hi Jit,

    First of all, SQL Server 2008 Reporting Services has remove the IIS dependency. So, this issue should not have anything to do with IIS.

    1) I suggest you logon the reporting services server with administrator permissions. Then, open the report manager website and go to the Home Properties->Security, and assign a proper role to the "'DomainName\MachineName'" User or the account who logon the Machine so that they have rights to run reports.

    2) As we know, if there is no user assigned to access database of the report data source, the windows built-in 'NT AUTHORITY\ANONYMOUS LOGON' account will be used so the issue occurs. This might be double hop issue in your reporting environment. There is a simple workaround that you can store a sql account to access the database of the report datasource in your report. Here is a non-microsoft article http://dotnetrobert.com/?q=node/36 for your reference.

    thanks,
    Jerry

    • Marked as answer by jitman Saturday, October 16, 2010 12:23 AM
    Tuesday, September 28, 2010 9:00 AM

All replies

  • Hi Jit,

    First of all, SQL Server 2008 Reporting Services has remove the IIS dependency. So, this issue should not have anything to do with IIS.

    1) I suggest you logon the reporting services server with administrator permissions. Then, open the report manager website and go to the Home Properties->Security, and assign a proper role to the "'DomainName\MachineName'" User or the account who logon the Machine so that they have rights to run reports.

    2) As we know, if there is no user assigned to access database of the report data source, the windows built-in 'NT AUTHORITY\ANONYMOUS LOGON' account will be used so the issue occurs. This might be double hop issue in your reporting environment. There is a simple workaround that you can store a sql account to access the database of the report datasource in your report. Here is a non-microsoft article http://dotnetrobert.com/?q=node/36 for your reference.

    thanks,
    Jerry

    • Marked as answer by jitman Saturday, October 16, 2010 12:23 AM
    Tuesday, September 28, 2010 9:00 AM
  • Hi Jerry,

    Yes you are right. I did 1st solution as I already changed reports to have hard coded sql server access. With above changes now reports are working fine.

    Thanks a lot for your help.

    I am though not getting why it considers "DomainName\WebServerMachineName" as account which accesses the reports?

    Shouldn't there be some user i.e. "Network Service" be accessing it? From where it picks up the above?

    Thanks,
    Jit


    Jit
    Wednesday, September 29, 2010 11:55 PM