Voice of the DBA

Author: way0utwest

  • Networking at the PASS Summit: First timers

    The PASS Summit is just over a week away, and one again Andy Warren and I are hosting a networking dinner on Monday night. This is a free, informal event to help people get to know each other. Whether you are new to the community or have been to many events, you’re welcome to attend. We are targeting first timers, so if you’ve never been to the Summit, and don’t know what things you can do, if you don’t have other plans, come on by.

    We want to ensure that people are taking advantage of their time at the Summit, while encouraging them to do more than hang out with themselves or friends. Many of us have done that, but there are better things to do, like meet your colleagues.

    Andy and I will greet people and introduce some of you to others. Networking is easy, and this is a chance to meet some new people and make some contacts in the community. We’re providing a time and location, you just have to show up, and of course, pay for your own food and drinks. If you want to buy someone else a round, that’s a great way to make new contacts.

    Register and come if you are in town Monday night. There’s no charge, but we’d like to have an idea of how many of you will come by. It’s a Buffalo Wild Wings in Charlotte, from 6-9. The address is below:

    400 E Martin Luther King Jr Blvd
    Charlotte, NC 28202

    I don’t know how much I’ll get to talk to most of you, but I’d like to shake your hand and say hi.

  • The Challenges of Being Safe

    This editorial was originally published on Mar 19, 2009. It is being re-run today as Steve is away at DevConnections.

    One thing we recommend is to make sure that production data is always kept safe, which means that you keep control of where it lives and how it’s used. I saw a post recently where someone needed to send data to a business partner and wanted to obfuscate it.  I think that is a great thing to do and was glad to see someone asking for advice.  Sending data to business partners is required, but you want to be sure that you are not necessarily giving away too much information, especially identity, privacy, or financial information that you shouldn’t.

    However what about your test and development environments? I’ve seen people obfuscate data here, but not always. In fact, not usually. This is despite the fact that you might turn over developers often, expose that data to testers, or other people that might not normally have access, and the fact that these environments almost never have auditing enabled.

    We need to mimic production environments and data, but there are quite a few challenges with doing this in a safe and secure manner. Just having scripts to obfuscate data is a challenge in and of itself. I’d love to see a tool built that would do this automatically, though I wonder how many companies would actually buy it. I’m not sure there’s a great market for tools here until insurance companies start require it for your “data loss insurance.”

    But building those scripts is both hard and time consuming. How do you decide what to obfuscate? What values do you use? How do handle PK/FKs to ensure that things match up correctly if you’ve used personal information, like a SSN, as a key field?

    Beyond that, there’s much more. What about ensuring that data matches up correctly? Can you really determine if there is an issue with some calculation or relationship if you have random data. After all people many times will have favorite accounts that they know well and understand what the data should look like. A developer may expect certain order details or address information, and use that as a benchmark when developing new code. If the data is random every time his environment is refreshed, does that slow his productivity? How do you test things like URLs and emails if data is randomized?

    And what about when you alter your schemas? How much work is there to update your scripts?

    And what about mistakes? How often has someone pointed development code at a production server? What happens if they accidently run an obfuscation script against production? If you’ve scrambled data around, would you catch it quickly enough to restore before too many transactions were sent through?

    Using some type of data obfuscation or randomization is a great way to help ensure that your production data is kept safe, but it definitely makes for a much more complex environment, and likely, more headaches for DBAs and developers.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.mevio.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Secure Programming

    This editorial was originally published on Mar 19, 2009. It is being re-run today as Steve is away at DevConnections.

    Writing secure software is hard. The way most people learn software, from simple examples that slowly build our knowledge, doesn’t encourage complicated solutions that provide robust error checking, error handling, and secure practices. Most examples that I’ve seen in the world include “dumbed down” code that is easy to understand and explain in a class or seminar.

    And that’s the code that often gets used by developers.

    They go with the simple example, and enhance it, not really concerning themselves with best practices, or even great practices. In fact, if you search for sample code on the Internet, read articles on various sites about programming, or buy a book from an “expert” it seems that more often than not, the code isn’t well written from a security perspective. Yet that’s the code, and the mentoring, that many developers get.

    And for a DBA, that code is a nightmare.

    Too often the code samples and practices that are taught to people include dynamic SQL built in the application with little to no thought about SQL Injection. And it doesn’t matter if the application is at fault. As the DBA it’s often your phone that rings, and you that end up explaining to your boss why all the products on the site cost $1, or why every customer’s name is “0wned by S&p#rG##k.”

    The NSA, along with a number of other companies, is trying to do something about poor programming practices. They have release a list of the 25 most dangerous programming mistakes. What’s interesting is that some government agencies, and hopefully companies soon, are using this list as a litmus test for vendors. Software companies are being asked to guarantee that their software contains none of these mistakes.

    I think it’s a great idea, and I wonder how well it will be enforced. Microsoft and Oracle, among quite a few other software vendors, provided input into the list. You’d hope that their software would be fixed, or at least all new software would comply with the recommendations and not contain any of these mistakes.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • The Need for Tape

    This editorial was originally published on Mar 26, 2009. It is being re-run as Steve is away at DevConnections.

    One of our SQLServerCentral authors sent me this post, which I found to be pretty amazing. Apparently hackers broke into a backup server at WebHostingTalk, which is a community for hosting providers to discuss issues. They deleted backups first, and then moved on to deleting tables from the database.  It resulted in some downtime, and then a restore of an old copy of their database prior to trying to restore more recent copies. More information from the admin is here.

    Now that’s just malicious and nasty.

    Hopefully this wasn’t an inside job, though I could see that as a possibility. For a service like this, that just provides a place for a community, let’s people interact and talk, this is just vandalism. It serves no purpose. It’s likely no one even knows it was “M@M@sB0Y” or some other hacker, so there’s no fame, and it disrupts people who just want to converse with colleagues.

    I’d hate to think about this happening here. We have lots, and lots of posts from people all over the world, and while it wouldn’t kill us, it would really annoy many people that have volunteered their valuable time to help others by losing their work. We definitely need to make sure we don’t have an issue here.

    I see two takeaways from this incident for most DBAs and administrators. First be sure that your backup servers are just as secure as your production ones. There is production data in the forum of backup files here, and you should be providing as much security for them, and perhaps more, as on other servers. Don’t treat these servers lightly.

    Second I think this shows that there’s a need for tape backups, or some type of non-linked backup. A tape grabs the files from your server, completely separately from the SQL Server (or other application). It is a pull link, and it’s not obvious from the source server where these files have gone. That’s good in that it prevents some type of attack on the main server from propagating on. Most people use a push from their server to a remote device as part of the backup process. That’s OK, but it provides an easy link for someone to attack the backup server from the main one.

    And one more benefit of tape? Usually they’re rotated out, so even if a hacker or insider knows how to get to the backup system, without physical access they can’t touch your tape.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.