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” (http://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. 

Comments (18)

  1. furmangg says:

    Good post. This got me thinking. Let me take the same concept and apply it to reports running on top of SSAS. Let’s say you have a report that should be secured so that it only shows you the divisions and regions you have access to. You’ve implemented this security at the cube level. How do you get SSRS to authenticate to SSAS using your current credentials so that the security in the cube takes effect? Several options…

    1. If SSAS and SSRS are on the same box, then have SSRS just pass through the user’s credentials

    2. If SSAS and SSRS are NOT on the same box, setup kerberos delegation (can be complex) and have SSRS pass through the user’s credentials

    3. Use expression-based connection strings in combination with either the Roles or EffectiveUserName SSAS connection string properties. See the end of section 16.4.2 in Teo Lachev’s SSAS book for more details on those connection string properties new in SSAS2005.

  2. Sacrilege says:

    I have been trying to do just what you suggested and build an expression based connection string with a custom assembly but I’ve had zero luck.

    The report runs fine with a static connection string. The report server is loading the assembly and returning the correct value but when I set the value in the connections string box I keep getting the following error:

    Format of the initialization string does not conform to specification starting at index 0.

    I’ve tried every way I could think of to format the expression but the same error occurs each and every time.

  3. Sounds like your "expression/connection string" doesn’t evaluate to a valid connection string once you put it together.

    Why don’t you hardcode the connection string on a temporary basis, then drop a textbox on the report and make it’s value the result of this expression. Then you can see whether or not the connection string itself is any good?

  4. Sacrilege says:

    Thanks, I’ve been down that route already. I did that exact thing. The expression evaluates fine and in fact all the custom function does right now is spit out the same static connection string I have been using.

    Do you have an example of code that does work as you suggested in your article?

  5. Bilal says:

    Does expression based connection strings work with stored procedures

  6. Hi as you mentioned i have done. But it fails at real time it is working fine during preview of rdl file but once it deploy on the report server the we can dynamically pass database name through parameters but not the UserId and Password. so I think it may be theoratically possible but not practically.

    If you have code then please send!

  7. Sarvesh, you can do this. However, if you attempt to pass in a username / password, you do need to select that Credentials are not required (as mentioned above. Are you sure you did this?

  8. Ben W says:

    I initially encountered the same problems as Sarvesh.  I found that I needed to configure an unattended report processing account to get this working.  I followed the steps in this article:

    http://msdn2.microsoft.com/en-us/library/ms156302.aspx

    There is more information is in the "No Credentials" section of this article:

    http://msdn2.microsoft.com/en-us/library/ms160330.aspx

    Hope this helps,

    Ben

  9. Kim says:

    I am having the same type of problem. Works in designer but get the following error when deployed to server: An error has occurred during report processing.

    Error during processing of the ConnectString expression of datasource ‘Dynam’. Any Suggestions?

  10. Jason says:

    While trying to pass the username/password, I also found difficulty (works fine if you store the credentials on the report server of course). The error occurs when my code uses the reportExecutionServer.LoadReport(<reportpath>,null) method.  Error message is: "The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.".  I followed the steps above, whereby you deploy the report with the credentials set, then go into report manager on the report server, and set the report’s data source setting to "Credentials are not required".  Do i need to look into setting the report up for unattended execution or similar? thanks

  11. Jason says:

    OK, i set the unattended execution account and it worked fine. thanks!

  12. Florent says:

    Hi,

    I want to use a dynamic connection string to display my reports, but this sample doesn’t work in my case.

    My data source name is : xxx_clearquest and my static connection string is :

    Data Source=nanrp1001nanrp1001;Initial Catalog=CSAProd

    When I use this static data source my reports are well displayed. So i add in my report the "ServerName" variable with the default value "nanrp1001nanrp1001", and modify the data source connection string like this :

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

    All seems to be right, but when I display my reports the following error occurs :

    An error has occurred during report processing.

    Cannot create a connection to data source ‘xxx_clearquest’.

    For more information about this error navigate to the report server on the local server machine, or enable remote errors.

    The report cannot connect to the data source. I try to found logs on server, but don’t found anythings refers to this error.

    Have you got any suggestions ? Thanks

  13. Florent says:

    I finally found my issue.

    Expression-based connection seem to doesn’t work with shared datasources. I add an embedded datasource in my report and it’s finnaly work.

    Helpfull link : http://msdn.microsoft.com/en-us/library/ms159165.aspx

  14. dLP says:

    I was able to get the expression based connection string to work in BIDS and when it was deployed.  It took a few tries to get it right, but when i did, it worked in both…but thank you for your post, it was helpful…

  15. skeskin says:

    thank you

    if you could explain about oracle connection string in ssrs 2008 i would be glad

  16. abhi says:

    hey i was working on reports with dynamic connection strings if they are to be entered manually my report works fine. but what i need is i need to populate the list of servernames in the network and if i select one of the server names that should generate a list of data bases for that server and then i need to connect to the query using those values for the connection string to my dataset that runs the actual report. if any body has any valuable information please feel free to post it here or else please mail me at crunchycocktail@gmail.com. guys this is urgent. i was able to generate the list of databases by enetering the name of the server parameter but i even want the servername as a parameter. thanks

Skip to main content