*big sigh*
I'm trying to replicate a previously working process. These were the basic steps:
- SQL 2000 MSDE dumps multiple databases to a backup folder on the same server via a scheduled SQL job. *Works Perfectly*
- The last step in this job was xcopy D:\MSSQLBackups\*.bak \\backupserver\share /v /d /y
*Yes it was a step type of 'Operating system (CMDExec)'
-The xcopy never occurs and the job history shows:
" Executed as user: SERVERNAME\SYSTEM. Invalid drive specification 0 File(s) copied. Process Exit Code 4. The step failed."
-The owner of the job is a domain account which has proper privileges to the source and destination shares/servers.
I tried to 'step' through the process by using the xp_CMDShell command in a TSQL query window.
Query:
EXEC master..xp_CMDShell 'xcopy D:\mssqlbackups\*.bak \\BACKUPSERVER\SHARE /v /d /y'
Result:
Invalid drive specification
0 File(s) copied
I proceeded further and created a batch file with some DOS commands to see where the command was running from and to obtain other information
Query
EXEC master..xp_CMDShell 'D:\MSSQLBackups\CopyDB.ba
Batch file contents:
cd\
D:
net use Z: \\BACKUPSERVER\SHARE
Z:
xcopy D:\mssqlbackups\*.bak Z: /v /d /y
net use Z: /delete
Results:
C:\Windows\System32>cd\
C:\>D:
D:\>net use Z: \\cork\sqlback$
The command completed successfully.
D:\>Z:
Access is denied.
D:\>xcopy D:\mssqlbackups\*.bak Z: /v /d /y
Invalid drive specification
0 File(s) copied
D:\>net use Z: /delete
Z: was deleted successfully.
(20 row(s) affected)
How can I find out what account is being used to access this mapped drive?
-Stumped
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The service runs with local system account cannot work with network/mapped drives. You can try using the unc path instead, as you tried before.
The reason for failed accessing unc path may permission issue on the BACKUPSERVER's shared folder. Check that out.
Also, try to change the MSSQL Server service's logon user account to other than system account and try that out.
John Nash.
Hello, Thanks for both your replies!
The SQLSERVERAGENT Service is running as a domain user that has rights to the BACKUPSERVER's share.
I even tried a domain admins account information to use for the SQLSERVERAGENT service and it failed.
If I open the BACKUPSERVER's share to 'everyone' the job works..
If I open the BACKUPSERVER's share to 'authenticated users' the job works..
How can I find out what account is being used here to access this share?
Hi FNDAdmin,
You could look at some auditing on the back up server, if you do this though you will want to find a "quiet time" when there is little else going on on the server.
check out: http://technet.microsoft.c
You should be able to include failed access attempts to, do remember to switch off auding afterwards as well!
To identify the user name who runs your batch script, just include the following lines in your batch script:
------------8<------------
echo User name=%USERNAME% >> logfile.txt
echo User domain=%USERDOMAIN% >> logfile.txt
------------->8-----------
If the user is other than system user, your script should work.
Otherwise, try UNC path.
John Nash
Business Accounts
Answer for Membership
by: mcmonapPosted on 2009-11-04 at 13:29:14ID: 25744145
Hi FNDAdmin,
If I remember correctly SQL2K jobs will run under the service account of the sqlserveragent account - does this account have the necessary permissions?