It is 2009, and I am shocked to find that applications today still show up with a requirement to be a sysadmin on SQL Server, and in the case of one particular application I am helping troubleshoot today, a Local Administrator on the SQL Server machine. As an application developer myself, I understand the due diligence required to properly design an application to follow proper security best practices, and it isn't hard to do if you do it up front. Since I am not one to roll over on security I am willing to spend the hours/days to figure out the appropriate rights necessary for an application to work in SQL without being a sysadmin, but I don't know many people who can/will do that much leg work.
So why does the app need to be a sysadmin in the first place? All to often it is just because that is how it was written, and it makes it easiest. Often there is absolutely no need to be a sysadmin. SecurityAdmin and DBCreator roles are initially needed to do the install so that the login can create the database for the app and setup security properly for it. After this, DBO rights to the database is all that it actually needs, and you can drop the server level roles from the login.
Another place I see that apps need elevated rights is where the application has builtin administrative functions. One app I have seen has a console that will allow the user to change max server memory for SQL Server from the application. Thank you no, that is my job, and I don't want/need some application admin playing with my server configuration. Why do application developers think that this kind of thing is a good idea? We already have applications to manage SQL Server with. I think that this is one of the reasons that the quality of applications that are out there has decreased in recent years. Rather than create a focused application that solves one problem, it seems like application vendors want to do it all, which generally only means one thing, they won't do any of it very good.
So that leaves a question for the community of readers: What do you do with applications like this? I know in past discussions on SQL Server Central that some people use virtualized SQL Servers to solve this problem, and isolate the database on its own VM, but is that really a solution? Now you get server sprawl and added licensing costs because the application, for lack of a better word, sucks?
Great rant....
ReplyDeleteOnce you're in bed with an application like this, you're right - there is precious little that can be done. The only thing that will stop application vendors from doing this kind of thing is if more companies make security due dilligence a criteria in software selection. If the vendor gets met with "we like your product, but it doesn't conform to our security policy" more than once, you can be sure that application changes will be soon to follow. Once you've purchased the app you no longer have any leverage though, so you'll be lucky to even get the vendor's attention at that point.
In my experience, most software companies are (unfortunately) sales-driven, so something that is a condition of a sale is far more likely to get air time than a gripe (no matter how valid) from an existing client.
Amen!
ReplyDeleteNo, isolating it on it's own VM is not a solution for these reasons:
1.)A DBA will still be stuck with the administration of this and will still have all of the same issues (contention, who did what, too many folks with elevated permissions)
2.) The root cause is not being dealt with, no "lessons" have occurred for the development team and management of said team.
3.)You are going against your best practices to accommodate a poor security design.
4.) The reasons you indicate.
The other solution that hardly ever works is the "Later" approach. You can't just say fine.. push it forward it's a rush job and we'll fix it later. Later never comes that way. You get far too busy on other fires and then one day you are being audited and you have some serious findings because you never cleaned that up.
The way I would solve that is: Slow down the process. Vehemently reject to push it forward and politely but firmly indicate why.
Explain the security holes, the performance issues it can create, the reliability issues it can create and how it will disturb the middle east peace process at the same time.
Work with them to work through what they absolutely need, how you can provide workarounds and why this is not acceptable.
If at the end of the day you do all that and management overrides, well then it is what it is. Document your concerns, and convince management/project management to put a non-negotiable item in a plan and calendar to remediate the situation. Then stick to making sure it is fixed. In the meantime, isolate them as best as you can (VM/Hyper-V/Seperate small server, etc).
My bad. I didn't even realize you were working on an ISV created app. Well for those, I actually try and head them off at the pass.
ReplyDeleteI have a set of questions I ask and force myself to get invited to meetings with sales engineers/technical sales folks and ask the questions.
If there are serious concerns I talk about our options (Pick a different vendor, ask for a change before we buy or increase the budget and get a separate server)
I actually found out something scary that way.. I ask how authentication works as one of the questions. A building security company (folks that provide the software for door access swipe cards) replied "we use the SA account" To which I said "what?! How do you encrypt the password???" They replied: "We just use blank password". I was flabbergasted and said something semi-sarcastic back like "And you guys are in the security business?!".. We went with them but not as they wanted to..
Mike,
ReplyDeleteYeah it is a ISV not a homemade app. I have encountered plenty of those that are done exactly like this though. Generally speaking, I am not a part of the up front discussions for application purchases, and even then, it is almost impossible to get straight answers from a sales person on this. They don't know what good practice is, nor do they care from what I have seen.
nope. I could write a book about the conversation I have had.
ReplyDeleteI make them put an engineer on though and talk about detailed level information. Generally they comply and we get to the bottom of it.
I used to do it a lot more when I was at an Oracle/SQL shop and we would have both myself and an Oracle DBA in on the call so we could determine best location for the app (if it supported both) and could make sure it fit with our best practices.