none
How to give default UserName and Password to report to ran without asking log in and password?

    Question

  • Hi,

    I have created a report in SQL Server data tool 2010 and in that report, I wrote a query which is pulling records from tables which are in SQL Server 2008.

    After that I have deployed report on dev2012 server. Now when I try to ran this report, it gives me below error message,

    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'xyz'. (rsErrorOpeningConnection)
        • Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    So I went to the 'Data Sources' of that report and selected the below option,


    After this when ever I ran the report, report is asking me log in name and password. And after I enter that, report is displaying properly.

    Now my question is, in my company reports are executing through active batches, so I can't select this option to give user name and password every time to ran the report. So is there a way that I can apply in the report so no one has to provide user name and password every time when some one is executing active batch for that report?

    I have one though that I can create one security group for this report and select below option to ran the report without giving user name and password every time,

    So before asking customer service to create this kind of security group I want to make sure that is this going to work? And Do I need password also? 

    Please let me know and thanks for the help in advance.


    Vicky





    • Edited by Vicky134 Thursday, February 06, 2014 3:03 PM
    Thursday, February 06, 2014 2:50 PM

Answers

  • Vicky,

    The option to store the credentials for the data source in the server is a good option. When the report runs SSRS will use those credentials to connect to the database.  An Active Directory (AD) security group cannot be used.  You can either used a database user (stored locally in the database) or an AD user account.  No matter which option you choose, the account must have the appropriate permissions on the database (i.e., to connect and read the data).  If you choose an AD account, you would select "Use as Windows credentials when connecting to the data source", otherwise leave that unchecked.

    You would use security groups to lock down security on the report or folder containing the report(s), giving people the ability to browse and run the reports.

    Thursday, February 06, 2014 3:31 PM
  • Hi Vicky,

    From your description, it seems that the issue occurs when you view the report from the report server or report manager, right? If so, the reason why you need to enter a Log In Name and Password before you view a report should be that the report Data Source is configured to use prompted credentials.

    To fix this issue, please refer to the following steps to change the connection:

    1. Double click your Data Sources in your Report Manager to find out the data source which your report uses.
    2. Open this data source and check the connection using option directly.
    3. You can see what kind of connection you are using and change it to Windows integrated security or stored credentials.


    The following document is about specifying credential and connection information for report data sources:
    http://msdn.microsoft.com/en-in/library/ms160330(v=sql.100).aspx

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Vicky134 Monday, February 10, 2014 9:50 PM
    Friday, February 07, 2014 9:18 AM

All replies

  • Vicky,

    The option to store the credentials for the data source in the server is a good option. When the report runs SSRS will use those credentials to connect to the database.  An Active Directory (AD) security group cannot be used.  You can either used a database user (stored locally in the database) or an AD user account.  No matter which option you choose, the account must have the appropriate permissions on the database (i.e., to connect and read the data).  If you choose an AD account, you would select "Use as Windows credentials when connecting to the data source", otherwise leave that unchecked.

    You would use security groups to lock down security on the report or folder containing the report(s), giving people the ability to browse and run the reports.

    Thursday, February 06, 2014 3:31 PM
  • Hi JJordheim,

    What is ' AD account'? How can I create this AD Account?

    Can you please explain me as I don't have any idea  about this and I tried to search about this but didn't find any good article?


    Vicky

    Thursday, February 06, 2014 4:12 PM
  • AD account means account maintained through Active Directory of the domain

    see

    http://www.visualwin.com/New-User-AD/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 06, 2014 4:58 PM
  • Yep.  You or a system administrator would create a user object in Active Directory.  This is assuming your environment is setup this way.  If you have people who handle this for you, go to them.  Otherwise you'll need to open Active Directory Users and Computer, go to the container or OU where you want the user object to exist, and create this account.
    Thursday, February 06, 2014 5:14 PM
  • Hi Vicky,

    From your description, it seems that the issue occurs when you view the report from the report server or report manager, right? If so, the reason why you need to enter a Log In Name and Password before you view a report should be that the report Data Source is configured to use prompted credentials.

    To fix this issue, please refer to the following steps to change the connection:

    1. Double click your Data Sources in your Report Manager to find out the data source which your report uses.
    2. Open this data source and check the connection using option directly.
    3. You can see what kind of connection you are using and change it to Windows integrated security or stored credentials.


    The following document is about specifying credential and connection information for report data sources:
    http://msdn.microsoft.com/en-in/library/ms160330(v=sql.100).aspx

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Vicky134 Monday, February 10, 2014 9:50 PM
    Friday, February 07, 2014 9:18 AM