May 24, 2005 at 8:12 am
I have created a DTS Package that executes a Cube rebuild using the OLAP Object. This Package resides on the STRVBIP01 SQL server, and works when I execute as a DTS Package. However, when it is scheduled, I get the following Error Message. The run as user (STR\svc_bipdata01) has OLAP Administator Priveledges, and has access to every cube on the server.
Executed as user: STR\svc_bipdata01. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSOlapProcess.Task_2 DTSRun OnError: DTSStep_DTSOlapProcess.Task_2, Error = -2147221387 (80040075) Error string: Cannot connect to the Analysis server on computer 'STRVBIP01'. Connection to the server is lost Error source: DSO Help file: Help context: 1000440 Error Detail Records: Error: 0 (0); Provider Error: 0 (0) Error string: Cannot connect to the Analysis server on computer 'STRVBIP01'. Connection to the server is lost Error source: DSO Help file: Help context: 1000440 DTSRun OnFinish: DTSStep_DTSOlapProcess.Task_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Thanks in advance for your help.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
May 24, 2005 at 8:45 am
Have you sheduled this job to run while your in the office? It appears that the connection has been lost when the job ran. This could be for a number of reasons (eg. Network problems, services stopped, analysis services restarting after an unexpected fatal error etc.) if the job runs successfully while your watching it I cant see any security issues. Double check the event viewer at the time the job failed, there might be a clue there.
Let us know what happens and if it continues we'll dig a little deeper.
May 24, 2005 at 8:58 am
While this was orginally scheduled to run at night, during the testing phase, I discovered this error. Since then- I have right clicking the job and starting it manually.
The job has never suceeded and always throws this same message, however, the DTS Package always does when I execute that. Seems to me that this is a security issue somehow, but I am not sure how as the SQL Agent runs under the account listed as the run as user (STR\svc_bipdata01). This is a domain user, who belongs to the OLAP Administrator Group on the STRVBIP01 Server, and is also in the same roll as the user I log in as, which is what the DTS executes under when I execute it manually. This is the only role that exists in Analysis Services at the present time.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
May 24, 2005 at 9:27 am
You mentioned the 'run as' user - are you passing the uername & pwd on the DTSrun command line? Or are you using xp_cmdshell, and setting the run as user on the sql agent step? If doing the latter, try doing the former, with the agent service running under the bip service user.
Have you tested:
i) the commandline command in a standard DOS prompt window?
ii) physically logging on to the machine as the svc_bipdata01 user and tried to execute the package?
Steve.
May 24, 2005 at 10:22 am
"are you passing the uername & pwd on the DTSrun command line? Or are you using xp_cmdshell, and setting the run as user on the sql agent step? "
Steve,
Neither. I right clicked on the DTS and choose schedule Package. This creates a job using the DTSRUN PID Command line, but no user or password exists on the command line. It was my understanding that this executed the package under the security context of the user that the SQL Agent is running under.
I have not tested either of the things you have listed, but I will - and will let you know the result.
Thanks,
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
May 24, 2005 at 5:35 pm
if you used the RC-> Schedule then you're using the former. Personally (and it's just a personal opinion) I'd create the DTSrun command string myself. Take a look at the dtsrunui app (comes as part of std install). It lets you enter the details you want (server, user etc) and whilst i think it's primary intention was to allow "operators" to execute packages, it has a neat feature where you can get it to generate the dtsrun command line strin with arguements. What makes it snazzier (is that a word?) is that it also lets you generate the encrypted hex version (as you'll see within your SQLAgent job now) - thereby encrytping the uid/pwd combo that you put in as the running user.
I've not tested this but it would be interestin to see what takes precedence re: "running user" - is it the user I specify in the DTSrun command string or is it the account under which sqlaagent is running? I would have assumed it was the one in the string???
Let us know how you go.
Steve.
June 1, 2005 at 7:36 am
Ok, so After reading the last post in here, I got pulled of to create a "Quick" Data Warehouse (Star Schema DB, Data Cubes, BI Portal) for another client, and just now got back to look at the job. And guess what - although I changed nothing, it now works.
I have admin rights on the box, so I made sure that no user permissions have been changed in SQL or Windows, and none were. But, to my disbelief, it works.
Now the only issue is finding out why it wasn't working in the first place, as things that "fix" themselves tend to unfix themselves.
I believe I will use your suggestion Steve and use the dtsrun utility. I use this often to fire DTS on foreign computers, so I am familiar enough with it, plus the help section on this one actually IS helpful. Rather than depending on the scheduler to run a DTS using the correct setting, I can specify each and every one.
Thanks, and I will let you know the results of researching why the job used to fail.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
August 6, 2007 at 7:51 pm
Hey, how did this tests turn out? I am having the same problem.
August 7, 2007 at 8:29 pm
Solved in my case, might be the same problem: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?&query=Freddy+Rios&lang=en&cr=us&guid=&sloc=en-us&dg=microsoft.public.sqlserver.olap&p=1&tid=fb7a9225-604d-49eb-89c4-48f0c3973f97
It is important to note that it appears to be a bug, and all that we did was a work around.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply