none
Expression-based Data Source + subscription = No-go?

    Question

  • Set up reports to use expression-based data sources and everything works fine as far as executing and viewing the reports. The ConnectionString expression includes the credentials, and the "No credentials required" option is selected.

    Unfortunately, am getting this when trying to create a subscription for same report. WTF?

    eportingServicesService!processing!3!12/20/2006-12:20:07:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'., ;
     Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'. ---> System.InvalidOperationException: The ConnectionString property has not been initialized.
       at System.Data.SqlClient.SqlConnection.PermissionDemand()
       at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
       at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
       --- End of inner exception stack trace ---

     

    Wednesday, December 20, 2006 9:00 PM

Answers

  • Most likely the custom assembly does not have enough permissions to access the config file and fails with a FileIOException or a code access security exception.

    Can you check if the account configured to run the RS Windows Service has permissions in the file system to read from your configuration file?

    -- Robert

    Wednesday, December 20, 2006 10:34 PM
    Owner
  • It most likely not an issue with user permissions. Are you sure your assembly has full trust?
    Tuesday, December 26, 2006 4:54 AM
  • OK, problem solved! And I almost feel ashamed to admit what it was.

    Essentially, as I mentioned in one of my first posts, the custom assembly looks to some values stored in the web.config. That's fine on the UI, but a subscription uses the WINDOWS service, and as such, my specialized web.config code wasn't working. A slight tweak to the assembly got everything on the right track.

    Thanks to those who offered advice.

    Thursday, December 28, 2006 1:16 AM

All replies

  • You probably use something like “User!UserID” in data source expression. It’s forbidden for subscriptions (http://msdn2.microsoft.com/en-gb/library/ms156307.aspx) as there is no user at subscription execution time. Also, you probably created subscription first and changed data source definition after – otherwise you would get error during subscription creation.  

    Wednesday, December 20, 2006 9:34 PM
    Answerer
  • No, I'm not doing anything like that. In fact, it kind of seems like a MSFT bug to me?

    Here is the expression for it:

    =Assembly.Namespace.ClassName.StaticMethod("ConnectionStrings", Parameters!VariableName.Value, "KeyGroupInConfigFile")

    Basically, it uses an assembly to pull a full connection string out (including the credentials) of a config file based on a key (Parameters!VariableName.Value).

    Wednesday, December 20, 2006 10:21 PM
  • Moreover, the subscription is saving the proper value for Parameters!VariableName.Value.
    Wednesday, December 20, 2006 10:22 PM
  • Most likely the custom assembly does not have enough permissions to access the config file and fails with a FileIOException or a code access security exception.

    Can you check if the account configured to run the RS Windows Service has permissions in the file system to read from your configuration file?

    -- Robert

    Wednesday, December 20, 2006 10:34 PM
    Owner
  • Will check that and report back...

    Wednesday, December 20, 2006 10:46 PM
  • So I double-checked and the Windows service is currently running under the Local System account. So, it would seem all the rights would be there for it? Also, the config file in question is actually the RS Server web.config.

    Would there be some sort of extra .Net configuration to do (keeping in mind that I pretty much made the trust on the custom assembly wide-open)?

     

     

    Wednesday, December 20, 2006 10:53 PM
  • Shameless reply to get back to the top. Anyone at MSFT able to help?
    Thursday, December 21, 2006 12:43 AM
  • Anyone?
    Monday, December 25, 2006 12:21 AM
  • It most likely not an issue with user permissions. Are you sure your assembly has full trust?
    Tuesday, December 26, 2006 4:54 AM
  • Yes, as mentioned in previous post, the assembly does have full trust. Mind you, it works fine when running it, just the subscriptions are failing.

    It kind of seems like the sequence of events when getting a subscription report ready has getting the datasources ready as the 1st step. If the datasource is based on expression, and that expression uses a saved parameter value, it bombs. Can you confirm or refute that? Thanks

    Tuesday, December 26, 2006 8:08 PM
  • OK, problem solved! And I almost feel ashamed to admit what it was.

    Essentially, as I mentioned in one of my first posts, the custom assembly looks to some values stored in the web.config. That's fine on the UI, but a subscription uses the WINDOWS service, and as such, my specialized web.config code wasn't working. A slight tweak to the assembly got everything on the right track.

    Thanks to those who offered advice.

    Thursday, December 28, 2006 1:16 AM
  • Hi Bingo,
    What was the tweak?

    Thanks

    Monday, February 05, 2007 9:09 AM