Post reply

Windows Auth (Integrated Security) with SSRS and Domain Global Security Group

  • March 18, 2009 at 4:34 pm

    #207151

    I have developed a SQL Reporting Services Report which uses Windows Authentication in the data source and that runs against a SQL 2k5 database . The report runs fine when I explicitly grant a non admin user access to the database but I would like to use Domain security groups.

    I've created a Global security group in the domain, added the user to the group, added the group to the server logins and added the login to the database and granted them the db_datareader role. With this setup I am able to connect with Management Studio and run queries against the DB as the user but the report fails with the following error:

    Cannot open database "DataSourceName" requested by the login. The login failed. Login failed for user 'DOMAIN\username'.

    Shouldn't this work?

    Also should this same security structure work in SSRS? I would like to put this Domain security group in the Browser Role to allow users in the group to run this report.

    If this is not the correct setup, does anyone have any suggestions how I could manage this without giving each individual user access to the database and SSRS?

  • March 27, 2009 at 3:22 pm

    #967769

    Anyone have any comments or questions?

  • March 27, 2009 at 4:07 pm

    #967786

    The standard fix for this in IIS is to disable anonymous access and enable impersonation, if that did not fix it then you may have to add the account used to run SSRS in SQL Server on the server level and the database level this time the datasource database.

    The global group you could just use shared datasource with one permission.

    Kind regards,
    Gift Peddie

  • March 27, 2009 at 4:42 pm

    #967795

    Thank you very much...disabling anonymous access in IIS on the Report server fixed my issue. After looking at the web.config for the /reports virtual folder it seems that impersonation is enabled by default for SSRS.

  • March 27, 2009 at 4:47 pm

    #967796

    I am glad I could help, this error is two part if you are not in IIS then there is only one fix to change SQL Server to both Windows and SQL authentication. So if you get the problem in IIS you use the previous post and in direct connection to SQL Server you use the above.

    Kind regards,
    Gift Peddie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply