Dynamic Connection string in SSRS

Posted on 2009-04-23
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
    LVL 17

    Accepted Solution

    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

    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

    OK  i will try that

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now