January 16, 2007 at 12:48 am
Hi ...
I have 2 servers sql2005 and fileserver. I need to have sql2005 access a file share on fileserver in order to copy a file to fileserver using the following statement:
exec master.dbo.xp_cmdshell 'copy \\sql2005\d$\output\data.txt \\fileserver\ibase$\data.txt /Y'
When I grant Everyone full control on \\fileserver\ibase$ the copy goes through successfully. I would like to limit access to the share to only the account that sql would use to access the share. If I use just the sql service domain account, it fails with Access Denied.
What account/s should I grant access to \\fileserver\ibase$ in order to do the copy?
Many thanks
January 16, 2007 at 5:28 am
use a domain accounjt itself and give proper sharing and ssecurity permissions for the user to that share. make sure if sql server can read that share using TSQL statements. eg: just write a select query to read the text file in the share.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
January 18, 2007 at 7:17 am
You need to give some additional information. SQL Server can be running under a domain user account or a local system account. If it is running under a local account, you will need to use the everyone group because you do not have credentials on the file server. There are a number of options using proxy accounts in SQL 2005 specifically to work around NT permissions. You should look at what is available. You also have the ability to limit which users can run xp_cmdshell and how credentials can be passed. Remember that if you set the sql service account to a domain account, set xp_cmdshell to use the sql service account, and give users the ability to run xp_cmdshell directly, you will have given these users full access to your entire network.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply