executing xp_cmdshell in remote server

Question

RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1  

declare @cmdstring varchar(1000)

set @cmdstring = 'copy D:\\Mine\\Mine\\Icons\\1355312509_gadu.ico D:\\Mine\\Mine\\1355312509_gadu.ico'
exec master..xp_cmdshell @cmdstring 

RECONFIGURE
EXEC sp_configure 'xp_cmdshell',0

I am trying to execute this on remote server, I am logged in as sysadmin though I am not able to execute, this is the error I get, I have been through earlier post but couldn't find the proper solution

output
The device is not ready.
NULL

Any help would be apreciated

Won't this code

RECONFIGURE
    EXEC sp_configure 'xp_cmdshell',0 

at the end of code eliminate those security threats??

This is what I have done and its okay

CREATE TABLE #temp
(
    id               INT IDENTITY(1, 1),
    name_file        VARCHAR(500),
    depth_tree       VARCHAR(10),
    is_folder_files  VARCHAR(10)
)

/* xp_dirtree selects file from specific location
*  depth_tree       :   depth of the search i.e. subfolders
*  is_folder_files  :   selects folders only or files too
*/

INSERT INTO #temp(name_file, depth_tree, is_folder_files) EXEC xp_dirtree @source_path, 0, 1

-- Must concatenate to have permission for xp_cmdshell
SET @concatenate_string = 'RECONFIGURE EXEC sp_configure ''xp_cmdshell'',1 EXEC MASTER..xp_cmdshell '


-- Generating copy string in bulk
SELECT @cmd_string =    
    ISNULL(@cmd_string, '') + 
    CASE WHEN (LEN(REPLACE(t.name_file, @seperate_value, 1)) <> LEN(t.name_file)) -- if @seperate_value is not in image
        THEN
        (
            SELECT CASE 
                WHEN REPLACE(t.name_file, 'Approach', 1) <> t.name_file OR REPLACE(t.name_file, 'CloseUp', 1) <> t.name_file -- if word Approach or CloseUp is not there in image 
                THEN
                    (
                    SELECT CASE
                    WHEN ((SELECT f.FaceID FROM Face f WHERE CAST(f.Notes AS VARCHAR) = SUBSTRING(t.name_file, 0, CHARINDEX(@seperate_value, t.name_file)-1)) IS NOT NULL) -- when extracted ID from image <> NotesID
                    THEN
                    (
                    @concatenate_string + '''copy ' + @source_path + t.name_file + ' ' 
                    + @destination_path 
                    + (SELECT f.FaceID FROM Face f WHERE CAST(f.Notes AS VARCHAR) = SUBSTRING(t.name_file, 0, CHARINDEX(@seperate_value, t.name_file)-1)) -- Compares and gives the faceID
                    + (SELECT   CASE 
                                    WHEN REPLACE(t.name_file, 'Approach', 1) <> t.name_file THEN '-AS.jpg'' '
                                    WHEN REPLACE(t.name_file, 'CloseUp', 1) <> t.name_file THEN '-BS.jpg'' '
                                    ELSE
                                        'Undefined'
                                END
                       )
                    )
                    ELSE
                        ' '
                    END
                )
                ELSE
                    ' '
                END
        )    
        ELSE
            ' '
    END

FROM #temp t

SELECT @cmd_string + 'RECONFIGURE EXEC sp_configure ''xp_cmdshell'',0'

EXEC (@cmd_string)

Answer 1

Aside from using T-SQL to do anything outside of SQL Server, which has its own inherited risks, you will need to firstly give the right permissions to the right account to even run xp_cmdshell.

You will need a local AD account, as the local "nt service\mssqlserver" account may not exist in your other remote server and so why will never get access to it.

However, before you get the Shell steps itself, do this:

USE MASTER;

-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
    RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1'
    RECONFIGURE

IF NOT EXISTS
     (SELECT loginname
      FROM   master.dbo.syslogins
      WHERE  name = 'yourdomain\anADaccount')
  BEGIN
    CREATE LOGIN [yourdomain\anADaccount] FROM WINDOWS;
    EXEC sp_xp_cmdshell_proxy_account 'yourdomain\anADaccount', 'accountpassword'
  END

--Create the database role and assign rights to the role
--select DATABASE_PRINCIPAL_ID('CmdShell_Executor')
IF DATABASE_PRINCIPAL_ID('CmdShell_Executor') IS NULL
  BEGIN
    CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]
    GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]
  END

IF NOT EXISTS
     (SELECT [name]
      FROM   [sys].[database_principals]
      WHERE  [name] = 'yourdomain\anADaccount')
  BEGIN
--Then once done create users and assign to CmdShell_Executor
    CREATE USER [yourdomain\anADaccount] FROM LOGIN [yourdomain\anADaccount];
    EXEC sp_addrolemember[CmdShell_Executor], [yourdomain\anADaccount];
  END

Now make sure your AD account also has permissions to the remote server. I strongly suggest you create these AD accounts on each <<local>> machine and not under the general domain's AD. You can then control the account to only have read access to a specific folder or location on your remote server.

Now you're ready to run the CMD shell from SQL Server, without the risk of any security holes that someone foreign may gain access to your database and run Shell commands!

EXEC    master..xp_cmdshell 'whoami.exe'  --find out what account you're actually using

At the end of your procedure, make sure you remove all those permissions!

EXEC sp_xp_cmdshell_proxy_account NULL
drop user [yourdomain\anADaccount] 
drop role [CmdShell_Executor]
drop login [yourdomain\anADaccount]

Answer 2

I have had this one some time too. I ran the script from server a, where things were on server b. I checked and the only thing (besides credentials, which looks it isn't an issue) was that the filestructure didn't exists on the remote server..

Answer 3

It's most likely an issue with the credentials the SQL Server Service is running under. The account might not have sufficient access to the folders you are trying to copy from or to.

One solution could be attempting to run the task from SQL Server Agent, see also this discussion on the MSDN forums.