Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS Package SAVE AS - Possible security vulnerability? Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:52 PM
Points: 6, Visits: 136
I am posting this not only to point out what appears to be a security vulnerability but to hear opinions on how to resolve the issue.

The end result is this;
A developer working on physical server A can use the SAVE AS funtion to install a DTS package on physical server B although this developer has only connect and select rights to a single database on server B.

More detailed infrastructure overview:
1. Both physical servers are on the same domain.
2. Both servers are running only one (default) instance of SQL Server 2008.
3. Both servers use integrated Active Directory security.
4. User is not a member of any priveledged group.
5. When user connects to Prod server via SSMS, as expected all rights are limited, the user can not even view DTS packages.
6. If user builds a DTS package on the Test server and using the SAVE AS option, changes the server name to the Prod server, the package WILL BE created on the Prod server!

Thoughts?
Post #1511906
Posted Wednesday, November 6, 2013 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 3,784, Visits: 8,488
It doesn't matter where the DTS is saved, because the connections aren't changed to the prod server.
What are you afraid of?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1511914
Posted Wednesday, November 6, 2013 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:52 PM
Points: 6, Visits: 136
Actually, the package connections in this case, had already been changed to point to production, in preparation for deployment; but to answer your question, I'm not afraid as the ability to run the package in production is secured and I am not seeing any vulnerability at that level. My goal with this post was simply to validate through the community whether or not this issue should be considered a significant concern and secondly, to garner advice on how to prevent it, as I'm sure my auditors are going spot that the object creation date precedes the change request approval date and after reading my documented explanation, they are likely to ask... What did I do in order to prevent unauthorized users from installing DTS packages to production servers in the future?
Post #1511960
Posted Friday, November 8, 2013 6:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:14 AM
Points: 153, Visits: 980
Are aou rally talkin about DTS?

For SSIS that user needs to be in a certain (ssis-)role, to be allowed to store packages in msdb.
No special risk there afaik


Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Post #1512639
Posted Saturday, November 9, 2013 11:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:20 PM
Points: 807, Visits: 725
This is obviously a security issue, since this permits a user to replace an existing package that is scheduled to run with his own package which may do all sorts of evil things.

Now, you cannot accuse me to know too much about SSIS or DTS, but so much is clear, this is not security flaw in SQL Server as such, but a problem in your environment.

Since I don't know SSIS/DTS, I don't know exactly how the connection is made, but I would guess that there is a linked server set up. It sounds that this linked server has a login mapping so that this user maps to sa or somesuch on the prod server.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1512919
Posted Monday, November 18, 2013 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:52 PM
Points: 6, Visits: 136
Indeed, it is a DTS package; a legacy item from SQL2k that has yet to merit enough priority to migrate... maybe this issue will help to elevate that priority!
Post #1515383
Posted Monday, November 18, 2013 5:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:52 PM
Points: 6, Visits: 136
Thank you Erland for your input. We do use linked servers in our environment and this was something that I had not checked, but upon doing so, I found that there are no links between the production and development servers. I feel you are correct in thinking that this will likely be the result of incomplete or inaccurate passing of credentials between machines. I rechecked all rights that this user has on the production box, server and database and all showed only a standard membership in a Public Active Directory group with only connect and read rights. I am puzzled over this. I have dug around, but can find no specific documentation about exactly what actions are being performed when the DTS Package Designer executes a save as action. All security documents that I can find refer to the required permissions to create or alter a package. Because we are now aware and alert to the issue, we are talking over potential test scenarios to try to trap the failure, but will likely be a few weeks before we can do that kind of research. I will definitely post my findings here as discoveries are made and again, I appreciate your input.
Post #1515387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse