CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresserviceanalysis
AstonMartin
AstonMartin

Reputation: 11

Executing Analysis Services OpenQuery from T-SQL Stored Procedure

Good evening,

I have a T-SQL stored procedure which executes a PREDICTION JOIN query from an Analysis Services server via an OPENQUERY.

When I execute the stored procedure manually it completes successfully and updates the relevant tables in my database.

However, I have the stored procedure to run as a scheduled job each morning. When the job is invoked it fails with the following error message:

Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "ANALYSIS_SERVICES". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSOLAP" for linked server "ANALYSIS_SERVICES" returned message "Either the user NT SERVICE\SQLSERVERAGENT does not have access to the Analysis database or the database does not exist.". [SQLSTATE 01000] (Error 7412). The step failed.

I assume it has something therefore to do with the SQLServerAgent permissions... I have tried setting the job with myself as the owner hoping this will give the relevant permissions though this didn't work.

After that, I'm all out of ideas... unfortunately my understanding of server roles and permissions is fairly limited and any help would be greatly appreciated.

Upvotes: 1

Views: 1960

Answers (1)

Registered User
Registered User

Reputation: 8395

There are several possible solutions:

  1. Create a linked server connection that impersonates an account that has permissions in the SSAS instance.

  2. Create a proxy account and execute the code under the context of the proxy. The proxy could be an account that has permissions in the SSAS instance. Use the proxy to execute the job step. If the job step was a T-SQL command, then move the code to a SSIS package and run the package as the proxy.

  3. Change the SQL Server Agent to use a domain account. Grant permissions to the domain account in the SSAS instance.

Upvotes: 0

Related Questions