December 27, 2005 at 12:18 pm
HI,
I have a DTS PAckage which has 'Execute Process Task' Step, to run a .bat file to import files from ftp site. This package is being called by the Job. The job runs fine when the owner is SQL Service acount. When i change the owner to user account, it fails to run under proxy account. Basically Proxy account is missing some permissions. Could anyone help. Here is the error msg.
Executed as user: CORP\NBPROXY. ...Executing... DTSRun OnStart: DTSStep_DTSCreateProcessTask_1 DTSRun OnError: DTSStep_DTSCreateProcessTask_1, Error = -2147024891 (80070005) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4900 DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnSt. The step failed.
Thankyou, Deb.
December 27, 2005 at 2:12 pm
In general terms, what does the DTS package do? Avoid posting anything that reveal internal information about your organization or sensitive internal processes. For instance, saying, "It reads a flat file from a network share and imports the data into a database table," is sufficient. That would give us an idea of where the access denied error is being generated.
K. Brian Kelley
@kbriankelley
December 28, 2005 at 8:31 am
The problem sounds like the user doesn't have permissions for the extended stored procedures needed to perform command-line tasks. This is a standard security practice implemented by most DBAs and System Administrators to limit the ability to manipulate files,and executables on the "OS level" to the "chosen few". This is the purpose of the Proxy account; Task that require a higher level of security access can be executed, as jobs, by a user that has the proper permisions.
The Proxy User must have ownership of the job that executes the package, since it has the proper level of permissions and the other user probably doesn't.
December 28, 2005 at 10:29 am
When the job is owned by proxy its going to run under sqlagent service account which works fine..
When the job is owned by non-sys admin (users), the job should run under proxy right?. Here also the job is owned by user account and when it runs, it runs under proxy (and the proxy is sysadmin), but still its failing. Any thoughts please?. Is it trying to run .bat under user account??.
Thankyou.
December 28, 2005 at 11:27 am
Deborah,
If you are attempting to execute a DOS batch file (".bat") then your DTS package is attempting to execute the extended stored procedure "xp_cmdshell". The execution of the job should fail; this is how security in MS SQL Server is designed.
An extended stored procedure is a DLL that can be executed as a stored procedure from SQL Server. This group of stored procedures is very powerful. They can perform command-line functions, which could be exploited by hackers as part of an attack on the database. This is why access to the extended stored procedures is usually limited to SA and a few maintenance accounts.
Check the permissions for the users (non-sys admins). I'm sure you will find that they don't have EXEC granted for the extended stored procedures. This means the package execution should fail, because the new owner doesn't have the necessary privileges to execute the required stored procedure (probably a stored procedure beginning with "xp_").
From a security point-of-view, only the proxy user should have ownership of jobs that perform command-line functions and only the DBAs should have direct access to the proxy users.
Is there a reason for wanting to give ownership of the job to a non-sys admin user?
KP
December 28, 2005 at 12:48 pm
KP,
I agree. But in this case the user would like to run the job manually whenever needed. Thats the reason we have setup proxy also. But the job which is owned by user fails even though it runs under proxy which is sysadmin. Any thoughts?.
Does xp_cmdshell runs under the owner of the job instead of proxy?, But the log shows it runs under proxy..
-Deb.
December 28, 2005 at 2:06 pm
Deborah,
In Enterprise Manager:
1.) Go to the "Managemenet" folder for the SQL Server that runs the jobs.
2.) Right-Click and choose "Properties" from the Pop-Up Menu
3.) Select the "Job System" tab
4.) Under the "Non-SysAdmin job step proxy account" section uncheck the checkbox "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps".
Let me know if this works.
Good Luck,
KP
December 28, 2005 at 2:16 pm
One small point... I don't believe SQL Server Agent is actually executing xp_cmdshell. That would require a connection to SQL Server and then having SQL Server execute the extended stored procedure. I believe it is shelling out on its own as SQL Server Agent is a service. I could be wrong, but I believe this is the case because once upon a time we advised to drop xp_cmdshell on SQL Servers where it wasn't necessary.
However, the setting is required as Kenneth has specified with regards to executing CmdExec and ActiveScripting job steps.
K. Brian Kelley
@kbriankelley
December 28, 2005 at 2:19 pm
Yes that option is already unchecked.. One more thing i found, I created a job owned by non-sys admin, which directly runs the .bat file, it runs fine using proxy.
Only when i call the .bat under DTS using execute process task it gives error (job calls dts). You could see the error msg at my first posting.
Thankyou.
December 28, 2005 at 2:28 pm
Is the DTS package referencing the batch file differently than the job which directly runs the .bat file? In other words, same exact path, etc.?
K. Brian Kelley
@kbriankelley
December 28, 2005 at 2:30 pm
Yes, everything is same, Iam using same UNC Path. Looks like the problem is permissions with CreateProcessTask.
Thankyou.
December 28, 2005 at 3:15 pm
What type of Windows account is the user?
Is the user in the same domain as the SQL Server?
Same domain as the account used for the proxy?
Where is the DTS package stored?
Is it stored as a file or in SQL Server?
December 30, 2005 at 8:32 am
Hi KP,
The user account & proxy account are Windows account which is on the same domain as the server. The DTS package is stored under SQL server.
Thankyou.
January 24, 2006 at 1:58 am
Hi, I have the same problem. I do an DTS package which exporting an Table to an txt file.
What can I do to solve it ?
January 24, 2006 at 7:20 am
What error message do you receive?
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply