asked on

Dynamic Connection string in SSRS

Hello,
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

ASKER CERTIFIED SOLUTION

OriNetworks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vikasbapat

ASKER

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

vikasbapat

ASKER

OK i will try that