September 15, 2006 at 4:00 am
We are using sql 2000 sp3. it was set up so that sql server runs under system account. Sql server agent runs under local system account.
I wish to run a regular unattended dts package to export data to a .csv file and thought it would be helpful to automate the backup of the .csv to an archive folder (the folders have read write permissions only for administrators).
I thought to use exec master..xp_cmdshell from a dts task. Testing the statement in query analyser gives access denied error when I connect using either sa or local administrator login account. I would not expect the DTS task to work either.
I’ve read enough on the forums to expect it is a permissions problem – perhaps sql server's system account does not have the permissions. I also read I should not really be using xp_cmdshell! Anyway:
1. what is a safe and secure method to copy the .csv file using DTS?
2. if I am to use xp_cmdshell what login accounts should I be creating and what permissions should I assign the account? (if you can suggest a step by step article even better)
Thanks for your help.
September 15, 2006 at 9:49 am
This article will help you troubleshoot your problem:
http://www.databasejournal.com/features/mssql/article.php/3372131
September 15, 2006 at 10:47 am
thanks, i have had a look at that. my concern is the comment "Saying that, you need to be careful how you set up your environment to make sure someone does not corrupt your system by using the almighty powerful xp_cmdshell extended stored procedure."
is it worthwhile to create a domain user account specifically for sql server service and server agent rather than using the local system account? to allow the domain user account the permissions to copy files would i need to make it a member of a security group that has the necessary permissions? which security group?
can't see the forest for the trees at the moment!
September 19, 2006 at 4:53 am
testing this further, the access denied is due to not having the modify permission on folder. the xp_cmdshell was run through query analyser (connect with admin login) and dir *.* command works. therefore looking at BOL i expect that this applies: "When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running".
Changing folder security to allow everyone to modify files allows xp_cmdshell to copy the file to a subfolder. however i want to restrict users to read only access.
to accomplish this i think i should:
1. add the account that sql server service runs under to the folder
2. give that account the modify permissions for the folder
to allow DTS schedule, repeat the above for the account that sql server agent runs under - BOL says "When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account".
correct me if i am wrong but i don't think i can add the local system account to the folder.
therefore should i create a new account for sql server and sql server agent to run under first? and then add the account to the folder with neccessary permissions. thanks for any guidance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply