I am trying to move all .zip in a specific folder to another folder. the source folder is located on another server, currently i am using
我试图将特定文件夹中的所有.zip移动到另一个文件夹。源文件夹位于另一台服务器上,目前我正在使用
EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\'
GO
Which is working if I am logged into both server, but the goal is to automate this process VIA sql server job agent. I have tried
如果我登录到两个服务器,这是有效的,但目标是自动化此过程VIA sql server作业代理。我努力了
EXECUTE sp_xp_cmdshell_proxy_account 'domain\useracc','pass'
GO
EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\'
GO
but I am receiving the following error;
但我收到以下错误;
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.
执行sp_xp_cmdshell_proxy_account期间发生错误。可能的原因:提供的帐户无效或无法创建“## xp_cmdshell_proxy_account ##”凭据。错误代码:'0'。
And also not sure if this is my solution. Please help with how I can achieve this. The file names on server1 change name and quantity everyday.
而且还不确定这是否是我的解决方案。请帮助我如何实现这一目标。 server1上的文件名每天更改名称和数量。
2 个解决方案
#1
2
I would strongly advise...Do not use xp_cmdshell. It opens up large security wholes in your surface area and makes you vulnerable to attack. xp_cmdshell should be disabled!
我强烈建议......不要使用xp_cmdshell。它会在您的表面区域打开大型安全整体,让您容易受到攻击。应该禁用xp_cmdshell!
Instead, if you want to automate this with server agent you have 2 options. My preference would be to write a simple SSIS package with a file system task and schedule this package with server agent. SSIS is underutilized for this kind of task but is actually pretty good at it.
相反,如果您想使用服务器代理自动执行此操作,则有2个选项。我的首选是编写一个带有文件系统任务的简单SSIS包,并使用服务器代理安排此包。 SSIS没有充分利用这种任务,但实际上相当不错。
Alternatively re-write your script to use Server Agent CmdExec job steps. This does not require xp_cmdshell to be enabled and reduces the attack surface.
或者,重新编写脚本以使用Server Agent CmdExec作业步骤。这不需要启用xp_cmdshell并减少攻击面。
#2
1
I Found that the following worked for me;
我发现以下内容对我有用;
In the command prompt, type services.msc, this would open the list of all services on the server.
在命令提示符下,键入services.msc,这将打开服务器上所有服务的列表。
In the list of services, look for SQL Server Agent, Right Click -> Properties. Go to Logon Tab
在服务列表中,查找SQL Server代理,右键单击 - >属性。转到登录选项卡
Change the logon to a user with access on both servers. then re-write your script to use Server Agent CmdExec job steps(Thank you Pete Carter)
将登录更改为在两台服务器上都具有访问权限的用户。然后重新编写脚本以使用Server Agent CmdExec作业步骤(谢谢Pete Carter)
#1
2
I would strongly advise...Do not use xp_cmdshell. It opens up large security wholes in your surface area and makes you vulnerable to attack. xp_cmdshell should be disabled!
我强烈建议......不要使用xp_cmdshell。它会在您的表面区域打开大型安全整体,让您容易受到攻击。应该禁用xp_cmdshell!
Instead, if you want to automate this with server agent you have 2 options. My preference would be to write a simple SSIS package with a file system task and schedule this package with server agent. SSIS is underutilized for this kind of task but is actually pretty good at it.
相反,如果您想使用服务器代理自动执行此操作,则有2个选项。我的首选是编写一个带有文件系统任务的简单SSIS包,并使用服务器代理安排此包。 SSIS没有充分利用这种任务,但实际上相当不错。
Alternatively re-write your script to use Server Agent CmdExec job steps. This does not require xp_cmdshell to be enabled and reduces the attack surface.
或者,重新编写脚本以使用Server Agent CmdExec作业步骤。这不需要启用xp_cmdshell并减少攻击面。
#2
1
I Found that the following worked for me;
我发现以下内容对我有用;
In the command prompt, type services.msc, this would open the list of all services on the server.
在命令提示符下,键入services.msc,这将打开服务器上所有服务的列表。
In the list of services, look for SQL Server Agent, Right Click -> Properties. Go to Logon Tab
在服务列表中,查找SQL Server代理,右键单击 - >属性。转到登录选项卡
Change the logon to a user with access on both servers. then re-write your script to use Server Agent CmdExec job steps(Thank you Pete Carter)
将登录更改为在两台服务器上都具有访问权限的用户。然后重新编写脚本以使用Server Agent CmdExec作业步骤(谢谢Pete Carter)