How to transfer files at remote location using sql server job?

I have a job configured in which it transfers the file at remote location.
My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on failure unknown user name or bad password.
I am not able to find out the exact reason that why this file is not being transferred.

If there is an error in my code then it should give the same error from the command prompt.
Can anyone help me understand why I am getting the error?

the main reason for failures from batch files with something that works from the command line is the % signs.

On the command line, a single % is required, so for %a in (*.exe) do echo %a works fine, but if you wanted that same command in a batch file, you have to double the % signs, which makes that command now for %%a in (*.exe) do echo %%a

This is only for those that you calculate yourself – using variables like %TEMP% would not change.

that’s probably because the user trying to move the files is the user that is running the job (probably the user that SQL Server agent is configured to run). Try giving read permission to this user on the folder you are reading the files from and write on the folder you are writing the files.

The issue has been solved . As the remote location folder was shared and was accessible to every one. My command was working fine from command prompt , even any user was able to create their own file on that location and also able to delete the file from that location.

The issue was related to user. My job was being executed by servernameadministrator and remote location administrator password was changed due to that bad password error occurred. I told my IT Team about the problem and they reset the server password as older one, and my job began to work fine.

The issue was solved.

I just want to know how my sql job authenticates the server login as I go through the script of my job and found nothing helpful regarding authentication.

Can any one explain it to me.

Thanks

Nitesh Kumar

This is quite interesting since all permission are being overlapped again and again.

Let me explain,

Your Sql Job run as a different user you connect to the server (Some cases they are the same but that’s the best scenario). You can check this by running this command on the Sql Server editor (enable the xp_cmdshell as well):

xp_cmdshell 'whoami'

SERVERNAMESqlUser

and compare it with the console result (logged in as JhonDoe):

c:UsersJhonDoe> whoami
SERVERNAMEJhonDoe

This usually takes the scenario where both results are different, so as you might guess the script is being executed as a different user so this user might have different privileges.

Then, you need to understand that whenever you try to connect to a shared folder. You’ll be ask for user and password unless you are the same user as the owner of the Shared Folder. However, this can take you to a misunderstanding.

What windows really does is try to connect to the Shared Folder with the same credentials your are logged in:

• You have logged in as JhonDoe with a password 123456 on the computer named PC1 so your user looks like this:

  c:UsersJhonDoe> whoami
  PC1JhonDoe
  

• The Shared folder belongs to JhonDoe with a password 123456 (This user with this password created the Shared Folder) and the computer, named PC2, that host the Shared Folder show this:

  c:UsersJhonDoe> whoami
  PC2JhonDoe
  

When Windows tries to connect to the Shared Folder it uses the PC1 login (JhonDoe 123456) since this is your local login and then it compares with the remote login information and it matches exactly with the PC2 login (JhonDoe 123456). Just then, it let you access the shared folder without any prompt for password.

However, when it faces this situation:

• PC1 (JhonDoe-12345)

  c:UsersJhonDoe> whoami
  PC1JhonDoe
  

• PC2 (JhonDoe-123456)

  c:UsersJhonDoe> whoami
  PC2JhonDoe
  

The credentials don’t match since they have different passwords. Then it fails because they are, actually, different users.