Is it possible to have a dynamic data source RRS feed

  • Question

  • I am looking to develop a SSRS report, and the idea is that I want to view backup history on various DB servers. I would like to have a drop down list of servers, and from there I can choose a server and a query will run on that server. I have been trying to find out some information on this, but my understanding is that this can be done via URL, but not sure if thats the best way to do this.


    Tuesday, August 31, 2010 11:33 AM


All replies

  • http://www.sqlservercentral.com/articles/Development/2945/
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 31, 2010 11:54 AM
  • Assuming Server Name to be one of your Report Parameter .

    ="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

    Take a look at expression based connection strings .



    Thanks .

    Rajkumar Yelugu
    Tuesday, August 31, 2010 12:02 PM
  • Tuesday, August 31, 2010 12:17 PM
  • ok Guys

    I managed to get this working after a lot of fiddling around, the problem that I now have is that the SSRS report is on server A, the report only works when internet explorer is opened on ServerA, if I open the report from ServerB, then I get the error from SQL which states that its trying to connect as an anonymous user. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. From what i can see, the expression based connection is not trying to connect using the windows details of the user who has opened the browser. Is there anything that i am doing incorrectly here. Below is the error that i am getting from reporting services.


    • An error occurred during client rendering.
      • An error has occurred during report processing. (rsProcessingAborted)
        • Cannot create a connection to data source 'Backup_Server'. (rsErrorOpeningConnection)
          • For more information about this error navigate to the report server on the local server machine, or enable remote errors
    Monday, September 6, 2010 10:30 AM
  • Hi MrFlinstone,

    To this error, I would recommend you follow there steps to troubleshoot this issue:

    1. Open the Sql Server Comfiguration Manager, make sure Sql Server and Sql Server Reporting Server are running.
    2. Click the Sql Server Network configuration, have a test to see whether protocol TCP/IP if Enabled.
    3. Make sure your SQL Server running under a domain user account.

    Hope this helps.

    Best regards,

    Challen Fu

    Tuesday, September 7, 2010 2:10 AM
  • Hi MrFlinstone,

    For your last question "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON", it is caused by double-hop. If I am correct, the data source for the report is stored in a server other than the Report Server. The Report Server is configured to use Windows Authentication. No Kerberos delegation is configured.

    In this case, to solve the issue, we can enable Kerberos authentication for the Report Server. Below are the steps: 

    1. Configure the SQL Server Reporting Services(SSRS) to run under a domain account or the account 'Network Service'.
    2. Make sure the <RSWindowsNegotiate/> is configured under <AuthenticationTypes> in the rsreportserver.config.
    3. Logon the domain control, register the Service Principal Name(SPN) for the service account(in step 1). We need to register the following SPNs
          SETSPN -A HTTP/<servername>:<port> <domain>/<service account>
          SETSPN -A HTTP/<servername>.<domain>.<com>:<port> <domain>/<service account>
    4. In the domain control, make sure the option 'Account is sensitive and cannot be delegated' for the service account is uncheced via the 'Active Directory Users and Computers' 
    5. In the 'Active Directory Users and Computers' , make sure the service account is trusted for delegation.
    6. Make sure the Internet Explorer(IE) in the client(in this case, it is ServerB) is enable to use Windows Integrated(Tools > Internet Option > Advanced > Enable Integrated Windows Authentication*).

    Alternatively, we can use 'Stored Credential' for the datasouce to solve the issue.

    For more information, please see:
    Understanding Kerberos Double Hop: http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx
    How to: Configure Windows Authentication in Reporting Services: http://msdn.microsoft.com/en-us/library/cc281253.aspx

    If there is anything unclear, please feel free to ask.

    Jin Chen

    Jin Chen - MSFT
    Monday, September 13, 2010 6:12 AM