xcopy command failed in sql server 2000 as scheduled task

[xo55ox]

After setting up the linked server connection at the standby server, I
tried to xcopy a file through the sql server 2000 schedule task to the
standby server's shared directory. But it keeps giving me the error
message with
'Invalid Drive Specification'.

My whole process includes
1) set up linked server connection on the standby server
2) set up job to xcopy file as operating system commend in sql server
from the production box(xcopy c:\directory\fi le.bak
\\standby_serve r\e$\directory\ /c)
3) test, but not successful - I am already running the whole schedule
task as a Windows user with Admin authority.)

What did I do wrong or did I miss something?

Thanks in advance

[Erland Sommarskog]

xo55ox (xo****@hotmail .com) writes:

After setting up the linked server connection at the standby server, I
tried to xcopy a file through the sql server 2000 schedule task to the
standby server's shared directory. But it keeps giving me the error
message with
'Invalid Drive Specification'.

My whole process includes
1) set up linked server connection on the standby server
2) set up job to xcopy file as operating system commend in sql server
from the production box(xcopy c:\directory\fi le.bak
\\standby_serve r\e$\directory\ /c)
3) test, but not successful - I am already running the whole schedule
task as a Windows user with Admin authority.)

What did I do wrong or did I miss something?

Under which user is SQL Server Agent running as? It seems to me that it
is running under Local System, in which case it cannot see any other
machines on the network.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[xo55ox]

We actually set up a separated user to run both sql server 2000's and
sql server agent's services. This account is member of administrators
and users. Do I need to add any permissions or rights to it? And do I
have to set this up on both the production and standby servers?

Thanks for the information.

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

xo55ox (xo****@hotmail .com) writes:

After setting up the linked server connection at the standby server, I
tried to xcopy a file through the sql server 2000 schedule task to the
standby server's shared directory. But it keeps giving me the error
message with
'Invalid Drive Specification'.

My whole process includes
1) set up linked server connection on the standby server
2) set up job to xcopy file as operating system commend in sql server
from the production box(xcopy c:\directory\fi le.bak
\\standby_serve r\e$\directory\ /c)
3) test, but not successful - I am already running the whole schedule
task as a Windows user with Admin authority.)

What did I do wrong or did I miss something?

Under which user is SQL Server Agent running as? It seems to me that it
is running under Local System, in which case it cannot see any other
machines on the network.

[xo55ox]

I verified we actually set to use LOCAL SYSTEM account to run sql
server 2000 and sql server agent on production server and set a
separated account to run the standby server. That account in standby
server is member of Administrators and Users.

So, do I need to add more permissions or rights for the accounts? Or
do I need to change the production account to something else with the
specific authority?

Thanks for the information.

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

xo55ox (xo****@hotmail .com) writes:

After setting up the linked server connection at the standby server, I
tried to xcopy a file through the sql server 2000 schedule task to the
standby server's shared directory. But it keeps giving me the error
message with
'Invalid Drive Specification'.

My whole process includes
1) set up linked server connection on the standby server
2) set up job to xcopy file as operating system commend in sql server
from the production box(xcopy c:\directory\fi le.bak
\\standby_serve r\e$\directory\ /c)
3) test, but not successful - I am already running the whole schedule
task as a Windows user with Admin authority.)

What did I do wrong or did I miss something?

Under which user is SQL Server Agent running as? It seems to me that it
is running under Local System, in which case it cannot see any other
machines on the network.

[Erland Sommarskog]

xo55ox (xo****@hotmail .com) writes:

I verified we actually set to use LOCAL SYSTEM account to run sql
server 2000 and sql server agent on production server and set a
separated account to run the standby server. That account in standby
server is member of Administrators and Users.

So, do I need to add more permissions or rights for the accounts? Or
do I need to change the production account to something else with the
specific authority?

Yes, as I said, you cannot have Local System if you need to access
network drives, since Local System does not see network things. The
best is probably to change to the same users as on the standy server.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[xo55ox]

We did set up a separated account as the standby server for the
production box. But my job is still failing. This time, in my log, it
reported
'Logon failure: unknown user name or bad password'.

I tried to use the sql server user account as well as the OS user
account to run the job. Both are failing.

Am I missing something on the user account configuration, on the
linked server configuration or something else?

Thanks.

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

xo55ox (xo****@hotmail .com) writes:

I verified we actually set to use LOCAL SYSTEM account to run sql
server 2000 and sql server agent on production server and set a
separated account to run the standby server. That account in standby
server is member of Administrators and Users.

So, do I need to add more permissions or rights for the accounts? Or
do I need to change the production account to something else with the
specific authority?

Yes, as I said, you cannot have Local System if you need to access
network drives, since Local System does not see network things. The
best is probably to change to the same users as on the standy server.

[Erland Sommarskog]

xo55ox (xo****@hotmail .com) writes:

We did set up a separated account as the standby server for the
production box. But my job is still failing. This time, in my log, it
reported
'Logon failure: unknown user name or bad password'.

I tried to use the sql server user account as well as the OS user
account to run the job. Both are failing.

Am I missing something on the user account configuration, on the
linked server configuration or something else?

I have to admit that I don't have full knowledge how this works, and I
don't really have good playground to test things on myself. But I try to
work from common sense.

1) You never said as what kind of job step you ran the xcopy command.
I assume that you use CmdExec and not a T-SQL job?

2) First of all the service account for SQL Server Agent must have
access to that other partition. I don't really know whether know
whether the job owner has any importance for CmdExec, but if the
service account does not have access to the disk itself, it should
not be able to get there through impersonation either, methinks.

3) Rather than referring to c$, set up a real sbare, and grant permissions
to that share to the user(s) involved.

This may be more of a Windows networking issue than an SQL issue.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp