none
user authentication in connection string RRS feed

  • Question

  • We have eliminated all domain user logins to our SQL Servers and are using SQL authentication exclusively.  I need to connect to Reports with a connection string using a given user account.  I can connect with integrated security but I want to put a specific account name/password into my .net connection string.

    Is this possible - we can't seem to make it work.

    Wednesday, June 20, 2012 6:27 PM

Answers

  • It looks to me like the error message is referring to report server authentication rather than the database connection credentials.  Did you test the connection using the Reporting Services configuration tool?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:58 PM
    Wednesday, June 20, 2012 8:09 PM
  • Hello JohnSLG,

    I am sorry for my delay. I want to check whether the issue still exists on your side. If it has, please try to review the following suggestion.

    From your description, I consider that the connection string is possible to connect to the report server as data source instead of the report server user. Therefore, I suggest that we can check whether the SQL instance allow Windows Authentication or Mixed authentication. In addition, please logon the report server in SSMS via SQL Engine using the domain account. If it fails, please try to connect the database with a sysadmin and add the domain user as a login account of Reporting service DB.

    If you have any questions about this issue, please let me know.

    Regards,

    Edward

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Edward Zhu

    TechNet Community Support


    • Proposed as answer by Edward Zhu Tuesday, July 17, 2012 8:37 AM
    • Edited by Edward Zhu Tuesday, July 17, 2012 8:39 AM
    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:59 PM
    Tuesday, July 17, 2012 8:37 AM
  • We removed the user information from the connection string because the connection seems to be made in the context of the domain account of the user making the request for Reporting Services despite what is included in the connection string.  We set up a domain user group, adding everyone with permission to access Reports.  After adding that group as a User to ReportServer we are able to gain access.

    We're still not sure why the credentials in the connection string are ignored in favor of the domain account of the user making the request but we'll role with it.

    Thanks.


    • Edited by JohnDMP Tuesday, July 17, 2012 12:59 PM
    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:59 PM
    Tuesday, July 17, 2012 12:57 PM

All replies

  • The connection string keywords for SQL Authentication are "User ID=yourlogin;Password=yourpassword".  The "Integrated Security" keyword should be omitted.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, June 20, 2012 6:34 PM
  • We tried:

    Integrated Security=false;Persist Security Info=true;Initial Catalog=reportserver;Data Source=Server2;User ID=user; Password=password;Connection Timeout=150; Min Pool Size=20;

    We connect to the report server and then are told "The permissions granted to user 'DOMAIN ACCOUNT USER NOT THE ONE IN THE CONNECTION STRING' are insufficient...

    Wednesday, June 20, 2012 7:13 PM
  • It looks to me like the error message is referring to report server authentication rather than the database connection credentials.  Did you test the connection using the Reporting Services configuration tool?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:58 PM
    Wednesday, June 20, 2012 8:09 PM
  • Hi There

    Dan is right, Have you given permission to the domain user logins in which you are trying to connect to your report server

    Reporting Services uses role-based security to grant user access to a report server. For a report server that runs in native mode, there are two types of roles: Item-level roles and System-level roles.

    The Item-level roles are used to view, add, and manage report server content, subscriptions, report processing, and report history. We can assign Item-level roles on the root node (the Home folder) or on specific folders or items. In this issue, you can try Syed’s suggestion to assign Item-level role to specific user on specific items. Additionally, you can also custom Item-level roles by using specific Item-level tasks based on your requirement.

    References:

    if you have any questions please ask

    Many thanks

    Syed

    Wednesday, June 20, 2012 8:13 PM
    Moderator
  • The USER in the connection string has Browser & My Reports roles to every folder in Reports.  I don't want it to check the permissions of A DIFFERENT USER.  Can I not use the permissions associated ONLY with the user in the connection string???  We don't want to give every domain account permissions to report server but anyone who gets to the menu where they can access the connection will already be authorized to use reports - so we want them to use the user account named in the connection string.


    HOWEVER...to test the connection I also granted Browser & My Reports to the domain user group that EVERYONE using SQL belongs to.....the user that was bounced above IS a member of that group.
    • Edited by JohnDMP Thursday, June 21, 2012 11:59 AM
    Thursday, June 21, 2012 11:58 AM
  • I don't see an option inside Reporting Services congfiguration to test connection strings.  The database is set up to use the NT AUTHORITY/NetworkService account.
    Thursday, June 21, 2012 12:00 PM
  • Hello JohnSLG,

    I am sorry for my delay. I want to check whether the issue still exists on your side. If it has, please try to review the following suggestion.

    From your description, I consider that the connection string is possible to connect to the report server as data source instead of the report server user. Therefore, I suggest that we can check whether the SQL instance allow Windows Authentication or Mixed authentication. In addition, please logon the report server in SSMS via SQL Engine using the domain account. If it fails, please try to connect the database with a sysadmin and add the domain user as a login account of Reporting service DB.

    If you have any questions about this issue, please let me know.

    Regards,

    Edward

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Edward Zhu

    TechNet Community Support


    • Proposed as answer by Edward Zhu Tuesday, July 17, 2012 8:37 AM
    • Edited by Edward Zhu Tuesday, July 17, 2012 8:39 AM
    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:59 PM
    Tuesday, July 17, 2012 8:37 AM
  • We removed the user information from the connection string because the connection seems to be made in the context of the domain account of the user making the request for Reporting Services despite what is included in the connection string.  We set up a domain user group, adding everyone with permission to access Reports.  After adding that group as a User to ReportServer we are able to gain access.

    We're still not sure why the credentials in the connection string are ignored in favor of the domain account of the user making the request but we'll role with it.

    Thanks.


    • Edited by JohnDMP Tuesday, July 17, 2012 12:59 PM
    • Marked as answer by JohnDMP Tuesday, July 17, 2012 12:59 PM
    Tuesday, July 17, 2012 12:57 PM