Executing exe file in xp_cmdshell doesn't work

Question

I have created a user in SQL Server with:

CREATE LOGIN adminuser WITH PASSWORD = 'ABCDegf123';
GO

EXEC master..sp_addsrvrolemember @loginame = N'adminuser', @rolename = N'sysadmin'
GO

Then I connected with this user.

What I want to do, is trigger this ftp command:

ftp -s:C:\Users\sss\Documents\script_ftp\ftp.txt

So I created a trigger:

CREATE TRIGGER sss14
ON Table_1
AFTER INSERT
AS 
    DECLARE @CMDSQL VARCHAR(1000)
    SET @CMDSQL = 'ftp -s:C:\Users\sss\Documents\script_ftp\ftp.txt'
    EXEC master..xp_cmdshell @CMDSQL

However, this doesn't work.

Note that creating a folder with xp_cmdshell works correctly. and when I run 'code' it's launch visual studio code as background process

i'm new in ms sql, could u tell me when i can find the error, — dzjs, Jul 31 '18 at 09:05
it's tells me that the execution of the cmd succeeded, but if i launch visual studio code for example: SET @CMDSQL = 'code' it doesn't start — dzjs, Jul 31 '18 at 09:10
correction when i run 'code' it's launch visual studio code as background process, — dzjs, Jul 31 '18 at 09:40
Using `xp_cmdshell` in a trigger to do FTP is a really bad idea -- hard to troubleshoot, hard to maintain, terrible for performance, doesn't play nice with concurrency. Consider doing this from client code. Use the trigger to write an entry in a Service Broker queue, and a separate application or service that reads requests from that queue and invokes FTP as necessary. Or use a job, or a scheduled task. — Jeroen Mostert, Jul 31 '18 at 09:49
anyone know why i'm executing EXEC master.dbo.xp_cmdshell 'dir C:\Users\sss\Documents\script_ftp' it returns access deniedin the first Row and Null in the second — dzjs, Aug 01 '18 at 07:33

Som DT · Answer 1 · 2018-07-31T10:48:58.603

1

When xp_cmdshell is called, SQL Server does not care about the output and does not consider any failure. However, you can insert the output to temp table and see the result. Please run below in SSMS to check.

DECLARE @CMDSQL VARCHAR(1000)
    SET @CMDSQL = 'ftp -s:C:\Users\sss\Documents\script_ftp\ftp.txt'
    create table #output(output varchar(2000)) 

    insert into  #output
    Exec master..xp_cmdshell @CMDSQL

    select * from #output 
    drop table #output

The command run with xp_cmdshell use SQL Server service account to run OS commands. Please check if SQL Server service account has got permission to perform ftp. If not, grant the appropriate permission.

edited Jul 31 '18 at 10:48

answered Jul 31 '18 at 09:50

Som DT

11
3

thnks, for the response i'm using SSMS, how can i see the table output? – dzjs Jul 31 '18 at 09:58
Returning data from a trigger in a `SELECT` is deprecated. Although this works now it won't in a future version of SQL Server. – Thom A Jul 31 '18 at 10:04
i'm using a new version of ms sql, it might be the reason why i don t see any result – dzjs Jul 31 '18 at 10:16
It's still deprecated at this time: [Deprecated Database Engine Features in SQL Server 2017](https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017) – Thom A Jul 31 '18 at 10:27
I modified the answer. Please run in SSMS with the same login to understand thre result. – Som DT Jul 31 '18 at 10:49
anyone know why i'm executing EXEC master.dbo.xp_cmdshell 'dir C:\Users\sss\Documents\script_ftp' it returns access deniedin the first Row and Null in the second – dzjs Aug 01 '18 at 07:33

Executing exe file in xp_cmdshell doesn't work

1 Answers1