SSIS Security concerning the File System, Execute Process, and Script Tasks

Question

SSIS Security concerning the File System, Execute Process, and Script Tasks

Jeff Moden

SSC Guru

Points: 1004290
More actions
May 14, 2017 at 2:57 pm

#330237
I don't work with SSIS, so please pardon my extreme ignorance. My Yabingoolehoo isn't so hot on this particular subject. :blush:
I was reading the following recent article...
http://www.sqlservercentral.com/articles/SSIS/155160/
and realized I knew very little of the security behind SSIS, especially at the Task level.
To summarize the article, it uses the following 4 methods to demonstrate performance differences of simply copying files from one directory to another.
1. Using the File System task
2. Using the command line (cmd) with the Execute Process Task
3. Using Powershell with the Execute Process Task
4. Using C# with the Script Task
Here are the questions that came to my mind.
1. Which login is used to execute the task?
2. Is there anything that traces what was done to the file system (using any of the methods) to the actual human that ran the SSIS package?
3. A bit on the far side, is there any way to prevent the Execute Process Task from anyone using it to get to the Cmd Line or Powershell? Is there any way to disable the Script Task?
4. And finally, how do you prevent an attacker that managed to get in as a "high prived user" from using any of these methods to further their attack? (Yes, it is a bit of a trick question... that's why I'm asking to be sure.).
If there's a good link on the subject, I'd be very interested in that, as well.
Thanks for the help folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
December 21, 2017 at 4:54 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

Jeff Moden - Sunday, May 14, 2017 2:57 PM
1. Which login is used to execute the task?

Depends how it was executed. From SSMS then it will use the credentials of the user connected, if scheduled as a SQL Agent job it will use either the SQL Agent service account or a proxy depending on how the job is configured.

Jeff Moden - Sunday, May 14, 2017 2:57 PM

2. Is there anything that traces what was done to the file system (using any of the methods) to the actual human that ran the SSIS package?

Assuming the package is deployed you can view the execution reports on the SSISDB database. These will tell you the caller and what the steps were performed.

Jeff Moden - Sunday, May 14, 2017 2:57 PM

3. A bit on the far side, is there any way to prevent the Execute Process Task from anyone using it to get to the Cmd Line or Powershell? Is there any way to disable the Script Task?

4. And finally, how do you prevent an attacker that managed to get in as a "high prived user" from using any of these methods to further their attack? (Yes, it is a bit of a trick question... that's why I'm asking to be sure.).

It's all down to rights. SSIS isn't a way for a user to do things they don't have rights to do. Can the user launch a cmd window and execute a specific command outside of SSIS? Then they can do the same in SSIS.
This does assume that the SQL Agent Account has the least privileges and access to the proxy accounts is controlled. If the SQL agent account is running as local admin or even domain admin then it is possible for a user to use this to inflate their rights (assuming they have privileges to deploy the package and create a SQL agent job)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

Awesome. Many thanks for the help.

Your answer on question 2 brings up one additional question...

2. Is there anything that traces what was done to the file system (using any of the methods) to the actual human that ran the SSIS package?
Assuming the package is deployed you can view the execution reports on the SSISDB database. These will tell you the caller and what the steps were performed.

Does any such logging occur during development and testing prior to package deployment? In other words, if someone broke in as a high privs user or service login, could they execute Cmd Line or Powershell scripts using the Execute Process Task (or whatever) without it being logged?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 3

Jeff Moden - Monday, May 15, 2017 7:46 AM

Does any such logging occur during development and testing prior to package deployment? In other words, if someone broke in as a high privs user or service login, could they execute Cmd Line or Powershell scripts using the Execute Process Task (or whatever) without it being logged?

In answer to your question yes it would be possible to execute a SSIS package without the execution being logged. be it through Visual Studio or DTexec. In order to run cmd or powershell on that server they need to execute the package directly on that server, if they have direct access they could execute the same cmd or powershell without using an ssis package.