February 10, 2010 at 12:24 am
Hi all,
I am stuck with this critical problem in executing the SQL server job. And spent a long time rectifiying it.
I have an analysis services cube. I need to process the partitions created in the cube every hour. Fot that i have created a .dtsx integration services package, and a Job scheduled every one hour to run the package. On processing the partition the data is moved from OLTP database to OLAP database.
When the job is run its throwing an error,
"Either the user, NT AUTHORITY\SYSTEM, does not have access to the database, or the database does not exist".
I have the user NT AUTHORITY\SYSTEM access to the OLTP database with all permissions granted.
I tried running the package independently. Here it throws a different error saying "database/<local user> does not have access to the database".
I will be greatful if anyone could help me in this regard.
February 15, 2010 at 6:37 am
Some basic questions to start with (some may even seem stupid :))
* Did you deploy the cube to the right destination on the server?
* Has your connection manager in BIDS been set up correctly?
* Does your server use windows integrated security or sql server security?
Regards.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 16, 2010 at 6:37 am
Use a data souce with integrated security.
Deploy to server - MSDB, and use server security.
Run manually. If it won't run manually, you still have a problem.
Run sql agent with a proxy setup for this account, unless you are running sql agent under a domain account that has access.
We do something like this, and although I didn't set it up, this is what I remember.
Also are SQL and SSAS on the same server, or separate servers? If separate, you may need Kerberos setup.
Greg E
February 16, 2010 at 7:06 am
Thank you Koen and Greg for the response.
I was actually able to resolve the issue.
Firstly there was no problem with the job script. Then i opened the SSIS package .dtsx using my Visual studio -> Business Intelligence -> Integration services project and debugged it.
The Initial catalog name i.e the OLAP database server name was mentioned wrong for the cube processing task. And the OLTP connection used wrong credentials too. I resolved them and re-deployed the package to run it successfully.
Some one on the other forums suggested me to run the .dtsx package independently to know the actual error. That paved the way for my solution.
Hope this helps someone with the same issues!! 🙂
February 16, 2010 at 7:21 am
Glad that you resolved the issue.
darshan-987176 (2/16/2010)
The Initial catalog name i.e the OLAP database server name was mentioned wrong for the cube processing task. And the OLTP connection used wrong credentials too. I resolved them and re-deployed the package to run it successfully.
That fell under my question:
Has your connection manager in BIDS been set up correctly?
(although I have to admit it is a rather fuzzy question :-D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply