More fun with expression-based connection strings in Reporting Services

Books online has a good explanation of what you can do with expression-based connection strings in the help topic "Connecting to a Data Source" (https://msdn2.microsoft.com/en-us/library/ms156450.aspx).

For example, it shows you how to dynamically change the data source (server) that your report uses based on a parameter value:

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

It also gives some good guidance around what to expect when you play with this functionality...specifically:

Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.

...and...

Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Datasets window and the Parameters list will not update automatically.

A co-worker and I found one thing that was missing. Some people might want to dynamically change the SQL credentials used to access a data source, kind of like this:

= "server=(local);initial catalog=AdventureWorks;uid=" & Parameters!UserName.Value & ";pwd=" & Parameters!PassWord.Value

This gets a bit tricky. First, when you initially build your data source you must provide credentials to access the data source (in the "Credentials" tab) so that you can run/test your queries and get a list of fields populated in your dataset. Then (as the notes above mention), when your report is "ready", you go back in and change the connection string to an expression.

However, you'll find that once you publish the report, it continues to use the *original* set of credentials you set in the credentials tab...not the new "dynamic" ones provided in your parameters.

The trick here is that after you publish, you must remember to go to the the Data Sources tab in Report Manager and select the "Credentials are not required” option. It's only at that point that the username/password that you originally set for the report is ignored and SSRS starts using the dynamic ones.

...And one more quick note...Do you really want to pass credentials via parameter values in the first place? NO! Instead, I would probably build an expression based connection string that references code in a custom assembly which builds the entire connection string for me based on non-confidential information I pass in.