xp_cmdshell Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

xp_cmdshell Problems

Author

Topic

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-19 : 12:15:12

Hi All,

I am trying to write a stored procedure that copies a database Backup File, and Transaction log File, from one server to another. I am generating the correct Syntax for the XCOPY command, and when I run the generated command (cut and paste from Job History output) The file is copied. Running the Stored Procedure From Query Analyser Or SQL Agent Job Service reports success but fails to copy the file.

I am a member of Sysadmins on the SQL Server, The SQL Server And Agent Service accounts are domain admin accounts. I have run the code on the Server directly. Permissions have been granted on the destination directory to the service accounts and I have also added the service account to xp_sqagent_proxy_account as well.

If I change the code to do a DIR then I get an Access denied on the destination directory . . . I can't see why as the all the accounts have full control on the destination directory.

What Have I missed?

(Oh and It is going in master Hence the sp Prefix


CREATE PROCEDURE dbo.sp_CopyTranLogFile ( @Time DateTime 
                                       , @BackupDirectory Varchar(255) 
                                       , @BackupFileName  Varchar(255) 
                                       , @DestinationDir  Varchar(255) ) AS
BEGIN
  
  DECLARE @ProcName varchar(32)
  DECLARE @UserId varchar(32)
  Declare @BackupFile Varchar(255)
        , @Cmd Varchar(1024)
        , @Result Integer

  SET NOCOUNT ON

  SELECT @ProcName = 'sp_CopyTranLogFile'
  IF @UserId = Null SELECT @UserId = SUSER_SNAME ( SUSER_SID ( ) )

  -- The Backup File Has the format Of FileRoot + Year + Month + Day + Hour + Min Of Backup

  SELECT @BackupFile = @BackupFileName     + Convert ( Varchar(4) , year  ( @Time ) )
                                           + Convert ( Varchar(2) , Month ( @Time ) )
                                           + Convert ( Varchar(2) , Day   ( @Time ) )
                     + RIGHT ( '00' + Convert ( Varchar(2) , DatePart (hh , @Time ) ) , 2 )
                     + '*.TRN'

--  Print 'Backup File Name : ' + @BackupFile

--  SELECT @Cmd = 'DIR ' + @BackupDirectory + @BackupFile
  SELECT @Cmd = 'XCOPY ' + @BackupDirectory + @BackupFile + ' ' + @DestinationDir

  Print @Cmd

  EXECUTE @Result = master.dbo.xp_cmdshell @Cmd , no_output

  IF @Result = 0 RETURN 0 ELSE RETURN 1

END /******* END OF PROCEDURE sp_CopyTranLogFile ***************/

--
Regards
Tony The DBA

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-19 : 12:37:32

Well first thing I would tell you is to leave master alone. Create another database for admin procedures....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-19 : 12:37:52

Your destination is a network folder and your sqlserver is not a domain account or does not have permissions to the folder.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

bakerjon
Posting Yak Master

145 Posts

Posted - 2005-12-19 : 17:45:24

Can you logon to the SQL Server as the service account using Remote Desktop and try to copy a file manually? This might give you more information, and it will at least take your SP out of the equation. You also might want to try moving the file somewhere locally in the SP so you are sure it is doing what you think it should.

Jon
-Like a kidney stone, this too shall pass.

http://www.sqljunkies.com/weblog/outerjoin

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-20 : 06:17:06

Thanks for the replies so far.
No inspiration has struck overnight so Back to Basics
I changed the SP so it does a simple DIR of the Source and
destination directories, I also made it print out the Commands
that were being executed and removed the no_output from the
xp_cmdshell statement. The test job I have set up I changed
so that all output was saved as a text file. When the job is run
then the output is as follows.


Job 'Test' : Step 1, 'Test Copy Job' : Began Executing 2005-12-20 11:03:33

Executing as User : BBCDOM1\sqlmanager [SQLSTATE 01000]
Backup File Name   : pericles_blive_db_2005122007*.BAK [SQLSTATE 01000]
Command To Execute : DIR F:\Pericles_BLIVE\pericles_blive_db_2005122007*.BAK [SQLSTATE 01000]
output
---------------------------------------------------------------------------------------------
 Volume in drive F is Anite Backup
 Volume Serial Number is E619-D5EA
(null)
 Directory of F:\Pericles_BLIVE
(null)
20/12/2005  07:00     1,380,616,704 PERICLES_blive_db_200512200700.BAK
               1 File(s)  1,380,616,704 bytes
               0 Dir(s)  56,461,262,848 bytes free
(null)
Command To Execute : DIR \\BBCSSSQLAPPS\RootD\Backup\Pericles\BLive\pericles_blive_db_2005121907*.BAK [SQLSTATE 01000]
output
------------------------------------------------------------------------------------------------------
Access is denied.
(null)

Now the user specified is a Domain Administrator, and IS a
SQL Administrator. I have Set up a Share on the remote server
RootD rather than use the default share D$, and Sqlmanager has
full control over that share and sub folders.

Logging on to the Source server as Sqlmanager using remote desktop
and trying to connect to the share using explorer works fine. And
I can manually copy the file from the Source to the destination
and delete it as well. If I copy the Command to be executed into a
CMD window they execute perfectly. It is only when I run this

sp_CopyBackupFile '20 Dec 2005 07:00:00' , 'F:\Pericles_BLIVE\' , 'pericles_blive_db_' , '\\BBCSSSQLAPPS\RootD\Backup\Pericles\BLive\'

Either from within QA (Logged in as Sqlmanager) or as the job in
SQLAgent (SqlManager is the service account) that I get the
'Access is denied' error on the second DIR Command. I was thinking
that the Password was incorrect for SQLManager in SQL agent but
if that was the case then none of the Admin / Backup jobs would run.

Really got me puzzled at the moment

--
Regards
Tony The DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-20 : 09:05:04

Is all this on the same domain?

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-20 : 10:05:45

Yes, All on same domain . . I was expecting trouble when I have to attempt it to our DMZ, but between servers within the domain???

--
Regards
Tony The DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-20 : 11:00:32

quote:
Originally posted by TonyTheDBA

Yes, All on same domain . . I was expecting trouble when I have to attempt it to our DMZ, but between servers within the domain???

--
Regards
Tony The DBA

What's it say for "select system_User"? and this "exec master..xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'"?

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-20 : 12:14:15

Run From Test Job On Source Server Via Remote Desktop

Select System_user As [System_User] Yields
System_User
BBCDOM1\sqlmanager

And The Echo Command doesn't appear to work properly as the Output is
%USERDOMAIN%\%USERNAME% (Both From the Job and QA)

Still I did open a CMD window and tried it and got
BBCDOM1\sqlmanager

But thats not a surprise as that is who I'm logged in as

ARRRGHHHHH! I hate these things, I'm on leave now for the Christmas Break, and I know that this is going to prey on my mind until I get back. Sad Individual that I am I will probably keep checking emails and access work through our portal anyway :(

Have a Happy Christmas All, and thanks for the help

--
Regards
Tony The DBA

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-20 : 13:12:41

quote:
Originally posted by TonyTheDBA

Run From Test Job On Source Server Via Remote Desktop

Select System_user As [System_User] Yields
System_User
BBCDOM1\sqlmanager

And The Echo Command doesn't appear to work properly as the Output is
%USERDOMAIN%\%USERNAME% (Both From the Job and QA)

Still I did open a CMD window and tried it and got
BBCDOM1\sqlmanager

But thats not a surprise as that is who I'm logged in as

Have you tried using the $ share? Can you access other $ shares or unc path's from xp_cmdshell?

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-21 : 06:15:32

Yep Tried D$ first, no such luck. Having had problems withthis in the past Set up a share with permissions and security set specifically to allow the service account access.

Anyway no more worrying now, it is somehting that I'll have to do manually, before taking the server out of commission (DR test, of course its not all that much of a test if I can't go directly to the lastest Backup and Tran Log dump! Still we can go back to the morning one which will be on tape).

--
Regards
Tony The DBA

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2005-12-28 : 03:55:11

Hi All,

Solved the problem this morning. I took the opportunity of the users being on Holiday, and being in work early to reboot the server. Code works fine now. The only thing I can think of is that although the Service account was changed and reported as being done so successfully in both SQL Server and SQL Agent that wasn't the account and SQL Server was still running as the Local System account, which would account for the problem.

Of course the thing that threw me was that SYSTEM_USER and all the other methods of reporting the account name were returning the correct domain account.

Thank you all for the input

--
Regards
Tony The DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources