SSRS 2017 Subscriptions and SQL Server Agents

  • bsychiu

    SSC Rookie

    Points: 35

    We have recently setup a new SSRS 2017 server along with a new SQL Server 2017 instance.

    Everything works as expected but when I tried to add subscriptions to a report, the subscription will fail to execute.

    When I review sql server agent log, I get the following message

    The job failed. The owner (domain\ssrs_service_account) of job <GUID> does not have server access.

    Did some research on the interweb and I tried giving my ssrs_service_account SA permission and the problem persist.

    For some reason I just couldn't find a solution to this.  Any help is appreciated!

     

  • Sue_H

    SSC Guru

    Points: 90260

    The service account for SSRS does not need to be a sysadmin so you would want to remove that. Usually the permissions get out of sorts when you don't use the Reporting Services Configuration Manager to set the service account for SSRS. Try resetting that account using the Reporting Services Configuration Manager. Then double check the subscription jobs and make sure the owner is the reporting services service account.

    Sue

  • bsychiu

    SSC Rookie

    Points: 35

    Thanks for the quick response Sue.

    Yes, SSRS service account didn't need sa access and that was added just as a test.

    So I resetting the account again using Reporting Services Configuration Manager.  Created new subscription on SSRS GUI and the problem persist 🙁

  • Sue_H

    SSC Guru

    Points: 90260

    Did you check the owner of the jobs? Make sure the password isn't expired for the service account, the account isn't disabled or locked out and if nothing else, try changing the service account. And make sure you use the configuration manager when changing it.

    Sue

  • bsychiu

    SSC Rookie

    Points: 35

    Yes, I checked the owner of the job and it is set to the service account in question.  Confirmed there is nothing wrong with the service account that i am aware of (account is enabled, password is okay).  However the sql agent job still failed.  However, the job works fine if I change the SQL Agent job owner to SA.

  • bsychiu

    SSC Rookie

    Points: 35

    So I back track what my jr DBA did on the setup.

    I changed the "Current Report Server Database Credential" from Credential "Service Account" to "Windows Account" and entered the service account information manually there.

    Also manually make sure the service account has properly permission to ReportServer and ReportServerTemp databases.

    After this change, the SQL Agent jobs created via SSRS GUI started working.

    Cheers!

  • Sue_H

    SSC Guru

    Points: 90260

    Thanks for posting back. It definitely seemed like something not right with the account - glad it's all working now.

    Sue

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

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