April 4, 2017 at 8:39 am
I am trying to copy files from our hosting site server to pur local server with XCOPY. If I log on the destination server, and copy-past the command from my SQL window, into a dos prompt, it works, but fails from a SQL SSMS window.
The value of @CMD is:
xcopy \\192.168.xxx.xxx\c$\LiveDBDownload\Diff_Redgate\*.SQB C:\Baks\DIFF_RedGate\
But the error I get when running in SQL is "Invalid drive specification"
SET @CMD = 'xcopy ' + @folderSourceDIFF + ' ' + @folderTargetDIFF
select @CMD -- view the contents
EXEC master.dbo.xp_cmdshell @cmd -- Run the file copy
SQL Services need to be a local administrator account, instead of "NT Service\MSSQLSERVER" ??
April 4, 2017 at 8:47 am
I'm thinking different permissions between the account that you log on as and the account that SQL Server runs under. What do you get from these?
xp_cmdshell 'dir \\192.168.xxx.xxx\c$\LiveDBDownload\Diff_Redgate\*.SQB'
xp_cmdshell 'dir C:\Baks\DIFF_RedGate\'
John
April 4, 2017 at 9:11 am
homebrew01 - Tuesday, April 4, 2017 8:39 AMI am trying to copy files from our hosting site server to pur local server with XCOPY. If I log on the destination server, and copy-past the command from my SQL window, into a dos prompt, it works, but fails from a SQL SSMS window.The value of @CMD is:
xcopy \\192.168.xxx.xxx\c$\LiveDBDownload\Diff_Redgate\*.SQB C:\Baks\DIFF_RedGate\But the error I get when running in SQL is "Invalid drive specification"
SET @CMD = 'xcopy ' + @folderSourceDIFF + ' ' + @folderTargetDIFFselect @CMD -- view the contents
EXEC master.dbo.xp_cmdshell @cmd -- Run the file copy
SQL Services need to be a local administrator account, instead of "NT Service\MSSQLSERVER" ??
SQL Services account would need to be an account on both boxes. If you are in an environment where you can use AD for things, then I would create an AD account for the SQL Server Service and the SQL Agent Service and then set the SQL Agent Service account as being an admin on the 192.168.xxx.xxx machine. The "C$" share is an administrative share so you would NEED to be an administrator on the second machine in order to access that folder.
Since it works as you, I am assuming you have an AD environment AND that you are an administrator on the second machine.
NT Service/MSSQLSERVER likely has enough permissions on the Target machine (the C:\Baks\DIFF_RedGate\) but it doesn't have administrator permissions on 192.168.xxx.xxx.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 4, 2017 at 10:37 am
John Mitchell-245523 - Tuesday, April 4, 2017 8:47 AMI'm thinking different permissions between the account that you log on as and the account that SQL Server runs under. What do you get from these?
xp_cmdshell 'dir \\192.168.xxx.xxx\c$\LiveDBDownload\Diff_Redgate\*.SQB'
xp_cmdshell 'dir C:\Baks\DIFF_RedGate\'John
The remote DIR returns "The network path was not found."
The local DIR works.
April 4, 2017 at 4:12 pm
Never use references like "\\192.168.xxx.xxx\c$\..." in PROD environment.
It would require giving xp_cmdshell proxy account the full set of sysadmin access rights, which would be really dangerous.
Create a proper share on \\192.168.xxx.xxx, give read-only permission on that share to the proxy account and use it in the script.
And make sure the host \\192.168.xxx.xxx is actually accessible:EXEC master.dbo.xp_cmdshell 'ping \\192.168.xxx.xxx'
_____________
Code for TallyGenerator
April 5, 2017 at 9:40 am
I'm trying FTP instead, but stuck on that too.
April 5, 2017 at 10:01 am
FTP will be a similar can of worms with a few fun new problems as you then have to have credentials stored in plain text. Either that or have no credentials required and that feels like a large security risk.
Any reason you are not using a proxy account or changing the SQL Server Agent Service to use an AD account and setting up a non-administrative share on the secondary machine?
Or you could pull this entirely out of SQL and use Windows task manager to manage the data transfer. You'd just need to schedule it at some point after backups complete to prevent file locking in the transfer. Using windows task manager would allow you to specify the user account it runs as in a slightly more simple manner than SQL Server does.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2017 at 10:45 am
The source server is at our remote hosting facility not on an AD domain.
The target server is local at my office, on AD.
These are all new servers. The old servers use FTP, but I didn't like the clear text password mentioned.
Windows Admin and server connectivity is not my strong suit (obviously !! )
April 5, 2017 at 10:58 am
I'm guessing though that you have some method of connecting to the remote machine via remote desktop or have some way to manage that server apart from just FTP?
If you use the administrative share, you will need to be an administrator on the 192.168.xxx.xxx machine to copy it with xcopy, copy, robocopy, etc. The only way around that is to not use the administrative share. But since they are not on the same domain, I am not entirely sure how you would get a trusted authentication between the 2 machines. Been quite a while since I had to do that... and I think we had to have both machines in the same workgroup to make it play nice but I am not 100% sure if that is the case or not. That would have been back in the days of Windows 2000. Since then, I've either had the machines on the same domain or same workgroup.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2017 at 11:45 am
It works from a DOS prompt, but not from a SQL Window, (both just testing)
Both servers have "Homebrew01" in the administrators group.
But I need something to work from a SQL scheduled job.
April 5, 2017 at 12:14 pm
Ah right. If it runs from the command prompt, it works as you.
Is 'Homebrew01" an AD account or a local account on both machines?
If it is a local account, I'd set up a different local account (something like SQLAgent), give them both the same password and put them in the Administrators group.
Next log into the 192.168.xxx.xxx computer, create a share on C:\baks with a name of something like "Backups" and give Homebrew01 and SQLAgent full access to that folder
Next log into each machine to verify you can connect as SQLAgent to verify both machines can use the account and that the passwords are correct.
Next, try to navigate to \\192.168.xxx.xxx\Backups while logged in as SQLAgent from the local machine to verify the network share is set up correctly
If all of the above is successful, try creating a new file or folder in that directory from the local machine. This is just to verify that the connection was set up properly
Now, since we have the SQLAgent account set up and verified it works, add the SQLAgent user as a sysadmin, set the SQL Server Agent Service to run as "SQLAgent" and restart the SQL Server Agent Service.
If Homebrew01 is an AD account, get your AD admin to make the SQLAgent account and add it to the administrators group on both of those machines and then continue at the first "Next" above.
And as the last step, try running that command again. It should succeed now.
As a cleanup step, you should remove SQLAgent from the Administrators group and give it the permissions described in this link:
https://docs.microsoft.com/en-us/sql/ssms/agent/select-an-account-for-the-sql-server-agent-service
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 5, 2017 at 7:43 pm
Can you map that drive "using different credentials"?
_____________
Code for TallyGenerator
April 5, 2017 at 10:03 pm
homebrew01 - Tuesday, April 4, 2017 8:39 AMI am trying to copy files from our hosting site server to pur local server with XCOPY. If I log on the destination server, and copy-past the command from my SQL window, into a dos prompt, it works, but fails from a SQL SSMS window.The value of @CMD is:
xcopy \\192.168.xxx.xxx\c$\LiveDBDownload\Diff_Redgate\*.SQB C:\Baks\DIFF_RedGate\But the error I get when running in SQL is "Invalid drive specification"
SET @CMD = 'xcopy ' + @folderSourceDIFF + ' ' + @folderTargetDIFFselect @CMD -- view the contents
EXEC master.dbo.xp_cmdshell @cmd -- Run the file copy
SQL Services need to be a local administrator account, instead of "NT Service\MSSQLSERVER" ??
Close the SSMS application. When you go to open it again by clicking on it, don't. Instead, hold down shift and right click on it. Then try the "Run As Administrator" option and see if that works.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2017 at 5:46 pm
Some of the replies here are spot on.
$ admin shares can only be accessed by accounts with admin privilege. So either a local admin on the box itself, or accounts that have admin privilege from a domain perspective and on the remote box will be able to get to that $ share.
The NT Service\MSSQL... account that SQL is running under which is the context the XP_CMDSHELL command is using does NOT have the rights to the admin share on the other box.
This is why the DIR also fails. (good test!!)
There are a few options that appear to have been already described:
1. Get XP_CMDShell working under a proxy account that does have access as an admin to the $ share on the remote box
2. Run the local SQL under an account (domain account) that does have access as an admin to the $ share on the remote box
(both of these seem over zealous in terms of needing to have admin access on the remote box!!)
So alternatively and form a more security focused perspective:
1. do you have access to a AD?? Set up a proper share on the remote box with access only for a specific AD account. Run the XP_CMDShell account under a proxy for that specific AD Account. I think this would be the ideal scenario.
2. OR, for any of the options, you could set up a local user on the remote box. Setup a share on the remote box with access granted to that account. (If you wanted, you could give that local account Admin and just use the $ share & if you were not security focused) On the local box you need to MAP the drive with that account first before accessing:
-- map the drive
NET USE x : \\<remote>\<Share> /user:<remote>\Account <password>
-- copy the file
xcopy x:\<file> <local>
-- disconnect the map
NET USE X: /d
Plain text.... anyone with access to the local box could probably see that account and password, so this is why a locked down account on the remote box is a better idea than giving it admin on the remote box.
Hope that helps.
April 7, 2017 at 9:12 am
angeloc - Thursday, April 6, 2017 5:46 PMSome of the replies here are spot on.$ admin shares can only be accessed by accounts with admin privilege. So either a local admin on the box itself, or accounts that have admin privilege from a domain perspective and on the remote box will be able to get to that $ share.
The NT Service\MSSQL... account that SQL is running under which is the context the XP_CMDSHELL command is using does NOT have the rights to the admin share on the other box.
This is why the DIR also fails. (good test!!)
There are a few options that appear to have been already described:
1. Get XP_CMDShell working under a proxy account that does have access as an admin to the $ share on the remote box
2. Run the local SQL under an account (domain account) that does have access as an admin to the $ share on the remote box
(both of these seem over zealous in terms of needing to have admin access on the remote box!!)So alternatively and form a more security focused perspective:
1. do you have access to a AD?? Set up a proper share on the remote box with access only for a specific AD account. Run the XP_CMDShell account under a proxy for that specific AD Account. I think this would be the ideal scenario.2. OR, for any of the options, you could set up a local user on the remote box. Setup a share on the remote box with access granted to that account. (If you wanted, you could give that local account Admin and just use the $ share & if you were not security focused) On the local box you need to MAP the drive with that account first before accessing:
-- map the drive
NET USE x : \\<remote>\<Share> /user:<remote>\Account <password>
-- copy the file
xcopy x:\<file> <local>
-- disconnect the map
NET USE X: /dPlain text.... anyone with access to the local box could probably see that account and password, so this is why a locked down account on the remote box is a better idea than giving it admin on the remote box.
Hope that helps.
The OP stated thought taht the remote machine does not have access to his AD. Therefore an AD account will fail.
As for mapping the drive, that feels like excessive work inside of an SQL job and anybody with appropriate access on the SQL Server could then see the account password which feels like a huge security risk. We do not store any passwords in plain text where I work.
It would be much more simple to just create a share on the remote machine and grant it access to the appropriate account, would it not? We already know that the account homrebrew01 works, so what is the harm in setting up a similar account on both machines to be used as the SQL Server Agent Service account?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply