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

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

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.

|||

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).

|||Moreover, the subscription is saving the proper value for Parameters!VariableName.Value.|||

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

|||

Will check that and report back...

|||

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)?

|||Shameless reply to get back to the top. Anyone at MSFT able to help?|||Anyone?|||It most likely not an issue with user permissions. Are you sure your assembly has full trust?|||

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

|||

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.

|||

Hi Bingo,
What was the tweak?

Thanks

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

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

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.

|||

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).

|||Moreover, the subscription is saving the proper value for Parameters!VariableName.Value.|||

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

|||

Will check that and report back...

|||

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)?

|||Shameless reply to get back to the top. Anyone at MSFT able to help?|||Anyone?|||It most likely not an issue with user permissions. Are you sure your assembly has full trust?|||

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

|||

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.

|||

Hi Bingo,
What was the tweak?

Thanks

expression-based connection strings difficult to manage

Expression-based connection strings are great but they are specific to each report which makes them difficult to maintain. Shared expression-based connection strings are not supported.

I'm looking for a way around this. I'm hoping to write a custom data extension, and in the custom data extension, do all the "dynamic" logic that sets the connection string (which you normally would be doing in your expression-based connection string).

I know the above can be done. What I don't know is how, inside a custom data extension, to get the value of expressions like User!UserID? (I want the connection to run under stored credentials, but I want to customize the connection string and add a property to it based on the UserID who is logged into Report Manager.)

Create a assembly with a static method that takes a text parameter(userid) and returns the connectionstring and add that assembly (look in BOL for custom code or something like that on how to add it to a report and deploy the assembly to the report server) and then in the datasource it is something like this =Assembly.Class.Function(User!UserID).

I have done it myself, but in my case it was depending on the reportpath.|||

Interesting workaround, SteffoS. Thanks for the reply. That may be the best available. The only downside is that you have to actually use expression-based connection strings. And in doing that, there are two unfortunate problems:

1. Every report must call that expression instead of doing development as normal

2. (More importantly) you can't preview your dataset when it's tied to an expression-based connection string. The workaround to that is switching to a shared datasource during development, then making it an expression before you deploy, but that's a pain in the rear.

Anyone else have thoughts?