Voice of the DBA

Category: Editorial

  • Removing Weak Security from SQL Server

    I was checking some arguments in the RESTORE command for SQL Server and saw that the MEDIAPASSWORD option was deprecated and marked as being removed at some point. That made sense, and I assumed that PASSWORD was the option to be used moving forward. However, that option is also marked as deprecated.

    What should we do?

    Well, we need to better secure our backup files for sure. Disk encryption and limits on AD/directory permissions ought to be set. Of course, we need to use care when handling or moving these files, especially when they cross the secure boundary in production systems to other dev/test/etc. networks.

    The security section of the document explains the reasoning here. The protection provided is weak and isn’t intended to protect data. You can still read data in the backup file. This is mainly to prevent an incorrect restore when using tools, meaning the human picking the wrong file. This isn’t to protect your data.

    I suspect most people dealing with SQL Server backups that use either of these options don’t know this. They think the password secures their data. I know because I’ve seen people use this to send a backup file through email or file transfer to another party. However, if you’ve ever opened a backup file in XVI32 or another editor, you will see that your data is in plain text. If you’ve never done that, give it a try today and search for strings that you know are stored in the database.

    Some security is better than no security and layers of security that build on each other are useful. However, depending on weak security isn’t good. It leads people to count on something that doesn’t work and ignore more serious issues.

    I’m glad that Microsoft is (supposedly) removing these options. I understand that backwards compatibility and preventing existing scripts from failing are important. At the same time, we need to move forward. I’d like these password items to become a no-op, and not cause errors, but I would hope that their use in scripts would also generate a message to the user that these options don’t work and need to remove removed. Perhaps with a direct message and a note in the error log, we’d start to see people embracing other security practices that will provide more protection.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Shift Right

    Earlier this year I was watching someone present on DevOps and IaaC (Infrastructure as Code). The speaker was showing how they had worked with clients to implement tests and checks that evaluated whether their systems were deployed and the code was working as expected. They did this as the CI/QA environments weren’t maintained, but rather spun up as needed when code was committed or tested.

    What I found fascinating was the part in the talk where the speaker said they were “shifting right.”

    That had me sitting up and paying closer attention. After all, for ten years I’ve been working in DevOps where we try to shift everything left. Shift testing to the developers, shift infrastructure into the repository, shift pen testing into some automated CI stage. We want to know about issues sooner. What is this shift right stuff?

    It turns out that the speaker was using this as a way of smoke testing. They didn’t completely trust that CI/QA were configured the same as production. They were performing many of the same tests in production after a deployment. In a few instances, they’d spin up a copy of production, deploy changes, and then run tests against that. They wanted to get a real-life view of how the system worked.

    For some parts of the infrastructure, this seemed like overkill. After all, if I’m updating an app and perhaps adding a web server to check, verifying this was the same size as a previously deployed web server seems silly. However, they included other checks like security. Was the new, or even old, server configured with the same security that was required by the org? They had policies around various authentication and authorization items and these were constantly rechecked. Someone alter a file share or enable anonymous access? If this was against policy, someone was notified when the next deployment caught this.

    What was interesting about this methodology was that deployments weren’t often failed. Instead, when something failed, there was a report sent to a group and the incident was logged, but the deployment still occurred. That’s an approach that I actually like. Don’t stop things, but make sure someone follows up. Of course, like many nagging problems, if this happens constantly or it can’t get fixed, then this isn’t helpful information.

    I haven’t thought much about the idea of shifting things right, but I have advocated for the same ideas. Create smoke tests that evaluate if things still work after deployment. Get feedback to people right away. Much like a unit test, I’d want to know if simple things are broken. I’d also approach these tests the same way. Don’t write a lot to start, but every time there is an issue, add a unit test and a smoke test to prevent regressions.

    And be careful of which frameworks you use. Some database unit testing frameworks alter tables, which can’t happen in production.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • A Real World Clash with AIs

    A group of people are protesting more cars in San Francisco by disabling self-driving cars with a cone on their hood. It is likely that the cone disables some sensors and won’t let the cars move. For truly autonomous cars, this could be an issue if people could prevent them from working with small hacks. I could imagine a strip of duct tape or some other smaller obstacle that might also work and be less noticeable to anyone looking at the vehicle.

    Ignoring the humor here, or the more-cars-in-cities debate, this is an interesting place where computing systems can fall down. People do find ways to hack sensors or other ways in which our software gets data from the real world, which can force behaviors we didn’t expect. We might allow for these, or we might not. In this case, I don’t know that the developers could do anything different, other than perhaps detect and alert the owners that the vehicle can’t move until someone clears the obstacle. They could add more sensors, but I suspect hackers would just block those.

    The bigger issue is that as we depend on computing devices that interact with and need to work in the real world, we’ll have more problems like this. We might see simple hacks, or even everyday events that can interfere with the machines completing their tasks. If we get very dependent on these AIs, then these hacks become more than just vandalism. Imagine getting in this vehicle in an emergency to go to the hospital, and it won’t move because a sensor is blocked? That could be more than inconvenient.

    We certainly have similar issues with people in the real world. People flatten tires (how does an autonomous car change a tire), they padlock doors and gates, and they might do any number of things that interfere with others’ ability to live their lives. Humans can often adapt to these issues with small behavior changes, but what about computing devices? Will a new class of jobs come about to correct issues like this? Will we see “fix-it” robots that autonomously move around and remove cones from hoods or change tires?

    It’s hard to even think about all the crazy permutations of how autonomous computing devices can evolve and the types of interactions we might have. I’m not even sure if I think that the way we are approaching AI and autonomous systems is good. We’re often applying similar frameworks to them that we do for other humans, which I’m not sure if the best way to approach these decisions.

    The future is looking very strange, and it’s coming quickly. I’m not even sure what to think about issues like this.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Using AI with Data Tasks

    The hot new technology of the year is AI. Between ChatGPT, Copilot, and generative AI, it seems that this is invading the world of computing at an incredible rate. Whether this becomes really useful and valuable or not is something that we will seem over time. There have been plenty of trends in this area in the past that haven’t become as ubiquitous as the hype would lead you to believe.

    I’ve done some light experiments with AI on my blog. To date, I haven’t found this to be that useful, other than a few cases where I basically used an AI to search the web for me. Rather than read a bunch of SSC or Stack Overflow results, the AI summarized things.

    Somewhat.

    I definitely had to test and verify the code more than I feel I’ve done with code posted in a forum. Of course, I do less experimenting because the AI results were a little more targeted to what I needed, rather than my cobbling knowledge and partial solutions together. I’m also not sure which I prefer.

    I would like to use AI for data work, and there is an article that talks about some of the ways that we’ve used AI in the past. Data profiling has made sense, and I can see value here. For data security, I’m not sure how helpful AIs have been. I’ve looked at some products, and I don’t know that I think any of them do a great job of identifying data. They do make it easy for whoever is assigned the task by doing some of the work, but they aren’t a panacea. They make mistakes, just like humans do.

    I do think data observation and looking for anomalies is a place where AI can really shine, but that’s not the data work that many of us do. It matters, but for most of us, this isn’t something we deal with.

    The future of AI was more interesting. The idea of data homogenization, taking data from different sources, and fitting it to a data model is interesting. Of course, the AI can’t make too many mistakes, or the time correcting might overwhelm the time saved. I think we see that now with humans who we ask to ETL data. If they aren’t good at it, or make lots of mistakes, those of us overseeing them might just do the entire job ourselves.

    I know that AIs are still new and immature, and while there is a lot of potential, they feel like junior staffers now, needing more handholding and micromanaging than I like to do. Perhaps they will change our careers and the way we work, but I don’t know how quickly, or even how deeply. Already I find lots of companies putting restrictions on what their employees can do with AI, which makes me think this might be more a targeted, niche technology more than a general, use-it-everywhere solution.

    Steve Jones