January 3, 2008 at 2:26 pm
I'm sorry, I am not a SQL administrator, however I do write some SQL queries and link to SQL tables from other applications.
I just know that the sa account on the SQL server is a sacred thing.
I work with MAS500 consultants that are trying to implement their software on our SQL server. They just do whatever they want on our server and then ask me what the sa password is. I guess they needed to make a change in MAS500 and forgot what the password was. The accountant from my own company was pushing me to give it to the consultant to get the job done. After me getting pissy about why do they need it and what are they doing with it questions and answer session, i was forced to type it in and they moved on. I later brought up the fact that this needs to change. That they need to create another account and not use the sa account. The accountant looked at me like I was crazy and said what are we supposed to do?
How can I go about asking the MAS500 people to create another account to administer MAS500 and NOT USE the sa account? Since I don't know too much about it I need to get more information to either alert them on the importance of this or look the other way since they do not seem to care. I guess I'm alone in this battle, but I would like to be more educated about the compromises of people using and knowing the sa account password. Does anyone have any reading material for me? (The scarier the better)
Thanks in advance. 🙂
January 3, 2008 at 2:42 pm
Just create an account for them to use and insist they use it. Better yet, insist that they use a Windows account for their maintenance.
Don’t bother trying to talk them out of it. Tell them that they have to prove to it is completely impossible to do maintenance without the SA account, and that they are completely incompetent if they cannot do maintenance without the SA account.
Don’t give in. Let out your inner SOB.
January 3, 2008 at 2:45 pm
- most applications actualy do NOT need sa, nor sysadmin privileges to run the application itself.
- most software installers do not know this, or are to lazy to alter the inputparameterfile.
- We don't give away sa passwords. In the worst case, we create an account with sysadmin privileges, that will be removed afterward.
- the same goes for application service accounts. No need to be member of local admins, nor network admins, nor sqlserver-sysadmins.
- the ones declaring you a alian if you don't want to provide sa or have the application run with sa auth most of the time just want to sell the software. Don't expect anything more than that from them.
- In more than one case I've supported installs and searched for .ini or .xml files and had them modified with propre useraccounts (with at most db group privileges (db_owner / database owner)
- yes, setting it up may take more time, but it pays off because of restricted privileges, closing some open doors and distribution of authority.
btw the same goes for some vendors trying to implement with simple recovery mode without a good reason.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 3, 2008 at 4:10 pm
What ALZDBA said - here! here!
Some software vendors are lazy and write app to use sa as then they don't have to worry about permissions, if you have a relationship with them you should complain about this.
Install and upgrade times are a bit different and a higher level of permissions will possibly be required by the app. Their install\upgrade scripts may have sa hardcoded all over them, in which case you may have no choice but to give them the password - but be grumpy about it and change it immediately afterwards.
If they just need a powerful id set them up another one. You can even cheat and give it less rights then they say they need, its amazing how often dbowner rights on their database is good enough. Find out what they actually want to do and set rights accordingly, and if the app uses the same id in normal use reduce its rights after the install/upgrade as necessary. No app should need more than dbowner rights.
How much you should worry about this also depends what else is on the server, if other non-related databases the more concerned you should be and the better your case.
Talk to the consultant, most are human and are happy if you are constructive and can offer them solutions, a lot have little DBA skills and are not even aware there are other options than sysadmin access. If he is an a*****e then remember you are a representative of his client and he needs you more than you need him, let the powers that be know of the flaws in the app they are paying for.
If you feel really strongly involve your security team if you have one, but please don't risk your career over what is a common issue with third party apps! Just do your job and be seen to be doing it by protecting your data and highlighting these issues.
---------------------------------------------------------------------
January 8, 2008 at 11:59 am
Thanks all for the replies. It has been about a week now. I have found that the company aSa software (an addon to MAS500) has created some custom solutions using the sa account. The users that are now using the aSa software are now asking me for the sa password repeatedly to continue onto the next screen to make changes inside of the aSa software (i.e. setting up GL accounts, etc.).
The users!!! So it's getting worse.
George, we are not that big of a company yet thus we do not have a security team, unfortunately. The MAS500 and aSa consultants were using the sa account for exactly the reason you had stated: to install and upgrade their application, however to have the user also need it to continue inside of the app is a different story. Good advice on the politics!
ALZDBA, great advice. I'm going to actually mention the inputparameterfile to the consultants and try to talk to them about creating an alternate account for them to use. We did have an incident where we had to restore the database and reinstall the application due to version conflicts between MAS500 and aSa. The consultant who performed the restore asked me for the sa password, I have been typing it in for them...
Michael, they already think im a SOB! haha! Actually they all think I'm overreacting. Even some of the people at my own company who have to do work in the applications -- think I am overreacting. I have not raised my voice yet or cussed out anyone but I'm sure people can see in my face that I'm bothered by this issue.
Thanks again all. Next post hopefully will be a victorious one.
January 8, 2008 at 1:11 pm
Someone needs to write a good model for ISVs (Independent Software Vendors) and in-house developers to follow when building applications that fits into the standards for managing the databases security. They need to know how to not use SA in SQL Server or SYS and System in Oracle. Most ISVs don't get it. They usualy have the product mostly built before a DBA is brought on board, usually it's not even a DBA more like a database developer or an app developer that knows a little SQL. In a lot of cases a DBA is not brought in until there are customer issues that nobody can deal with. I've spent the last 15 years working for ISVs and moving away from SA and SYSTEM is usualy the first thing I advocate for when starting the company. In most cases the customers had been screaming for it, but nobody new how to get away from using SA. Usually these companies have 10 to 15 years of legacy code and are only focused on the new business problems/features they are building into their next release. Then there is the issue of how do they get from where they at to what they should be doing? Agains, someone needs to put something together to guide ISVs on how to move toward a better security model for the production shop, not for their development shop. Yes, it's basically lack of knowlegde and lazyness.
Oracle advocates highly for not using SYS or SYSTEM for anything. It actually causes performance and other DB issues. This has helped somewhat, but then again, SQL Server ISVs that port to Oracle for one or two customers often default to SYSTEM for everything because that is what they did in SQL Server. In SQL Server everything was owned by dbo/SA until recently so I think things will start to shift over as older version of SQL Server are dropped from the supported version lists and vendors can then focus on the new features of the DBs.
Definitely scream and scream loud. It will not change unless we all make it change. Get your CIO or VP to scream loud too. Titles carry a long way when the customer's executive's talks to the ISV's executive. Also, remember to inform them that there is no way you could be their only customer with this same issue. We recently had a huge shift in priority and all of a sudden security got put on the schedule. I was shocked, until I found out our largest customer was going to be even larger and they said enough was enough. In this case the issue was not the DBs security, but the App Servers and Web Servers security. Yes, they have the same issues when dealing with ISVs.
Hang in there, the fight has just begun and unless we target all development shops at once, we will have to fight the battle one shop at a time.
January 8, 2008 at 1:22 pm
...Michael, they already think im a SOB! haha! Actually they all think I'm overreacting. Even some of the people at my own company who have to do work in the applications -- think I am overreacting. I have not raised my voice yet or cussed out anyone but I'm sure people can see in my face that I'm bothered by this issue...
A DBA should always keep the following in mind:
"Upon this a question arises: whether it be better to be loved than feared or feared than loved? It may be answered that one should wish to be both, but, because it is difficult to unite them in one person, is much safer to be feared than loved, when, of the two, either must be dispensed with. Because this is to be asserted in general of men, that they are ungrateful, fickle, false, cowardly, covetous, and as long as you succeed they are yours entirely; they will offer you their blood, property, life and children, as is said above, when the need is far distant; but when it approaches they turn against you. And that prince who, relying entirely on their promises, has neglected other precautions, is ruined; because friendships that are obtained by payments, and not by greatness or nobility of mind, may indeed be earned, but they are not secured, and in time of need cannot be relied upon; and men have less scruple in offending one who is beloved than one who is feared, for love is preserved by the link of obligation which, owing to the baseness of men, is broken at every opportunity for their advantage; but fear preserves you by a dread of punishment which never fails."
The Prince,
CHAPTER XVII Concerning Cruelty And Clemency, And Whether It Is Better To Be Loved Than Feared,
by Nicolo Machiavelli
January 9, 2008 at 1:44 pm
Hi
I feel for you; been there too and sometimes its been very painful.
I try to describe what SA is for; some people assume that SA is just a more powerful user, e.g. who can see more tables. I try to explain that SA is used to manage databases and servers not to use them. I talk about how I use it for Backups and Restores, creating DBs and dropping them. Resolving performance problems, etc. I point out that I, as the DBA, have 2 accounts; a user account and the SA account and that I don't use SA most of the time because I'm to good at my job to be that stupid.
I then try to explain that it gives full rights over all DBs in the instance, we have finance, assets, HR, 2*ERP all in the same instance so I always say to user X (say from finance) "are you comfortable with me giving out the SA password to ?" This will mean that Assets can get into your Finance data via the backend and we won't even have an audit trail let alone control" Once I get them to agree that they want their data secure I then point out the Asset dont want their data compromised by Finance either thank you!
Point out that 80% of breaches of corporate systems are by people legitimately doing their job and making a mistake. Do the business users want you to enable or otherwise a situation where they and others might do really bad stuff _by accident_
That said, because we to use shrink wrapped apps I have a couple of situations where I have had to grant SA to App user accounts. This always results in the user account being retained within the Data team. Thats is to say noone who is not trained in database admin gets to have access to an account with powerful rights. This has seen some functions move back to IT with is against our general policy but the users than put pressure on the vendors to resolve this.
Finally, if you cannot resolve this issue clearly document your concerns and send an email to your manage and the business owners of each system affected explaining, in nuetral language what the risk are and that the situation is against your best judgment but that you will abide by the instructions of management.
Regards
Karl
January 9, 2008 at 2:18 pm
Here is another suggestion...
Create a new account and give it dbo right on that database and tell them (users) that it works just as well as sa account. It will give the users all the right that they need to do what they need to do in that database as if sa would have the same right.
At the same time I would change the sa password and keep it in a safe place such as password safe.
If the user comes back and said they are unable to do their work, I would invest more time and run profiler on them so that I know exactly what they are trying to invoke that caused those errors.
Best of luck.
sopheap
January 10, 2008 at 1:23 am
When I get so called "engineers" come to install and need the sa password, I don't give it. Instead I create a new sql server account and give it sys admin rights. They then install from this and are told they must ruin from a low privilege account or windows accounts. Once they have gone I either remove the admin account or reduce its privileges to the minimum, sometimes by trial and error!
If you have network applications e.g. Mailbox Vault, Mimesweeper, Mailguard, LANdesk it pays to keep them on a separate server and make it the network team's responsibility - they're usually the ones who mess things up anyway 🙂
January 10, 2008 at 2:39 am
A silly question, perhaps, but if you're not a SQL Administrator, who in your company is?
Because that's the person who should be wading into this fight. You're absolutely right to be fiercely protective of the SA account, but if truth be told, if you're not the SQL Admin, you shouldn't have the SA password either.
Of course, if you ARE the de facto SQL Admin, then the comments already given should be pretty helpful. However, it may also be well worth spreading the pain to your boss by letting them know that, unless the SA account is resecured, you can't take responsibility for the security of the data in any of the databases on that server (and, if you're a small company with only one server, that may well mean the HR details are included).
Semper in excretia, suus solum profundum variat
January 10, 2008 at 4:37 am
You want scary documentation?
Google "SA Account" or "SA Account best practices". This should come up with plenty. Especially since so many worms & viruses use the SA account for malicious activity. Also, Google for data breaches. There have been so many lately that it's not even funny.
Make the point to your boss and (if possible) the owner of the company that once multiple people know the password, they could inadvertantly give it out to someone who doesn't need it. Or worse, they could delete a major stored procedure or drop a database / table or corrupt data by using access methods they don't need. Tell the owner you're trying to save him from a multi-million dollar lawsuit if his data gets stolen. Tell him that you're trying to save the money that he'll spend rebuilding everything if a bad install corrupts the db or if some power-user decides to change data types or something.
Hey, in fact here's a real-life scenario for you. Customer is using Float datatypes for all their financials. Do you know what happens if you change those Floats to Money or Decimal-2 precision? Someone loses a LOT of money very quickly. (Test it on a test db if you want proof for the boss).
And the accountants want the SA password? These are the people I'd be worried about since they probably don't understand the difference between the above datatypes. They'd go in to fix a problem, change one thing, and suddenly the boss is out several thousand dollars because of one simple, minor mistake that wasn't thought through.
I'm not saying this *will* happen with this MAS people, but you wanted scary, so here it is.
January 10, 2008 at 8:08 am
"Make the point to your boss and (if possible) the owner of the company that once multiple people know the password, they could inadvertantly give it out to someone who doesn't need it."
Almost certainly one of these folks will e-mail the password to someone in cleartext, probably to a distribution list. With any luck they won't include the domain\server\instance name in the e-mail, but I wouldn't count on it.
January 10, 2008 at 8:14 am
And once the email goes out, even if it's only to legit business users, it only takes one "blonde moment" to forward the email to someone outside of the business, forgetting to sanitize the contents first, and then you have a whole host of new problems.
January 10, 2008 at 8:16 am
"...then you have a whole host of new problems..."
Yeah, you are hosting a hacker's new attack site. Arrrrggggghhhhh!
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply