November 6, 2013 at 8:26 am
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?
November 6, 2013 at 8:52 am
It doesn't matter where the DTS is saved, because the connections aren't changed to the prod server.
What are you afraid of?
November 6, 2013 at 10:55 am
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?
November 8, 2013 at 6:08 am
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
November 9, 2013 at 11:26 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 18, 2013 at 4:55 pm
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! 😉
November 18, 2013 at 5:08 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply