August 11, 2011 at 3:08 pm
Hi everyone,
i've been reading all the great info on the site for a long time but this is my first post!
and it's about a delicate subject, at least where i work.
The applications group have many COTS applications and many of them REQUIRE the SA to install.
Right now, i told them that if they need the SA someone from the DBA group will work with them to install the app and type in the password. I get looked at funny when i say this!
Do you have this situation where you work and how do you deal with the APPS people that want an SA account on DEV, Stage and PROD?
So far i've been able to hold my ground but it hasen't been easy.
Looking forward to hear your comments!
August 11, 2011 at 3:24 pm
This sort of thing is a problem with lots of third-party apps too. I found that, if you push back, it is sometimes possible to get more information about what the requirements actually are from the vendor. Sometimes a sysadmin might have to do the install, but the application can run under a db_owner of the application database. I'd continue asking the questions and would avoid actually using the sa login for installations, if possible. This was such a problem at my last job that we actually included the question on the questionaire that we used when evaluating software for purchase.
August 11, 2011 at 4:26 pm
In general the reason apps want SA is because they want to install or run maintenance scripting and use the sqlagent as well. This is great in that a vendor doesn't have to assume there's intelligent DBAs available to handle the app. This is horrible as any DBA worth his salt reels in horror at the idea.
What typically happens is the DBA is involved, as mentioned above, to do the installation and then the app itself has a limited rights set. HOWEVER, this can break support contracts with the vendor. They can always return to the pat answer 'you didn't install it like we told you to'. Needless to say, this can cause problems.
A lot of places I've worked at that handle a LOT of Vendor apps have decided that instead of contract negotiations and the rest of the rediculous, these apps are given private instances on private VMs. They don't get sa per-se, but a login with sa rights (for consistency of the sa password and lack of exposure of it). If sa is required, it's required, you give it a unique password from the rest of the entire server farm and live with it.
This does a few things.
1) If the vendor needs to come onto the server for 'support', it has ONLY exposure to a server and instance that is dedicated to its app.
2) Makes sure that you don't break support contracts.
3) Gives you an isolated location in case the vendor app acts like most do and thrashes like a drunken eel through your server.
4) Make your life a living hell when you're setting up the 400th linked server from your warehouse.
In general, it's going to come down to your support contracts as to how much hill you're going to have to stand on when you fight this fight. In the end, you're probably going to lose to the legal department when they review the contracts.
Good luck, I've actually pretty much given up on this one.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2011 at 3:58 pm
It is worth while showing the vendor why SA rights are plain dangerous. ie add a domain user to the admin group, stop a service, send a rogue email etc...
I did this to a vendor with amusing results. The young "consultant" said "its the way it has to be done...." where as the older more experienced consultant went white as a sheet and was very, very worried as he could see the liabilities of their actions. Having a quiet smoke and a coffee with the senior we discussed ways in which to lock the app down, approaches to how he could go to all of his existing clients 'pro-actively', and I ended up with a database app that met my security expectations.
The sledgehammer approach is useful if you want to break rocks, but is less useful when dealing with people and their egos 😉
August 23, 2011 at 9:51 am
We try to put all of the third-party app databases on a single server, mostly for cost savings on licenses and hardware. I do not give out the sa password to anyone. If that is absolutely required for the install of the system, I would get a separate database server for them (in our case, that would be a VM server, so easier to set up and no extra hardware needed). That means the business group that needs that app will have to pay separate SQL Server licenses and any hardware costs (cost of choosing the app that needs it). The upside is that the vendor can VPN into the box and have local admin rights, since it is only used for their app.
The main problem is security. The apps that are on the consolidated SQL Server have sensitive data. Therefore, security is the absolute decision maker. No one can see any database but their own, and can't have access to the box to see or have access to any backups, etc.
Normally, the app usually needs an app server. Therefore, we create a VM app server (usually a web server) and put Management Studio on it for the vendor to connect to their database (which they have db_owner rights to on the consolidated server). This gives them access to their database, but not to the administrative functionality, or to any of the other databases.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply