September 16, 2013 at 11:40 am
I have 2 servers that are in the same rack, but not on a domain. Ultimately, I want a SQL scheduled job to copy files from Server_A to Server_B
Logged on to Server_B as Windows\Administrator, I want to copy backup files from Server_A to Server_B with the following SQL Statement:
DECLARE @cmd varchar(500)
SET @cmd = 'copy /Y ' + '\\Server_A_IP_Address\f$\Backup\231_StandbyServer\' + 'MyBackupFile.bak ' + 'F:\Backups\231_Server\FULL\'
EXEC master.dbo.xp_cmdshell @cmd
which runs this:
copy /Y \\Server_A_IP_Address\f$\Backup\231_StandbyServer\MyBackupFile.bak F:\Backups\231_Server\FULL\
I get "access denied"
But if I put the "[font="Courier New"]copy /Y \\Server_A_IP_Address[/font]...." statement into a DOS command line, it works fine.
What would prevent the administrator account from running the command in SQL, that runs fine in DOS ?
Thoughts ?
September 16, 2013 at 2:57 pm
It is running under the SQL Agent service account when you run it in a job. I believe you need to create a proxy
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2013 at 5:19 am
homebrew01 (9/16/2013)
I have 2 servers that are in the same rack, but not on a domain. Ultimately, I want a SQL scheduled job to copy files from Server_A to Server_BLogged on to Server_B as Windows\Administrator, I want to copy backup files from Server_A to Server_B with the following SQL Statement:
DECLARE @cmd varchar(500)SET @cmd = 'copy /Y ' + '\\Server_A_IP_Address\f$\Backup\231_StandbyServer\' + 'MyBackupFile.bak ' + 'F:\Backups\231_Server\FULL\'
EXEC master.dbo.xp_cmdshell @cmd
which runs this:
copy /Y \\Server_A_IP_Address\f$\Backup\231_StandbyServer\MyBackupFile.bak F:\Backups\231_Server\FULL\
I get "access denied"
But if I put the "[font="Courier New"]copy /Y \\Server_A_IP_Address[/font]...." statement into a DOS command line, it works fine.
What would prevent the administrator account from running the command in SQL, that runs fine in DOS ?
Thoughts ?
When the servers are in a workgroup you will need to create a matching user (username and password) on each server for the account that runs the sql server agent service. The sql server expects to accces the filesystem unchallenged, hence the matching usernames. This is most commonly encountered when setting up log shipping, see my article here[/url] for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 17, 2013 at 6:50 am
I had not go to the point of running it as a job, just running in a query window, or as DOS command. Does it matter if they are matching Windows accounts, or matching SQL logins ?? I was trying Windows "Administrator" account yesterday, which worked from DOS, but not a SQL query window.
September 17, 2013 at 6:56 am
Windows accounts. Running from a query window means it is running using the SQL Server Service account.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2013 at 12:43 pm
From Server_B, I am not able to pull from Server_A. But if I log on Server_A, I can push to server_B ..... hmmmm
Well, that's good enough to get what I need done,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply