Voice of the DBA

Tag: security

  • Protecting Schools

    One very common targets for hackers are schools and universities. The latter may have plenty of resources for staff, but often school systems don’t have that same type of budget. In many ways, it’s sad that hackers would target schools that struggle to educate and help others. The staff often deals with low pay and high stress already, and losing access to computer systems adds to an already tough job.

    There was an interview this week with the director of technology at one district. Johnathan Kim works for the Woodland Hills school district and is a former staffer at the Navy Cyber Defense Operations Command. That’s the type of training that I think few school district employees have. I’ve known a few people who manage technology inside schools, and while they are often smart, capable people, they aren’t security professionals.

    The interview talks about a few of the changes that Mr. Kim has made, such as enabling two-factor authentication (2FA) and removing local admin rights for many teachers. I know these are the types of rules that frustrate many workers who use computers. In fact, I ran into someone who rarely upgrades software on their development machine because so many applications require administrative rights and they don’t want to bother opening tickets more than a few times a year.

    Two-factor authentication can be a pain, and I know I get confused sometimes as I have both 2FA and MFA with different processes for different systems. It’s good in that a few times in the last year I’ve caught a hacking attempt, but it’s also a pain to deal with when I’m doing something simple. I can see why people don’t like it when they don’t understand the challenges of securing systems. Every time I find myself frustrated, I stop and remember the problems others have had, especially those that have dealt with ransomware in their organizations.

    A good point in the interview is that education can help smooth the way for security practices that feel unnecessary or disruptive. It’s good to remind ourselves why we want the least privileges needed assigned to others, and why those of us with privileged access need a second account for that access. We also ought to come up with a good story to educate others when they complain, perhaps using a story of a breach or loss to help remind others that our systems are constantly under attack.

    Steve Jones

  • Xp_cmdshell Use Cases

    I had someone ask me recently how to run xp_cmdshell on a Linux version of SQL Server. I told them you can’t, as it’s an unsupported feature and not one that I expect to see released. I had to double-check, since I did think that supporting a BASH shell was a possibility, but it wasn’t added to the product.

    In the feedback forums, I saw a request for xp_powershell, though the feedback from MS is to use CLR for this. They suggest external access permissions, but those aren’t supported on Linux. I also didn’t see a request for shell scripts added, and I’m not sure I want one.

    A few years ago, I wrote a piece on the dangers of xp_cmdshell, as this does create a security risk. I can be mitigated, but the modern world is complex and it can be easy to make a mistake here. I’ve used xp_cmdshell often without issues, but I’ve also known the risks and tried to mitigate them with controls on the machine, network, and who can execute the procedure.

    I’ve seen people use xp_cmdshell for a number of tasks, like exporting a result set to a file, checking disk space, moving files after a BULK INSERT or backup, or some other task that is tightly related to actions taking in T-SQL. This can be a very handy utility for many administrators.

    Today I’m curious what are your use cases. Where do you use this utility, or where is it much easier than adding a PowerShell step in an Agent job? Similarly, do you use xp_fileexist or other XPs to do things are are outside of the realm of T-SQL. Leave a comment below and let us know how you use this stored procedure. Or in which situations this has proven to be useful in the past.

    And maybe vote for a BULK EXPORT 😉

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • SQL Injection Is Not Acceptable

    SQL Injection has been a problem for my entire career. Thirty years ago I could have easily just blamed this on ignorance, as most of our developers didn’t think about the nefarious ways that hackers enter data in our applications. These days, there isn’t a good reason for this to keep happening, and the problem is us. I think that we don’t provide good examples or training on secure coding or secure architecture as a normal part of teaching programming. In many organizations, we don’t check for issues and prevent their release. Some do, but many don’t. On top of this, the existing code is usually a poor template for writing future code. I do think Microsoft aims for secure coding in SQL Server but in Windows, there is work to be done there.

    A few months ago, I saw an article that noted the US CISA organization and the FBI issued a secure-by-design alert (PDF) that noted there is no excuse for SQL Injection vulnerabilities (SQLi) in modern software. This alert notes that SQLi has been an “unforgivable vulnerability” since at least 2007. Inside the document on vulnerabilities, it notes that a single quote can’t be used in certain fields: username, password, ID field, or numeric field. They also note that co-mingling user data and query data, like constructing queries on demand, is a poor practice.

    The alert even emphasizes that developers are engaging in poor practices when they “fail to treat user-supplied content as potentially malicious.”

    I agree, and their recommendations are worth reading and implementing. If your boss doesn’t want to spend time on these, point out the bulletin and note that since this is a published advisory, I wouldn’t be surprised to start seeing lawsuits in the US or even insurance claim denials if your software team doesn’t follow these practices. Note that the list includes leadership support of secure coding and secure design principles.

    I doubt this has changed a lot, but I think some managers likely see this as a) a good idea, and b) a way to mitigate potential issues down the road. Changing the habits of software developers, updating code snippets or patterns, and even adding linting/static-code-analysis to CI pipelines take time, as does the training for developers. However, it’s something that has to start changing over time to get better at building higher quality, more secure software.

    I’d like to see insurance companies refuse to indemnify or cover losses or problems from software that is written from this point forward and is vulnerable to SQL Injection. There are far too many tools out, and software is too critical to allow these types of simple coding errors to proliferate. I’d also be pressuring companies to ensure older code is being actively refactored to reduce the number of vulnerabilities over time for all software they still support.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Cyber Insurance for War

    Is the United States at war in cyberspace with other countries? It’s the claim that a few insurers made when US-based Merck filed a claim after a 2017 cyberattack. The company filed a $700mm-ish claim, and the insurers rejected this saying that the attack was an act of war by Russian government operatives. If a factory were disrupted in the real world by actual government operatives (soldiers, spies, etc.), then it likely would be considered an act of war, but in cyberspace, who knows.

    Actually, in cyberspace, who really knows who is whom, and for who they work? Maybe we don’t even know who “they” are in many cases.

    Bruce Schneier wrote a piece on this, talking about a possible solution of using a government backstop that would provide some assistance or coverage that insurers might not be able to cover. While that sounds good if you have a claim, as a citizen, I think this likely encourages more attacks from others, whether they are nation-states or individuals. If someone thinks they might create a spending crisis in a country, maybe they would mount many attacks that are disguised as coming from a foreign nation and cause a government spending issues.

    The United States has a scale issue, but this could cause similar issues in many smaller countries if this were a way of doing business. Perhaps this might also cause some economic issues if companies don’t want to do business in places where they aren’t covered.

    However, this might not be a big issue as more insurers are starting to carve out exceptions in their coverage for these types of attacks, so if your organization is hacked by a supposed nation-state, you aren’t covered. I suspect this will also start to extend to other exceptions, such as having unpatched (or incompletely patched) systems, poor policies or just incompetent employees, lack of security scans, and more.

    In some sense, I would hope that some of these attacks will force, or at least pressure, many organizations to take security more seriously. At the same time, as someone dealing with these changes, it can be inconvenient and a blow to productivity as I struggle to adapt to changes in policy, protocol, and procedure. Some are easy, like locking my home machine. Some are more challenging and frustrating as I try to share more content inside the company. It’s good, but it’s frustrating.

    Cyberattacks and cybersecurity are increasingly a part of our lives as technology professionals and I urge you to spend a little time learning about the field. If you want a fun challenge, try the Advent of Cyber 2023 is still up as of this writing. I went through it and it was a fun (and scary) way to learn a few things about security, vulnerabilities, and tools out there.

    Steve Jones