Dynamic Connection string in SSRS

Posted on 2009-04-23
Medium Priority
Last Modified: 2012-05-06
  I have MSRS report that generates connection string dynamically on my local host using username, password, server name and database name  parameters but when I deploy it to production server, I need to provide login credentials for the DataSource from Properties tab. It takes server name and database name to build the connection string but no luck with login credentials. Any help would be greatly appreciated.
Here is the connection string that I specify for the report :
="Data Source=" & Parameters!DataSrc.Value & ";Initial Catalog=" & Parameters!DBName.Value & ";ContextConnection=False;Persist Security Info=True;User ID=" & Parameters!UID.Value & ";Password=" & Parameters!PWD.Value

Open in new window

Question by:vikasbapat
  • 2
LVL 17

Accepted Solution

OriNetworks earned 1500 total points
ID: 24221285
In your connection string you are using userid and password. You have to change your connection string to look more like

Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;

Integrated Security=SSPI; tells the server to use the current logged on credentials.

Author Comment

ID: 24231207
Thanks for the response but I do not want to use logged on credentials to run the report. I want to dynamically send the login credentials for each report as parameters. It works just fine for a local server but when I deploy it to production server, only way to make this report run is provide login credentials in Property tab under DataSource section.
Basically I need this architecture to use same set of reports for 20 different clients each of them using different server name, database name, user name and password.
If I specify Credentials are not required it gives error :
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. (rsInvalidDataSourceCredentialSetting)
But when I go to "Credentials stored securely in the report server" and specify credentials, it works. I'm trying to avoid specifying user name and password in Property section - in stead I need to pass them dynamically, just the same way I can pass server name and database name.
Any help will be greatly appreciated.

Author Closing Comment

ID: 31573758
OK  i will try that

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question