SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Be on the lookout for the mistakes of others

I've come to learn that there are some really flaky systems out there.  Not just a few quirks here and there, but significant problems that someone should have caught.

For example, I spent some time earlier this week consolidating two SQL Server machines into one.  Through this process, I had to evaluate the vendor app that interfaced with one of these databases.  In examining the configuration, I found that every user of the system was connecting through a static SQL user account - not a good idea, considering the merits of using Windows authentication, but not a critical problem.  However, what was a dealbreaker was that this common account used by all had been given db_owner priveliges.  Furthermore, the user ID and password of this db_owner account were stored in a configuration file on each machine!

The most appalling part of this was my call to the vendor.  I explained what I had found, and the account rep insisted that it had to be configured this way for the application to work properly.  More frightening, he didn't consider this setup a problem at all.  Despite this advice, I ended up doing some experimentation using Windows authentication and a diminished level of access.  Contrary to what the account rep told me, this setup worked just fine.

Now what I'm wondering is, at what step in the development project did it seem to be a good idea to allow anyone db_owner rights - and to put that login data directly on the client machine?  I will admit to taking some shortcuts during system development, but generally during the proof-of-concept phase.  How such a significant oversight like this could get past developers, DBAs and QA is beyond me.

Now had there been a compromise, I'm sure the software vendor would have been questioned about it, but we on the front lines would have borne the bulk of the mess.  The point is, we must always be alert to poorly designed systems - even if it's someone else's poor design.

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and Microsoft Data Platform MVP with over thirteen years of data management experience. He is the founder and principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.


Posted by Carolyn on 16 June 2006
I've been there got the t-shirt, though it's usually been the sa username and either a known password or a 2 digit password, and it doesn't take a brain surgeon to work out what the 2 digits were. Even worse than db_owner.

It's been on both third party software and on internally written stuff which I've discovered when starting in a new company.

Try doing a emergency restore on a database as sa when all the users are still trying to log in as sa!!! It's not fun Single User mode doesn't work. Try working out who's causing all the deadlocks!!

It's on my list as a contractor to inform management when I see this that security is one of the things I can't be responsible for, that usually gets things sorted.
Posted by Joan on 19 June 2006
The challenge is once you discover the flaw how do you get them to change. As a DBA I have been faced with this exact configuration and have been trying to convince development that this is wrong, but they will not listen. They spend more time trying to show flaws in other suggestions rather than work together to implement a viable solution.
Posted by M on 19 June 2006
Try MS Sharepoint. If it is not the db_owner (not just a member!), it won't run. We traced it, and the app makes a call to the server, and if the answer is not dbo, it won't play.
Posted by Mudluck on 19 June 2006
There is and for the unforeseeable future always going to be a between a gap between developers, Project managers, Software testers and the DBA. The reason for this is priorities and perspective. The DBA sees the environment as a holistic machine with many parts that need to work in harmony to create Speed of response and Security among other things. The developer sees the database as a place to dump things to and retrieve things from. All a developer cares about in the end is getting his project to do those two things and any security permissions bug is 9 times out of 10 going to end up as the solution being granting the user dbo, or date_reader/ date_writer because these are easy answers. This is compounded by the fact that often in DEV and CERT the developer and software QA are given DBO rights to do there work and many developers are just to lazy to keep track of what table needs which rights. So the implied permissions of DBO and the like are just an easy answer. Luckily for me HIPPA has come about and the last two audits we have gone through have uncovered allot of this blanket access and has forced developers to refractor there apps, though as I have also discovered a DBA must be patient as nothing happens overnight.
The answer for any company is to but someone(s) in charge as a fulltime DBA. Allow that person the opportunity to raise the flag and reject releases that do not comply. It’s amazing how fast standards get enforced when a few key rollouts get held up. If the DBA can feel that his voice is being heard and he/she can train the developer in a better practices for security then you will see quality improve over time. Quality takes effort as great databases aren’t just created overnight.

If its 3rd party you have even less of a chance of changing the code. My recommendation would be one of the following.

1. Look at other vendors and hopefully there is someone else that has better code.
2. Submit each and every bug you find to the vendor and threaten to bail out of your service contract if nothing is done in a timely manner.
3. Write it yourself if you have the time, resources, and the money.
Posted by Regan on 16 February 2007
This is so true - I frequently have to trot out my oh no line... "I've seen this movie, and they all die in the end!!"

Until we (the IT industry) start forcing ourselves to consider security from the beginning of the design lifecycle, this problem will hang around. We all know that when we hit the last 1/3rd of a project, someone saying that "this is a problem, but not a bug" just doesn't get any traction. And I'm not talking about having to design for security (naturally that as well), I'm talking about:
1. Least-Rights-Required principle:
-making sure your developers DO NOT DEVELOP with elevated permissions.
-They DO NOT develop with SA.
-They DO NOT develop with DB_OWNER.
-They DO determine, at design phase, what users will be used, and what rights they require.
-They DO use those users in development, so that it gets developed correctly from dev, and we don't run into "insufficient permission" in the TEST environment, where the developer throws up his hands, says - "it works fine in dev - it must be connfiguration/environmental issues".

2. work in similarly configured environments:
-You (DBA) shall ensure that servers in DEV are configured the same as TEST, QA (hopefully you have this environment) and LIVE. That means at least:
-- authentication modes,
-- code pages,
-- users and permissions and roles

- ideally this means SQL versions, and installtions (clusters etc.) but we all know that many organizations can't afford 4 clustered, Ent Edition systems for a system. But at the very least, make sure that you have the same kind of layout - databases are on the same server's etc. I had the "joy" of working in an environemnt where the single DEV DB 'server' had all the organizations databases while TEST had a few servers with databases on the different DB servers. Eventually I managed to get instances installed on the Dev DB server, moved all the DB's onto instances that represented servers in the other environments, and after a while of complaining and struggling, people began to (a) understand the live environments better, (b) design solutions that worked outside dev :-)
Leave a Comment

Please register or log in to leave a comment.