Query Execution Failed for Dataset - permissions

  • r.gall

    Ten Centuries

    Points: 1024

    I suspect I have a permissions issue that I cannot track down the source of. My report runs for me fine in Visual Studio so I know it should work, but when deployed, the people using it can't run it and get the error:

    "An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'Addresses'. (rsErrorExecutingCommand)

    For more information about this error navigate to the report server on the local server machine, or enable remote errors"

    I have an SSRS report that gets its data by running a simple SELECT statement directly in the report (as opposed to running a stored procedure). The SQL is so simple I didn't see it necessary to create a stored procedure as its along the lines of "SELECT fldAddressLine1, fldAddressline2, fldCity, fldPostcode FROM tbl_Address"

    The setup is:

    • Windows login called 'joebloggs'
    • 'joebloggs' is a member of the Windows Active Directory group called "OrderSystemUsers"
    • Database server is called "sql2014\production"
    • Database is called "OrderSytemDB"
    • Report is called "OrderAddressReport" which connects to a deployed DataSource called "OrderSystem_LIVE"
    • In Visual Studio, the report "OrderAddressReport" contains a dataset called "Addresses" which links to datasource "OrderSystem_LIVE" and contains the simple SELECT statement mentioned above.

     

    I am unable to enable remote errors so I am trying to figure out what the issue is.

    1. I have added the group "OrderSystemUsers"  into the "Security > Logins" folder in the Object Explorer Pane in SSMS for "sql2014\production" and in the Properties under 'server roles' have got 'public' ticked. Also, unther the 'User Mapping' section I have ticked the database "OrderSystemDB", and then in the database roles have ticked 'db_datareader', 'db_datawriter', and 'public'.
    2. In the database section of the Object Explorer (sql2014\production > Databases > OrderSystemDB > Security > Users) I can see that the Active Directory group "OrderSystemUsers" is listed, and in the properties for this under "Membership" both 'db_datareader' and 'db_datawriter' are ticked

    What could I have missed? I have tried:

    • In SSMS, going to the properties of the table the SELECT  statement is referenceing ("tbl_Address") and under 'Permissions' adding the user group "OrderSystemUsers" explicitly granting the 'Select' permission.
    • Having the user log off and on (in case permissions need to be pulled in  at log on)
    • Left it for a day in case there was an extended delay in permissions populating somewhere

    I need the user 'joebloggs' to be in the Active Directory group 'OrderSystemUsers' so that I can add more users in future without having to grant permissions over and over.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22506

    First, did you try what was suggested in the error:

    navigate to the report server on the local server machine

    Based on what you have tried, my thought on this is it might not be a permission related error, or at least not a permission error with the user "jobebloggs" or the AD group "OrderSystemUsers".

    Since it works in visual studio, my guess is it is a data source configuration error.  The VERY first thing I would check is if the report you uploaded HAS a data source associated with it.  It might in VS, but if that data source didn't exist in SSRS, it will map to nothing and need to be manually mapped.

    Once you are certain the data source is correct, Check what user the data source connect to the database as.  If it is connecting as a specific user, that user would need permissions at the database level.  If it is passing the credentials along from the user viewing the report, do you have other reports that do this where it is successful?

    But before you can do much troubleshooting, you need to know the error.  Blindly assuming it is a permission based error may lead you down a bunch of rabbit holes only to discover you are looking at the wrong thing.

  • SQL_Hacker

    SSCommitted

    Points: 1636

    I agree with Brian Gale, you need to go to the local server. You may need to add the AD group to the Security page in the Reports web screen. What version of SSRS are you running? If you open Reporting Services Configuration on the server, go to the Web Portal and then click on the top link (assuming it's not encrypted) and that will take you to the home page of SSRS. Then you need to get to the Security page and add the AD group to have at least Browser permission so the users can view the report.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • r.gall

    Ten Centuries

    Points: 1024

    Thanks Brian.

    Since checking the AG was set against the datasource and dataset correctly, my Administrator has been able to enable remote errors for me this morning and I now have a meaningful error stating that the SELECT permission was denied:

    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'Addresses'. (rsErrorExecutingCommand)

    The SELECT permission was denied on the object 'vwOrders', database 'OrderSystemDB', schema 'dbo'.

    I didn't include the full version of the original SELECT query, and it actually contains a sub-query in the list of columns selected referencing this view.

    Are the roles "db_datareader" and "db_datawriter" be only sufficient for selecting from tables, and not views? Do I instead need to explicitly grant the SELECT permission on the View object?

  • Mr. Brian Gale

    SSC-Insane

    Points: 22506

    Granting db_datareader should be all that is needed to have permissions on the view unless you have explicit DENY permissions on the view for a group that user is a member of or for that user directly.  If I remember SQL Server permissions correctly, DENY takes precedence over GRANT.  If the user doesn't have an explicit "DENY" on the view, you will need to dig deeper.

    If it isn't an explicit DENY, it sounds like your data source is set to connect as a specific user or isn't passing the correct user along and thus the permissions you are setting are not for the correct user.  My next step would be to load up profiler (or extended events aka XE) and have a look at who the code is running as.  Once you know this, you can adjust permissions appropriately.

    Something I'd be careful of though is granting db_datawriter permissions.  Depending on the data in the database, I'd be careful of db_datareader too.  If any confidential data exists in the database, db_datareader is a bit of a security risk.  db_datawriter is a HUGE security risk on a live system as they can write to any table.  I would strongly recommend following a "least permissions" model and use the data reader and data writer roles sparingly.  It may be fine for a test system or during initial setup while testing something, kind of like turning off a firewall or granting "EVERYONE" full control on a folder, but I try not to leave those things set that way long term.

  • Sue_H

    SSC Guru

    Points: 90533

    Yup, deny takes precedence with the only exception being column level permissions and table denies - which is suppose to be deprecated at some point. A member of a role or being in a group with deny could also impact this. With nested groups and such, sometimes it can be faster to just look for the denies to figure out if the user is impacted from that. There is a script up here on SSC that you could use to check for denies:

    Find Deny permissions on database objects

    Sue

     

     

Viewing 6 posts - 1 through 6 (of 6 total)

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