sbj avatar image
sbj asked

Running sql agent job with domain account for Power BI Report Server

I have a problem trying to run sql agent jobs owned by a domain account. We have Power BI Report server installed on a SQL server 2016 std edition, let´s call i ServerA, but the ReportServerPBI- and ReportServerPBITempDB-databases resides on another SQL Server that we can call ServerB, running an Enterprise Edition. When the users creates their reports through Power BI Desktop and schedules them to update with various intervals, the SQL Agent jobs are created on ServerB but the owner is the serviceaccount (domain user) that runs the Power BI Report Server on ServerA, let´s call it svcA. I have made sure svcA have the necessary permissions on ServerB, that is: db_owner and RSExecRole in ReportServerPBI- and ReportServerPBITempDBs, RSExecRole in master and RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole in msdb. But everytime any of the jobs run, I get this error: The job failed. Unable to determine if the owner (Domain\svcA) of job 17080A7D-813D-4CF9-84FE-DD6AF96EF130 has server access (reason: Could not obtain information about Windows NT group/user 'Domain\svcA', error code 0x5. [SQLSTATE 42000] (Error 15404)). If I change the owner of the jobs to ServerBs agentaccount, it works like a charm. But then the Power BI users cannot change the schedules of their jobs anymore, so I really don´t want to do that. Both servers and all of the serviceaccounts resides in the same domain. I was thinking maybe it could be solved by granting the SQL Agent service account on ServerB 'Read' permission to svcA´s AD-object, but the AD-team says that is only valid when the accounts are in different domains. I´m stumped and out of ideas. My google-mojo has failed me, so I´m hoping someone out there have a solution they want to share. Anyways, happy holidays to you all!

sql agentreport services
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Davtjen avatar image
Davtjen answered

I use sql accounts to connect to our datawarehouse(server) while executing the ssis packages.

Develop the package in Visual Studio (including connection managers using reader account for production reads and read/write for datawarehouse table creates, both sql users). Users connect to datawarehouse for PowerBI datasets.

After developement we deploy to datawarehouse(server) ssis catalog. From there a job is created with sa user as owner. On the package in ssisdb we choose configure option and update the connection managers user settings. The sql job then is configured to run the package on given schedule.

Works fine.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.