FTP from xp_cmdshell and SQL Server 2008
-
I get an "Access is denied" error message when I attempt to ftp via master.xp_cmdshell with the following code:
exec xp_cmdshell 'ftp -d -i -s:\\<servername>\medlog\sql_apps\afmsql\daasc_reb\txt\work_reb_ftp1.txt 192.67.xxx.xx'
However, I can run the aforementioned from a command prompt. Of course my account and the sql server service accounts are different. I'm running Windows Server 2003.
Any thoughts on the above error?
-
Maybe xp_cmdShell is disabled? Default is disabled. Also the service account might not have enough permissions to do this. I assume you are a local admin / domain admin and the service account is a low privileged user
-Roy
-
xp_chdshell is enabled. And yes, I'm a domain admin but the destination server is in another domain. I'm passing the username and passwords in the xxxxx_ftp1.txt file.
-
pretty sure the issue is this:
\\<servername>\medlog\sql_apps\afmsql\daasc_reb\txt\work_reb_ftp1.txt
when you jump to xp_cmdshell, it doesn't matter what YOUR credentials are; sql server uses the credentials that is running the sql service, which might be Local System or Network System, which do not have access to any shares on the domain, since they have never logged in.
you'll need to change the login used to start the server to a domain login that has access to the share:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell:
I see your point but in SQL 2000 the same code runs w/o the SQL Server service account having direct access to the share. When FTP is executed it looks in the .txt file for login credentials (username and password). Could it be anything else other than the service account needing access to the share?
-
Lowell has what I'd suggest. Make sure the service account and see the share.
-
yeah this is a very common issue; pretty sure the issue is permission to get to the share; especially with the error access denied.
change the login used to run the service to YOUR login and password.
stop and start the service.
try the ftp code again.
when it works, you know that the old login used to run the service did not have permission.
it probably works on your 2000 instance becuase the login used to run THAT service is a domain admin, but the 2008 intance is still local or network.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I'm a domain admin. I changed the service account to me and now FTP works from xp_cmdshell. I'll have to figure out what specific permissions the service account needs rather than being a domain admin to FTP from xp_cmdshell.
Any thoughts?
-
One would be to have permission to the shares (Your source file and target file). The user that runs the service should also be a domain account (Not an admin) so that it has privilege over the network. I am not sure what other permissions are needed.
-Roy
-
Here's what I always do, it might help you out as well.
I create a new regular user in AD or on the local machine called SQLAdmin. (ie yourdomain\SQLAdmin)
i make that user a local admin on the SQL server machine, basically so i can do bcp and bulk inserts from any folder on the machine.
since it's on the domain, I make sure it's in the any groups needed to get to any specific shares.
finally, i use THAT account to run the SQL server service.
Others will tell you where i've given too many rights, so hopefully you can refine it from my overeager but effective user, but that's worked for me.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Once you create a new user like Lowell pointed out and change the service account using the SQL Server Configuration manager, it will give the appropriate right to all the required folders. Then give additional rights to the folder where you have the BCP or Snapshots. I would be a bit reluctant to give the user local admin rights though. Sorry Lowell....
That is how I would do it.
-Roy
-
The account was created in AD and granted full control permissions on the location of the .txt file and where the files are placed. Now all is fine.
Thanks everyone.
-
Glad to know that things have been sorted out..
-Roy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply