SQL Server Information Services is Microsoft's ETL tool. It provides a drag-and-drop interface for describing data flows from sources to sinks, complete with transformations and all sorts of other operations, and is useful for migrating data between databases, linking legacy mainframes into modern databases, or doing what most people seem to need: migrating data into Excel spreadsheets.

It's essentially a full-fledged scripting environment, with a focus on data-oriented operations. The various nodes you can drag-and-drop in are database connections, queries, transformations, file system operations, calls to stored procedures, and so on. It even lets you run .NET code inside of SSIS.

Which is why Lisa was so surprised that her predecessor had a "call stored procedure" node called "move file". And more than that, she was surprised that the stored procedure looked like this:

if (@doDelete = 1)
begin
    set @cmdText = 'mv -f ' + @pathName + @FileName + @FileExt + ' ' + @pathName + 'archive\' + @FileName + @FileExt + '.archive'
end
else
begin
    set @cmdText = 'cp -f ' + @pathName + @FileName + @FileExt + ' ' + @pathName + 'archive\' + @FileName + @FileExt + '.archive'
end

insert into #cmdOutput 
exec @cmdResult = master.dbo.xp_cmdshell @cmdText

This stored procedure was called from SSIS, which again, I want to stress, has the functionality to do this without calling a stored procedure. But this approach offers us a few unique "advantages".

First, it requires xp_cmdshell be enabled. This particular stored procedure is disabled by default, since it allows a user inside of SQL Server to invoke shell commands. Microsoft disables this by default, because it's a gaping security hole. Any security scanning tool you may run against your server will call it out as a big red flag. You're one SQL Injection attack away from an old rm -rf /.

Which, speaking of rm, you'll note the command strings they build to execute. mv and cp. Now, SQL Server can run on Linux, but this instance wasn't. No, the person responsible for this stored procedure also installed GNU Core Utils on Windows, just so they could have mv and cp to invoke from this stored procedure. Even better, they didn't document this dependency, so the first time someone tried to migrate the database to new hardware, this functionality broke and no one knew why.

At least the migration gave them a chance to update their SSIS packages to use the "File Transfer Task" instead of this stored procedure. But don't worry, there were plenty of other stored procedures using xp_cmdshell.

[Advertisement] Keep all your packages and Docker containers in one place, scan for vulnerabilities, and control who can access different feeds. ProGet installs in minutes and has a powerful free version with a lot of great features that you can upgrade when ready.Learn more.