none
SSRS report from different database at run time prompting for USer ID and Password.

    Question

  • Hi,
    Environment: We have SQL SSRS 2008 R2 installed on Windows 8 sever.
    We have different databases SQL 2008 R2 located on different server/have different IP addresses.
    We have a drop on the report to choose which db we want to fetch the report from. We have been able to achieve that by having same USerID but different passwords on all the databases.
    Our report fires the same query and fetches the exact dataset from any database chosen.

    Problem: It prompts for a user id and password of the db on every time we try to get it from a different database. Is there a way to set the credentials/Settings at run time so that we are not prompted for the same. 
    We have already tried putting the credentials in datasource properties with nogo.

    Help will be much appreciated.
    Saturday, February 23, 2013 12:24 PM

Answers

  • Hi Yatri,

    Are you using the same account and password when accessing different database servers? If so, it should be a domain user account. In this way, I suggest that you don’t specify user name and password in the dynamic connection strings. Instead, you can configure the data source to use stored credentials, and then specify the domain user account. 

    Reference:
    Store Credentials for a Data Source (Report Manager)

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, February 27, 2013 10:19 AM
    Moderator

All replies

  • Create a single data source and provide expression based connection string as follows;

    =SWITCH(
    Parameters!DatabaseSelectionParameter.Value = 1, "Data Source=Server1;Initial Catalog=Database1;User Id=uid;Password=123456",
    Parameters!DatabaseSelectionParameter.Value = 2, "Data Source=Server2;Initial Catalog=Database2;User Id=uid;Password=123456"
    )

    Hope this will help.


    Thanks
    Salman
    http://muhammadsalmanx.wordpress.com

    Sunday, February 24, 2013 7:48 AM
  • Hello Salman,

    I have tried this option....

    This works when the user id and password (credentials) are same across different database but when they are different (credential ) , it prompts for a user id and password for the first time and every time we try to get it from different database ......

    Thanks

    Monday, February 25, 2013 4:45 AM
  • I tried this approach with different user ids and passwords on different servers and it is working fine without prompting for the user id and password.

    I followed the below mentioned steps to configure expression based connection strings:

    1. Add New Data Source

    2. Write connection string expression (don't click on Edit button, In this way Connection Properties will leave as blank otherwise you'll see the server and user name of the first connection string in Connection Properties dialog box and may be this is the reason for you password prompts, so give it a try and directly write you expression without clicking on Edit button)

    You are requested to please share the outcome.


    Thanks
    Salman
    http://muhammadsalmanx.wordpress.com

    Tuesday, February 26, 2013 6:13 AM
  • Hi Yatri,

    Are you using the same account and password when accessing different database servers? If so, it should be a domain user account. In this way, I suggest that you don’t specify user name and password in the dynamic connection strings. Instead, you can configure the data source to use stored credentials, and then specify the domain user account. 

    Reference:
    Store Credentials for a Data Source (Report Manager)

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, February 27, 2013 10:19 AM
    Moderator
  • Hi Mike & Salman,

    @Mike,

    I am using different same user name but different password for accessing different database server....

    And hence can not configure as you have mentioned

    @Salman,

    The solution works on my location machine, but If I deploy the report on another server then the report prompts for credentials as it was doing before :(

    I mean, On my local machine it will not ask me for credentials for every time I change the database

    But when the report is uploaded on the server (one different server where I have this as well as other reports uploaded); It prompts for credentials....

    No change

    Sorry for the delay in reply

    Thanks


    Thursday, March 07, 2013 9:52 AM